.gif)
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
