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 = 0x00DED9328A0F
EXEC master..xp_varbintohexstr @binary, @string OUTPUT
SELECT @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.