Thread: fn_get_sql , no full SQL data
01-16-06, 12:09 #1Registered User
- Join Date
- Jul 2002
Unanswered: fn_get_sql , no full SQL data
I'm struggling to get anything out of this fn_get_sql() thing as included below.
First, running this in Q.A. still doesn't return the full SQL string, just the first x characters. Second, for most processes the sql_handle in Sysprocesses is just zeroes. Does this info get lost after a while? All the processes where I need to know what happened are old ones, that's waiting for NETWORKIO.
drop proc dbo.getsql
create proc dbo.getsql as
declare @sql_handle binary(8000)
select @sql_handle = sql_handle from sysprocesses where spid = 112
-- select spid,sql_handle from sysprocesses where spid = 79
-- PRINT @SQL_HANDLE
SELECT * from ::fn_get_sql(@sql_handle)
01-16-06, 12:24 #2Registered User
- Join Date
- Jun 2003
--------Try this sp deveolped by vyas--------
use master go SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create PROC dbo.ShowCodeLine ( @SPID smallint, @WAIT tinyint = 0, @NoLoop bit = 0 ) AS BEGIN --Author: Narayana Vyas Kondreddi --Date Created: 20031016 --Source: http://vyaskn.tripod.com SET NOCOUNT ON DECLARE @sql_handle binary(20), @handle_found bit DECLARE @stmt_start int, @stmt_end int DECLARE @line nvarchar(4000), @wait_str varchar(8) SET @handle_found = 0 IF @WAIT NOT BETWEEN 0 AND 60 BEGIN RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1) RETURN -1 END ELSE BEGIN SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2) END WHILE 1 = 1 BEGIN SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END FROM master.dbo.sysprocesses WHERE spid = @SPID AND ecid = 0 IF @sql_handle = 0x0 /* --If you are running this on SQL Server 2005, then change the above line to the following: IF(@sql_handle = 0x0) OR (@stmt_start = 0 AND @stmt_end = 0) --Without this change, this procedure might go into an infinite loop and needs to be killed --In SQL Server 2000, as soon as a batch completed, --the sql_handle column in sysprocesses becomes 0, but that's not the case in SQL Server 2005 */ BEGIN IF @handle_found = 0 BEGIN RAISERROR('Cannot find handle or the SPID is invalid', 16, 1) RETURN -1 END ELSE BEGIN RAISERROR('Query/Stored procedure completed', 0, 1) RETURN 0 END END ELSE BEGIN SET @handle_found = 1 END SET @line = ( SELECT SUBSTRING( text, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end WHEN -1 THEN DATALENGTH(text) ELSE (@stmt_end - @stmt_start) END ) FROM ::fn_get_sql(@sql_handle) ) RAISERROR(@line, 0, 1) WITH NOWAIT IF @NoLoop = 1 BEGIN RETURN 0 END WAITFOR DELAY @wait_str END END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOI love cursor,though I never use it.Cos' I dont want to misuse it.
01-16-06, 12:26 #3Registered User
Provided Answers: 14
- Join Date
- Jan 2003
fn_get_sql only works for spids that are currently in a running/runnable state. You can try dbcc inputbuffer for anything not currently running.
For the 255 character problem, have you adjusted your settings in Query Analyzer? GO to tools->options results tab, and increase the length there.
How are you getting a waittype of NETWORK_IO? This is a very common LastWaittype value, but if a thing is currently waiting on a network read/write, you have to check the waittype value in sysprocesses. Network_IO is 0x800.
01-16-06, 15:21 #4Registered User
- Join Date
- Nov 2005
- Copenhagen, Denmark
I've already tried the ShowCodeLine procedure, but I all get is its own error message about invalid handle. Probably because it's about a process that's not running/runnable anymore.
I'm getting processes stuck in NETWORKIO for some reason. They are UPDATE:ing or INSERT:ing a record having an Image column among others. Since the binary value is so long, I can't see the WHERE clause because of the 255 limit. I'll look at that Q.A. setting tomorrow (it's evening here right now).
I am checking the waittype in Sysprocesses, yes.