Remote Desktop Connection as an Admin

You might have come across situations where you need to connect to a remote desktop and some other users are holding sessions and the total number has exceeded and the mstsc command will not allow further sessions to the remote desktop of the server.

One easy way to kick one user out will be to use the following command to login

%SystemRoot%\system32\mstsc.exe /admin

and then type ur username and password to get in.

Try Catch block in a Transaction

We can use TRY catch blocks from SQL Server 2005 onwards and can rollback a transaction if there were any errors. Please see the example below


BEGIN TRY
 SET XACT_ABORT ON -- Will rollback on any errors
 BEGIN TRANSACTION    -- Start the transaction
   DELETE FROM Mytable
WHERE id = 'something'
   -- If we reach here, success!
   COMMIT
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Checking data in database before going for any inserts

We commonly encounter a scenario where we have to check if a data is already there in table before going for any insertions. So here is a quick query for this purpose

please note that i wrote this query keeping Sql Server in mind.

IF NOT EXISTS ( SELECT TOP 1 [NAME] FROM myTable WHERE [Name] = 'check_name' )
BEGIN

INSERT INTO myTable
           ([Name]
           ,[Description]
           )
     VALUES
           ('check_name'
           ,'test'
           )
END

Passing array of parameters to UDF/Stored Proc in SQL Server

With the beautiful XML support from SQL Server 2005 onwards, passing multiple values to Stored Procedure or User Defined Function has never been so easy. Please see the sample code

-- Pass all employeeId or other information together
-- in a sincle call of the stored procedure

DECLARE @x xml, @EMPID INT
SELECT @x =
  N'<Root>
      <Employee empId="1"/>
    </Root>'
SELECT E.EMPLOYEEID FROM TBLEMP E   
JOIN @x.nodes('Root/Employee') AS T(Item)
ON E.EMPLOYEEID = T.Item.value('@empId', 'int')

-- Writting the stored procedure

CREATE PROCEDURE SP_EMPLOYEES @ids xml AS
   SELECT E.EMPLOYEEID, E.MANAGERID, E.NAME
   FROM   TBLEMP E
   JOIN   @ids.nodes('/Root/Employee') AS T(Item)
     ON   E.EMPLOYEEID = T.Item.value('@num', 'int')
go

-- Test the procedure with the following
EXEC SP_EMPLOYEES N'<Root><Employee num="1"/><Employee num="2"/>
                             <Employee num="3"/><Employee num="4"/></Root>'

Searching for a text on SQL Server (PL SQL Style)

Hi those coming from a PL SQL background will be knowing of a very common functionality which enables you to search for a particular text through PL SQL Dev tool on a particular text. Now if you try to look for a same type of a feature on SQL Server Management Studio, there is none.


The work around is the following stored procedure.
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%foobar%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

So if you want to search for a text like foobar in all procedures used in the database, the query would look like something above. If the same thing has to be searched in a function then the parameter "IsProcedure" has to be changed to "IsFunction".


This tip will particularly important if you are associated with support and enhancement kind of activity and need to find say "How many times a particular table name is used in a procedure or a function".

Introduction

Hi,

My name is Kaushik and I am an ASP.NET developer working in the Greater Chicago Area. I have created this blog so that I can refer to the resolutions that could gather or find out while doing my day to day activities. Nothing is impossible to implement through software and at the same time its not possible to know everything, so a good reference is a key to quick resolutions to complicated/simple tasks.