Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

fn_get_sql

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Returns the text of the SQL statement for the specified SQL handle.

Syntax

fn_get_sql ([ @SqlHandle = ] SqlHandle )

Arguments

[ @SqlHandle = ] SqlHandle

Is the handle value. SqlHandle is binary(20).

Tables Returned
Column name Data type Description
dbid smallint Database ID. Is NULL for ad hoc SQL statements.
objectid Int ID of the database object. Is NULL for ad hoc SQL statements.
number smallint The number of the group, if the procedures are grouped. Is 0 for entries that are not procedures and NULL for ad hoc SQL statements.
encrypted Bit Indicates whether the object is encrypted.

0 = Not encrypted
1 = Encrypted

text Text Text of the SQL statement. Is NULL for encrypted objects.

Remarks

You can obtain a valid SQL handle from the sql_handle column of the sysprocesses system table.

If you pass a handle that no longer exists in cache, fn_get_sql returns an empty result set. If you pass a handle that is not valid, the batch stops, and the following error message is returned:

Server: Msg 569, Level 16, State 1, Procedure fn_get_sql, Line 12 The handle passed to fn_get_sql was invalid.

Microsoft® SQL Server™ cannot cache some Transact-SQL statements, such as bulk copy statements and statements with string literals larger than 8 KB. Handles to those statements cannot be retrieved using the fn_get_sql function.

The text column of the result set is filtered for text that may contain passwords. For more information about security related stored procedures that are not monitored, see Limiting Traces.

The information returned by the fn_get_sql function is similar to the DBCC INPUTBUFFER command. Use the fn_get_sql function when the DBCC INPUTBUFFER cannot be used, for example:

  • When events have more than 255 characters.

  • When you have to return the highest current nesting level of a stored procedure. For example, there are two stored procedures that are named sp_1 and sp_2. If sp_1 calls sp_2 and you get the handle from the sysprocesses system table while sp_2 is running, the fn_get_sql function returns information about sp_2. Additionally, the fn_get_sql function returns the complete text of the stored procedure at the highest current nesting level.
Permissions

Only members of the sysadmin fixed server role can run the fn_get_sql function.

Examples

Database administrators can use the fn_get_sql function to help diagnose problem processes. After an administrator identifies a problem server process ID (SPID), the administrator can retrieve the SQL handle for that SPID, call the fn_get_sql function with the handle, and use the start and end offsets to determine the SQL text of the problem SPID. For example:

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle) 

See Also

DBCC INPUTBUFFER

sysprocesses

Limiting Traces

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft