SQL Server Queries, Running under a process

Some time it requires to know what actually the SQL Server is executing under a process. So that we can know where exactly the procedure/query is stuck right now. Which helps us to know whether we can kill the running process or not. By making use of the inputbuffer it gives only the procedure (with parameteres) currently executed by the process, in the case of a procedure. Below query gives you the excat query executed by SQL Server.

More...

T-SQL - Checking for Unicode characters

Here is a code in T-SQL which will check if a string contains UNICODE characters:

Lets create a table variable:

declare @temp table (Name varchar(100), Address1 nvarchar(200))

Now we will insert two records, one of which contains ascii and one non-ascii character.

More...

T-SQL - Password Encryption/Decryption

Here is a sample code which will show how to encrypt/decrypt password using password phrase in T-SQL:

We will hard code password phrase and and original password then we will encrypt the password and store it in a variable.

Declare variables:

DECLARE @origPass NVARCHAR(50),
@passPhrase NVARCHAR(50),
@encryptedPass VARBINARY(MAX),
@decryptedPass NVARCHAR(50);

More...

T-SQL - Removing trailing zeroes

There are some cases when we have a value with trailing zeors to the left like '00089' but actually we want to consider only '89'.

The one solution to above problem is we can CAST it as INT. But since the value can be non-numeric also like '00089 MM:NN'. In this case CAST to INT will not work.

We will try to solve this problem using PATINDEX.

Below is the Code:

More...