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 - Page 2

By Andrew Novick

The data type of the EventInfo column is nvarchar(255). This has proven to be an annoying limitation because it restricts the results to the first 255 characters of any SQL statement. While that might be enough when the statement is executing a stored procedure, it is often insufficient when a complex SELECT or UPDATE is involved.

Calling system UDFs is different from calling your own functions. The documented system UDFs all return tables of information, so they are used in the FROM clause of SQL statements. A double colon precedes the name of the system UDF. Therefore the FROM clause that invokes fn_get_sql looks like this:

FROM ::fn_get_sql(@HandleVariable)

The system UDFs obey a short set of rules:

  • Their names begin with the characters fn_
  • Their names are all lower case characters
  • They are defined in the master database under the pseudo owner system_function_schema.

I have referred to system_function_schema as a pseudo owner because SQL Server creates it for the sole purpose of owning these functions but it does not correspond to a user in the master database.

fn_get_sql overcomes the length restriction on the size of the SQL statement returned by returning a text column. Here is the layout of its result set:

Column Name

Data Type

Description

dbid

smallint

Database ID

objectid

int

ID of the database object. NULL for ad hoc statements.

number

smallint

The number of the group, if the procedures are grouped.

encrypted

bit

1=Encrypted 0=Not encrypted

text

text

Text of the statement. NULL if the object is encrypted. Will only return 8000 characters.

In spite of the fact that the text column is of data type text, the design of SQL Server's internal cache limits what's in the cache. It does not include BULK operations and it can't include string literals that are over 8K.

Unlike DBCC INPUTBUFFER, which takes a SPID, fn_get_sql takes a sql_handle as its parameter. sql_handle is a new BINARY(20) column in the sysprocesses table that resides in master. This field and two more, stmt_start and stmt_end, were added to sysprocesses to support fn_get_sql. The sql_handle can be retrieved using the SPID as this script:

-- Retrieve the sql of this connection 
DECLARE @handle binary(20)

SELECT @handle = sql_handle
    FROM master..sysprocesses
    WHERE spid = @@SPID

SELECT [text] 
    FROM ::fn_get_sql(@handle)
GO
(Results)
text   
---------------------------------------------------------------
-- Retrieve the sql of this connection
DECLARE @handle binary(20)

SELECT @handle = sql_handle
    FROM master..sysprocesses
    WHERE spid = @@SPID

SELECT [text] 
    FROM ::fn_get_sql(@handle)

Don't get confused by the fact that the output is identical to the query. It is supposed to be the same. It even includes the comment line that starts the batch.

Handles expire very quickly and should be used immediately. If you pass in the handle that is no longer in the cache, fn_get_sql returns an empty result set. That was happening to me often and I couldn't explain the empty results. It turns out that when the plan for a SQL Statement has zero cost it isn't cached at all.

Microsoft has added a remedy to this situation in the form of a new trace flag, 2861. Once it is turned on, zero cost plans are cached and they show up as the result of fn_get_sql. Trace flags are turned on with the DBCC TRACEON statement such as:

DBCC TRACEON (2861)

You can turn it back off with TRACEOFF as in:

DBCC TRACEOFF (2861)


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