Questions, Answers, & Tips

In T-SQL, how can I convert a binary(6) field such as 0x00DED9328A0F to a char(14) field (including the 0x) such as 0x00DED9328A0F? All I want is a textual hexadecimal value.

You can use the undocumented system stored procedure xp_varbintohexstr to convert varbinary fields to hexadecimal strings. Although Microsoft recommends against using undocumented system stored procedures, which can change from release to release, system components often can provide the exact functionality you need. The following code snippet shows how to use xp_varbintohexstr to convert your binary(6) field to a char(14) field:

DECLARE @string varchar(14)DECLARE @binary binary(6)SELECT @binary = 0x00DED9328A0FEXEC master..xp_varbintohexstr @binary, @string OUTPUTSELECT @string

Can I generate a list of all queries or server process IDs (SPIDs) currently executing within a transaction?

The sysprocesses table in the Master database contains many pieces of valuable information, including a column called open_tran. Sysprocesses has a row for every unique SPID in a SQL Server connection. The open_tran column holds the current value of @@trancount for the SPID you're looking at in the sysprocesses table. An open_tran value greater than 0 means that the SPID is in a transaction; a value of 1 means that the SPID is in a top-level transaction, a value of 2 means that the SPID is nested one level deep in a transaction, and so on.

To view which connections are inside a transaction, you can issue the following command:

SELECT spid, open_tran FROM master..sysprocesses WHERE open_tran > 0

You can then use the dbcc inputbuffer command to see which command is currently executing. For example, dbcc inputbuffer(10) shows you the first 255 characters of the last SQL command executed in SPID 10.

In a multiuser WAN environment that serves more than 500 users, I want to isolate people who are using named pipes instead of TCP/IP to connect to SQL Server. Users have specified different protocols by means of the client configuration utility. I've used network monitoring tools to monitor NetBIOS over TCP (NetBT) connections but haven't found a consistent method of isolating named pipe connections. How can I detect the protocol through which users are connecting to SQL Server?

You need look no further than the sysprocesses table. The sysprocesses table has one row for every active server process ID (SPID)—or every user connection—in the SQL Server connection. Sysprocesses' net_library column contains the connectivity protocol information you're looking for.

Table 1, page 26, describes SQL Server 7.0's different Network Library (Net-Library) names. The Server Net-Library column in Table 1 contains the Dynamic Link Library (DLL) name you see in sysprocesses' net_library column. However, SQL Server 2000's communications architecture has changed dramatically; you can find the Net-Library names that SQL Server 2000 uses by reading the SQL Server 2000 Books Online (BOL) "Communication Components" section.

I've found the Microsoft SQL Server newsgroups to be a great source of information, but sometimes I'm working at a site that doesn't permit Network News Transfer Protocol (NNTP) packets to pass through, so I can't access the news servers. Is a Web version of the newsgroups available?

Yes, you can access the newsgroups through the Web at http://msdnnews.microsoft.com/. If you've never browsed Microsoft's SQL Server newsgroups, you're missing out on a valuable resource. The newsgroups provide a great place to ask questions, and you can learn a lot by simply reading other people's questions and answers.

I'm a Visual Basic (VB) developer and new to T-SQL programming. Does T-SQL have a function equivalent to VB's InStr() function?

Life would be much easier if functions had the same name across different programming languages.

T-SQL's charindex function, for example, lets you do the same thing as VB's InStr(), which specifies the first occurrence of one string in another string. Charindex's syntax is

CHARINDEX ( expression1 , expression2 [ , start_location ] )

Expression1 is a short character data type expression that contains the sequence of characters you want to find. Expression2 is the character string that you want to search. And start_location is the character position where charindex starts searching for expression1 in expression2. If you don't specify a start_location or if start_location is a negative number or zero, the charindex search starts at the beginning of expression2.

In working with milliseconds in a datetime value, I've stumbled across what seems to be a bug. Two examples illustrate the problems I've run into. First, when I execute the following script:

CREATE TABLE #MyTable (   MyID int IDENTITY(1,1) NOT NULL,   MyDate datetime NULL )INSERT INTO #MyTable (MyDate) VALUES ('01/01/99 00:00:00.001') SELECT * FROM #MyTable

I get the following result set:

MyIDMyDate                     ------------------------11999-01-01 00:00:00.000

which doesn't show the millisecond value of .001 that I entered.

Second, when I execute the following script:

CREATE TABLE #MyTable (   MyID int IDENTITY(1,1) NOT NULL,   MyDate datetime NULL ) INSERT INTO #MyTable (MyDate) VALUES ('01/02/99 00:00:00.000')

the following command returns an empty result set:

SELECT * FROM #MyTable WHERE MyDate > '01/01/99 23:59:59.999'

But changing the millisecond value from .999 to .998 causes the query to return the expected row.

Many people don't know this, but according to SQL Server Books Online (BOL), datetime values that reference milliseconds are accurate only to one three-hundredth of a second, or 3.33 milliseconds. In addition, SQL Server rounds values to increments of .000, .003, or .007 milliseconds, as Table 2 shows.

In your first problem, SQL Server is rounding down the millisecond value from .001 to .000. And in your second problem, when you execute the script, SQL Server is rounding up the millisecond value of .999. However, when you change the value to .998, SQL Server rounds down the value to .997, and you get your expected result. (For more information about milliseconds and datetime values, see Kalen Delaney, Inside SQL Server, "Inside Datetime Data," October 2000.)

How do I retrieve records that contain a datetime value if I'm interested only in the date part of the value? For example, how do I retrieve all records for 7/1/2000, regardless of their time value?

You can easily isolate the date part of the datetime value by using the CONVERT function, as in:

SELECT convert(char(27), getdate(), 109)Sep12 2000 10:59:06:703AM

Isolating the date makes your code easier to read because you don't have to reference the time values in a BETWEEN clause, but use the CONVERT approach with caution. The problem is that you can render indexes useless if your WHERE clause contains a search argument against a column referenced by a function such as CONVERT; the index will have stored data differently from the way the CONVERT function stores the result. Thus, I encourage you to consider the time portions of your data when writing queries. (For more information about milliseconds and datetime values, see Kalen Delaney, Inside SQL Server, "Inside Datetime Data," October 2000.)