SQL Server T-SQL User-Defined Function of the Week

Get your Network Library from sysprocesses

Volume 1 Number 44     September 16, 2003

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

SQL Server Magazine is probably the premier publication for 
Microsoft SQL Server DBAs and developers.  The September 2003
issue has an interesting article by Kalen Deleney about the
sysprocesses table.  

Most of the information relates to the fn_get_sql function that
was covered in Issue 14 back in February. I've written more 
about fn_get_sql in my book, but that won't
be out for a few months so I may update the web site with 
more on fn_get_sql soon.

One of the tidbits from the article was the availability of the
net_library column in sysprocesses.  It shows you the current 
network library in use for the connection described by the
each row in sysprocesses.

This week's UDF returns the name of the network library used
by the current connection.  Here's the CREATE FUNCITON script:

/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.udf_Process_NetLib (

)   RETURNS nvarchar(12)
    -- No SCHEMABINDING due to use of system table 
/* 
* Returns the name of the network library that this connection is 
* using.  This confirms which network library is in use.
*
* Attribution: Based in information from an article by Kalen  
* Deleny "Sysprocesses in SP3" in the 9/03 SQL Server Magazine
*      
* Example:
select dbo.udf_Process_NetLib () as [Net Library]
*
* History:
* When          Who     Description
* ------------- ------- -----------------------------------------
* 2003-08-27    ASN     Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* 
* Published in T-SQL UDF of the Week Newsletter Vol 1 Number 44
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

    DECLARE @NetLib nvarchar(12)

    SELECT @NetLib = RTRIM(net_library)
        FROM master..sysprocesses
        WHERE spid = @@SPID

    RETURN @NetLib

END
GO

GRANT EXEC ON [dbo].[udf_Process_NetLib] 
        TO [PUBLIC]
GO
\-------Stop copying above this line ---------------------------/

The way that I'd expect to use it would be to query it from a
client application as part of the application diagnostics.  I 
typically put an array of diagnostic information in the about
box of my applications as an aid in trouble shooting.  The 
network library is one more piece of information that might
give a clue about a problem.

It doesn't take much to use it as this query shows:
/------- Start copying below this line -------------------------\
SELECT dbo.udf_Process_NetLib () as [Network Library]
GO
\-------Stop copying above this line ---------------------------/
(Results)
Network Library 
-------------------------------- 
TCP/IP      

That's all there is to it.

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+