Systems Engineering and RDBMS

Archive for July 31st, 2008

SQL Server 2008: Converting Binary and Character Values

Posted by decipherinfosys on July 31, 2008

In one of our post, we had covered how to convert a hext string to a varbinary and vice-versa in SQL Server 2005. In SQL Server 2005, if you use the convert function to convert a character string to a varbinary, you will get the ASCII codes of the characters and likewise if you tried to convert from a varbinary to a string using convert, you would have gotten the characters represented by the ASCII codes in the binary value. That is why one needed to use different means like the one we have shown in the previous post. In SQL Server 2008, one can simply use the CONVERT function to do the same thing.

The CONVERT function now has new styles that allows to make this a possibility. The binary styles can have one of the three possible values: 0, 1 or 2. When using 0, the behavior is the same as SQL Server 2005 and prior releases i.e. it translates ASCII characters to binary bytes or binary bytes to ASCII characters.

When you use the value of 1 and are converting a string to a binary, you need to have the “0x” in the input string. And when you use the style of 2, you do not need the “0x” in the input string when converting a string to a binary. And when converting a binary to a string, when using style 1, it includes the “0x” prefix in the resulting string – when you do the same with style 2, it does not include the prefix. Here is an example to help make sense out of the above statements:

SELECT
CONVERT(NVARCHAR(34) , 0xc23eed6b65c93e44a41a2818e274194f, 1) AS BINARY_TO_STRING_STYLE1,
CONVERT(VARBINARY, ‘0xc23eed6b65c93e44a41a2818e274194f’, 1) AS STRING_TO_BINARY_STYLE1,
CONVERT(NVARCHAR(34), 0xc23eed6b65c93e44a41a2818e274194f, 2) AS BINARY_TO_STRING_STYLE2,
CONVERT(VARBINARY, ‘c23eed6b65c93e44a41a2818e274194f’, 2) AS STRING_TO_BINARY_STYLE2

And here are the results:

BINARY_TO_STRING_STYLE1            STRING_TO_BINARY_STYLE1              BINARY_TO_STRING_STYLE2            STRING_TO_BINARY_STYLE2
---------------------------------- ------------------------------------ ---------------------------------- ----------------------------------
0xC23EED6B65C93E44A41A2818E274194F 0xC23EED6B65C93E44A41A2818E274194F   C23EED6B65C93E44A41A2818E274194F   0xC23EED6B65C93E44A41A2818E274194F

Posted in SQL Server | 2 Comments »

SQL Server 2008: The Debugger is back

Posted by decipherinfosys on July 31, 2008

If you have worked in SQL Server 2000 and were used to using the T-SQL debugger, you might have lamented it’s absence in SSMS in SQL Server 2005. In SQL Server 2005, one can debug the stored procedures using Visual Studio though and that works really great as well. In SQL Server 2008, MSFT has re-introduced the debugger into SSMS. In today’s post, we will pick up a stored procedure in the AdventureWorks database and will go through the features of the debugger – it is nothing different than other debuggers that you have used but it is nice to know that for folks who rely on using only SSMS for their stored procedure work, they do not need to use VS to help debug in an effective manner.

In the BOL, all of the information pertaining to the debugger is under Transact-SQL Debugger. If you have both the client tools and the server running on the same machine, then there is no configuration needed to use the debugger. However, if you are using the client tools from one machine to connect to the database server on another machine, then you need to enable port and program exceptions by using the Windows Firewall Control Panel application on both the client and the server.  We will look into those and other security related issues pertaining to the debugger in an upcoming post.  In this post, we just want to go through an example of the debugger.

So, let’s prepare an execution script for executing a stored procedure in the Adventure Works database:

I am using a very simple example just to illustrate the feature:

declare @x datetime
select @x = GETDATE()
exec [uspGetBillOfMaterials] 799, @x

And now, press the debug button as shown in the image below – this will launch the debugger:

Once the debugger launches, you will see the different debug options at the top – Step Into, Step Over, Step Out, breakpoints etc. and at the bottom, you will see two windows – one for the local variables/Watch and the other one a call stack/Breakpoints/Command Window and the Output – these are tabbed interfaces.  Here is the image that shows that:

And now, let’s step into the call to the procedure and you will see the local variable values and in the call stack, you will see the actual call.

This particular procedure only has a CTE which executes and returns back the records so it will exit out as we process through it and will return back the records.  Using the debugger, troubleshooting issues becomes easier especially in the case of larger procedures and the nested procedures which call other procedures or views/functions.

Posted in SQL Server | 6 Comments »