Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 14, 2003

Find Out What They're Doing with fn_get_sql

By Andrew Novick

Don't miss the movie that accompanies this article!

Not only did SQL Server 2000 introduce the concept of user-defined functions (UDF) but also Microsoft's development team created several such functions as part of the database product. In addition to many undocumented system UDFs, used internally by SQL Server, the product release has nine documented UDFs that are part of a special schema in the master database named system_function_schema. These system UDFs provide information about the internal workings of SQL Server that isn't available elsewhere. SQL Server 2000 Service Pack 3 (SP3) includes a new system user-defined function, fn_get_sql. The function was included in an earlier hotfix but SP3 is the best way to get it. (See Microsoft Knowledge Base article 325607)

fn_get_sql makes it easier for DBAs, programmers, and diagnostic tools to retrieve the text of the SQL being executed by active SQL processes. This is a technique commonly used when diagnosing a deadlock or other blocking problem. Diagnostic tools that monitor activity inside the database engine can also use it.

The first step before using fn_get_sql is to get the documentation. There is an updated Books Online for SP3 that includes the new documentation for fn_get_sql. You can get it without installing SP3 at this link: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

If you're not installing SP3, be careful as you install the updated documentation. If you let it, the installer will overwrite your existing Books Online (BOL). However, if you make the right selection, it can be installed side-by-side with the old BOL, which you will want to keep around. You will know that you have the updated documentation because the title bar of the BOL window is updated as shown in Figure 1.

Figure 1 Updated BOL Title Bar

Prior to SP3, the way to get the SQL being used by a SQL process was from the DBCC INPUTBUFFER command. DBCC INPUTBUFFER takes a SPID as its argument. SPIDs are integers that uniquely identify a database connection. SPIDs below 53 are generally used by system connections. Fifty-three and over are used for user connections. A connection can get its own SPID with the @@SPID built-in function.

When invoked, DBCC INPUTBUFFER returns a rowset consisting of these columns:

Column Name

Data Type

Description

EventType

nvarchar(30)

Language Event
No Event

RPC

Parameters

int

0=text 1-n=parameters

EventInfo

nvarchar(255)

For an RPC (stored procedure) it contains the procedure name. For a Language Event it contains the text of the SQL being executed.

This query gives you the idea of how DBCC INPUTBUFFER works by showing you its own text:

DBCC INPUTBUFFER (@@SPID)
GO
(Results)
EventType      Parameters EventInfo                   
-------------- ---------- --------------------------- 
Language Event          0 DBCC INPUTBUFFER (@@SPID)
(End of results)


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date