SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sean Smith / usp_who5 / Latest Posts

RE: usp_who5

Sean Smith-776614 — Thu, 05 Apr 2012 13:33:31 GMT

No problem. I seem to remember something about certain "date / time" columns in some of the system tables having ridiculous values (sometimes) for some reason which could be the issue (unfortunately I don't remember 100% what the details were behind that, could have been something I briefly scanned in an article on the net...). Let me know if it continues and you manage to track down the raw data value. With this I can write handling for it. :)

RE: usp_who5

patelnikhil30-508910 — Thu, 05 Apr 2012 13:19:46 GMT

now it works. seems it was one off issue.. but I will keep you posted.. thx and nice work ..

RE: usp_who5

Sean Smith-776614 — Wed, 04 Apr 2012 19:57:23 GMT

Are you able to isolate the SPID throwing the error? If yes, are you able to identify the column in question?

RE: usp_who5

patelnikhil30-508910 — Wed, 04 Apr 2012 17:17:03 GMT

Thx for sharing the code,, I am getting the below errorMsg 535, Level 16, State 0, Line 10Difference of two datetime columns caused overflow at runtime.

RE: usp_who5

Sean Smith-776614 — Thu, 29 Mar 2012 13:29:11 GMT

The latest changes are now online!

RE: usp_who5

Sean Smith-776614 — Tue, 20 Mar 2012 15:51:10 GMT

Ah! Sorry, misunderstood. In SSMS, go to [i]Tools -> Options[/i]. From there, select [i]Query Execution[/i] and you should see [i]SET TEXTSIZE[/i]. I have it set to 2147483647. The new code (once approved by SSC.com) should automatically set this during execution. :)

RE: usp_who5

Jim Dunn-363909 — Tue, 20 Mar 2012 15:44:23 GMT

No, sorry. Where to find how to change the text size on SQL2008 as your previous thread says you could look around on a SQL 2008 server....

RE: usp_who5

Sean Smith-776614 — Tue, 20 Mar 2012 14:04:13 GMT

Location of where to find the procedure?

RE: usp_who5

Jim Dunn-363909 — Tue, 20 Mar 2012 13:15:43 GMT

I'm running SQL 2008 and can't locate where to find it, so if you could test on SQL2008, that would be great!

RE: usp_who5

Sean Smith-776614 — Tue, 20 Mar 2012 13:09:38 GMT

New features coming soon (just as soon as the folks at SSC.com approve the code changes): Rewrote Time Calculation Logic (output now DAYS HH:MM:SS) Added Fields To Output: "SQL_Statement_Current" "End_Of_Batch" "Plan_Cache_Object_Type" "Plan_Object_Type" "Plan_Times_Used" "Plan_Size_MB" Expanded "Running" Type Indicators Added System Reserved SPID Indicator To "SPECID" Added "C" Type "@v_Filter" Option Merged "I?" And "O?" Help Parameters Into "?" Changed Help Output From RAISERROR To PRINT Renamed Input Variables

RE: usp_who5

Sean Smith-776614 — Thu, 01 Sep 2011 15:31:02 GMT

Ah, yes. I think I know why. There's a setting in SSMS (buried somewhere, I can never remember the exact spot) where you can set the text output limit. In SQL Server 2005 I think it's either under [i]Tools -> Options -> Query Results -> Results to Text: [b]Maximum number of characters displayed in each column[/b][/i] (I have mine set to 8192)... or it's the [i]Tools -> Options -> Query Execution -> SQL Server -> General: [b]SET TEXT SIZE[/b][/i] (I have this set to 2147483647).In 2008 I think it may be buried somewhere else in the options, perhaps even named differently.Let me know if any of that helps and if not I will get on to a 2008 box as soon as I can and test it out.

RE: usp_who5

Jim Dunn-363909 — Thu, 01 Sep 2011 12:36:06 GMT

Works great! One problem though. I'm running on SQL2008 but I don't see everything returned back when I run exec sp_who5 'O?' for displaying all the output columns. I only receive a partial list (below) It just stops with .... on Database_Name. Any idea why?Msg 50000, Level 16, State 1, Procedure usp_who5, Line 107Output: SPECID : System Process ID with Execution Context ID Blocked : Blocking indicator (includes type of block and blocking SPID) Running : Indicates if SPID is currently executing, waiting, inactive, or has open transactions Login_ID : Displays Windows user name (or login name if user name is unavailable) Login_Name : Full name of the user associated to the Login_ID (if available) Elapsed_Time : Total elapsed time since the request began (HH:MM:SS) CPU_Total : Cumulative CPU time since login (HH:MM:SS) CPU_Current : Cumulative CPU time for current process (HH:MM:SS) Logical_Reads : Number of logical reads performed by current process Physical_Reads : Number of physical reads performed by current process Writes : Number of writes performed by current process Pages_Used : Number of pages in the procedure cache currently allocated to the process Nesting_Level : Nesting level of the statement currently executing Open_Trans : Number of open transactions for the process Wait_Time : Current wait time (HH:MM:SS) Wait_Type : Current wait type Last_Wait_Type : Previous wait type Status : Status of the current process Command : Command currently being executed SQL_Statement : SQL statement of the associated SPID Query_Plan_XML : Execution plan (XML) Since_SPID_Login : Total elapsed time since client login (HH:MM:SS) Since_Last_Batch : Total elapsed time since client last completed a remote stored procedure call or an EXECUTE statement (HH:MM:SS) Workstation_Name : Workstation name Database_Name ...

RE: usp_who5

Sean Smith-776614 — Tue, 23 Aug 2011 07:46:36 GMT

The new code is live! :)

RE: usp_who5

Sean Smith-776614 — Tue, 16 Aug 2011 12:18:27 GMT

For anyone interested, I just submitted a new version of the proc (still awaiting approval from SQL Server Central though...). Recent changes:1. Minor Changes To Code Style2. Added "@v_Filter_Database_Name" Filter Variable (someone suggested this to me but I can't remember his name... sorry about that!)3. Added "Last_Wait_Type", "Query_Plan_XML", And "Wait_Type" Fields To OutputHope everyone likes the updates.

RE: usp_who5

Sean Smith-776614 — Wed, 16 Jun 2010 07:23:18 GMT

Glad you liked it and I hope it helps you out. :)

RE: usp_who5

David Bird — Wed, 16 Jun 2010 07:11:08 GMT

Thanks for sharing your stored procedure. I was successful in creating and executing this stored procedure from a user database on SQL Server 2005 and 2008. I was surprised that one of my databases was emulating SQL 2000, after I changed it to SQL 2005 it worked as expected. I guess this is not too uncommon (-:For SQL Server 2000 instances I use a slightly modified version of Mike A. Barzilli [URL=http://www.sqlservercentral.com/scripts/Miscellaneous/31267/]SP_WHO3[/URL] which is created in the master database. I prefer not to create DBA stuff in the master database, so USP_WHO5 holds potential as a replacement for this old SP on SQL 2005/2008 instances.Thanks

RE: usp_who5

Sean Smith-776614 — Tue, 15 Dec 2009 07:36:39 GMT

It was primarily developed for SQL Server 2005.

RE: usp_who5

shell_l_d — Tue, 15 Dec 2009 07:33:05 GMT

Ran it in SQL Server 2000 & ran into a few errors...1) NVARCHAR (MAX) x 2 had to be replaced with NVARCHAR (4000)2) RAISERROR( '....' x 2 had to be shortened: Cannot specify user error format string with a length exceeding 440 bytes.3) exec @vSQL_String fails as @vSQL_String truncated at 4000 chars[code]-- http://www.sqlservercentral.com/scripts/sp_who/68607/--exec msdb.dbo.usp_who5 'A'--exec msdb.dbo.usp_who5 'B'--exec msdb.dbo.usp_who5 'X'--exec msdb.dbo.usp_who5 'I?'--exec msdb.dbo.usp_who5 'O?'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET NOCOUNT ON------------------------------------------------------------------------------------------------------------------------ Error Trapping: Check If Procedure Already Exists And Drop If Applicable----------------------------------------------------------------------------------------------------------------------IF OBJECT_ID ('[dbo].[usp_who5]') IS NOT NULLBEGIN DROP PROCEDURE [dbo].[usp_who5]ENDGO------------------------------------------------------------------------------------------------------------------------ Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure------------------------------------------------------------------------------------------------------------------------ Purpose: Return Information Regarding Current Users / Sessions / Processes On A SQL Server Instance-- Create Date: 10/27/2009-- Created By: Sean Smith (s(DOT)smith(DOT)sql(AT)gmail(DOT)com)-- Modifications: 11/05/2009 - Converted Script To Dynamic-SQL------------------------------------------------------------------------------------------------------------------------ Main Query: Create Procedure----------------------------------------------------------------------------------------------------------------------CREATE PROCEDURE [dbo].[usp_who5] @vFilter_Active_Blocked_System AS VARCHAR (5) = NULL ,@vFilter_SPID AS SMALLINT = NULL ,@vFilter_NT_Username_Or_Loginame AS NVARCHAR (128) = NULL ,@vFilter_SQL_Statement AS NVARCHAR (4000) = NULL-- ,@vFilter_SQL_Statement AS NVARCHAR (MAX) = NULL -- NOT COMPATIBLE IN SQL 2000ASSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET NOCOUNT ONSET ANSI_WARNINGS OFFSET ARITHABORT OFFSET ARITHIGNORE ON------------------------------------------------------------------------------------------------------------------------ Error Trapping: Check If "@vFilter_Active_Blocked_System" Parameter Is An Input/Output Help Request----------------------------------------------------------------------------------------------------------------------IF @vFilter_Active_Blocked_System = 'I?'BEGIN RAISERROR ( 'INPUT INFO...'-- NOT COMPATIBLE IN SQL 2000 = string too long/* 'Syntax: EXEC dbo.usp_who5Optional Input Parameters: @vFilter_Active_Blocked_System : Limit result set by passing one or more values listed below (can be used individually or combined in any manner): A - Active SPIDs Only B - Blocked SPIDs Only X - Exclude System Reserved SPIDs (1-50) @vFilter_SPID : Limit result set to a specific SPID @vFilter_NT_Username_Or_Loginame : Limit result set to a specific Windows user name (if populated), otherwise by SQL Server login name @vFilter_SQL_Statement : Limit result set to SQL statement(s) containing specific textNotes: Blocked SPIDs (Blocked / Blocking / Parallelism) will always be displayed first in the result set '*/ ,16 ,1 ) GOTO skip_queryENDIF @vFilter_Active_Blocked_System = 'O?'BEGIN RAISERROR ( 'OUTPUT INFO...'-- NOT COMPATIBLE IN SQL 2000 = string too long/* 'Output: SPECID : System Process ID with Execution Context ID Blocked : Blocking indicator (includes type of block and blocking SPID) Running : Indicates if SPID is currently executing, waiting, inactive, or has open transactions Login_ID : Displays Windows user name (or login name if user name is unavailable) Login_Name : Full name of the user associated to the Login_ID (if available) Elapsed_Time : Total elapsed time since the request began (format HH:MM:SS) CPU_Total : Cumulative CPU time since SPID login (format HH:MM:SS) CPU_Current : Cumulative CPU time for currently executing request (format HH:MM:SS) Logical_Reads : Number of logical reads performed by the current process Physical_Reads : Number of physical reads performed by the current process Writes : Number of writes performed by the current process Pages_Used : Number of pages in the procedure cache currently allocated to this process Nesting_Level : Nesting level of the statement currently being executed Open_Trans : Number of open transactions for the process Wait_Time : Current wait time (format HH:MM:SS) Status : Status of the current process Command : Command currently being executed SQL_Statement : Returns the SQL statement of the associated SPID Since_SPID_Login : Total elapsed time since the client logged into the server (format HH:MM:SS) Since_Last_Batch : Total elapsed time since the client last completed a remote stored procedure call or an EXECUTE statement (format HH:MM:SS) Workstation_Name : Workstation name Database_Name : Database context of the SPID Application_Description : Application accessing SQL Server SPECID : System Process ID with Execution Context ID '*/ ,16 ,1 ) GOTO skip_queryEND------------------------------------------------------------------------------------------------------------------------ Declarations/Sets: Declare And Set Variables----------------------------------------------------------------------------------------------------------------------DECLARE @vFilter_Active AS BITDECLARE @vFilter_Blocked AS BITDECLARE @vFilter_System AS BITDECLARE @vSQL_String AS VARCHAR (4000)--DECLARE @vSQL_String AS VARCHAR (MAX) -- NOT COMPATIBLE IN SQL 2000SET @vFilter_NT_Username_Or_Loginame = NULLIF (@vFilter_NT_Username_Or_Loginame,'')SET @vFilter_SQL_Statement = NULLIF (REPLACE (@vFilter_SQL_Statement,'''',''''''),'')SET @vFilter_Active = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%A%' THEN 1 ELSE 0 END)SET @vFilter_Blocked = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%B%' THEN 1 ELSE 0 END)SET @vFilter_System = (CASE WHEN @vFilter_Active_Blocked_System LIKE '%X%' THEN 1 ELSE 0 END)------------------------------------------------------------------------------------------------------------------------ Main Query: Final Display/Output----------------------------------------------------------------------------------------------------------------------print '1 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)SET @vSQL_String = ' SELECT CONVERT (VARCHAR (6), SP.spid)+''.''+CONVERT (VARCHAR (6), SP.ecid)+(CASE WHEN SP.spid = @@SPID THEN '' •••'' ELSE '''' END) AS SPECID ,(CASE WHEN SP.blocked = 0 AND Y.blocked IS NULL THEN ''·············'' WHEN SP.blocked = SP.spid THEN ''> Parallelism <'' WHEN SP.blocked = 0 AND Y.blocked IS NOT NULL THEN ''>> BLOCKING <<'' ELSE ''SPID: ''+CONVERT (VARCHAR (6), B.spid)+'' • ''+(CASE WHEN B.Login_ID_Blocking = ''sa'' THEN ''<< System Administrator >>'' ELSE ISNULL (B.Login_ID_Blocking,''N/A'') END) END) AS Blocked ,(CASE WHEN SP.spid <= 50 THEN '' --'' WHEN SP.status IN (''dormant'',''sleeping'') AND SP.open_tran = 0 THEN '''' WHEN SP.status IN (''dormant'',''sleeping'') THEN '' •'' WHEN SP.status IN (''defwakeup'',''pending'',''spinloop'',''suspended'') THEN '' *'' ELSE '' X'' END) AS Running ,ISNULL (NULLIF (SP.nt_username,''''),SP.loginame) AS Login_ID ,ISNULL ((CASE WHEN SP.loginame = ''sa'' THEN ''<< System Administrator >>'' ELSE SP.loginame END),'''') AS Login_Name ,(CASE WHEN SP.spid >= 51 AND LEN ((DMER.total_elapsed_time/1000)/3600) > 2 THEN ''99:99:99+'' WHEN SP.spid >= 51 THEN ISNULL (RIGHT (''00''+CONVERT (VARCHAR (2), (DMER.total_elapsed_time/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.total_elapsed_time/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.total_elapsed_time/1000)%3600)%60),2),'''') ELSE '''' END) AS Elapsed_Time ,(CASE WHEN SP.cpu = 0 THEN '''' WHEN LEN ((SP.cpu/1000)/3600) > 2 THEN ''99:99:99+'' ELSE RIGHT (''00''+CONVERT (VARCHAR (2), (SP.cpu/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.cpu/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.cpu/1000)%3600)%60),2) END) AS CPU_Total ,(CASE WHEN DMER.cpu_time = 0 THEN '''' WHEN LEN ((DMER.cpu_time/1000)/3600) > 2 THEN ''99:99:99+'' ELSE ISNULL (RIGHT (''00''+CONVERT (VARCHAR (2), (DMER.cpu_time/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.cpu_time/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((DMER.cpu_time/1000)%3600)%60),2),'''') END) AS CPU_Current ,ISNULL (CONVERT (VARCHAR (20), DMER.logical_reads),'''') AS Logical_Reads ,ISNULL (CONVERT (VARCHAR (20), DMER.reads),'''') AS Physical_Reads ,ISNULL (CONVERT (VARCHAR (20), DMER.writes),'''') AS Writes ,(CASE WHEN SP.memusage = 0 THEN '''' ELSE CONVERT (VARCHAR (10), SP.memusage) END) AS Pages_Used ,ISNULL (CONVERT (VARCHAR (15), DMER.nest_level),'''') AS Nesting_Level ,(CASE WHEN SP.open_tran = 0 THEN '''' ELSE CONVERT (VARCHAR (10), SP.open_tran) END) AS Open_Trans ,(CASE WHEN SP.waittime = 0 THEN '''' WHEN SP.spid >= 51 AND LEN ((SP.waittime/1000)/3600) > 2 THEN ''99:99:99+'' WHEN SP.spid >= 51 THEN RIGHT (''00''+CONVERT (VARCHAR (2), (SP.waittime/1000)/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.waittime/1000)%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), ((SP.waittime/1000)%3600)%60),2) ELSE '''' END) AS Wait_Time ,RTRIM ((CASE WHEN SP.status NOT IN (''dormant'',''sleeping'') THEN UPPER (SP.status) ELSE LOWER (SP.status) END)) AS [Status] ,RTRIM ((CASE WHEN SP.cmd = ''awaiting command'' THEN LOWER (SP.cmd) ELSE UPPER (SP.cmd) END)) AS Command ,ISNULL ((SELECT [text] FROM master.sys.dm_exec_sql_text (SP.sql_handle)),'''') AS SQL_Statement ,(CASE WHEN LEN (DATEDIFF (SECOND, SP.login_time, GETDATE ())/3600) > 2 THEN ''99:99:99+'' ELSE RIGHT (''00''+CONVERT (VARCHAR (2), DATEDIFF (SECOND, SP.login_time, GETDATE ())/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.login_time, GETDATE ())%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.login_time, GETDATE ())%3600)%60),2) END) AS Since_SPID_Login ,(CASE WHEN LEN (DATEDIFF (SECOND, SP.last_batch, GETDATE ())/3600) > 2 THEN ''99:99:99+'' ELSE RIGHT (''00''+CONVERT (VARCHAR (2), DATEDIFF (SECOND, SP.last_batch, GETDATE ())/3600),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.last_batch, GETDATE ())%3600)/60),2)+'':''+RIGHT (''00''+CONVERT (VARCHAR (2), (DATEDIFF (SECOND, SP.last_batch, GETDATE ())%3600)%60),2) END) AS Since_Last_Batch ,RTRIM (SP.hostname) AS Workstation_Name ,LOWER (DB_NAME (SP.dbid)) AS Database_Name ,CONVERT (NVARCHAR (128), RTRIM (REPLACE (REPLACE (SP.[program_name],''Microsoft® Windows® Operating System'',''Windows OS''),''Microsoft'',''MS''))) AS Application_Description ,CONVERT (VARCHAR (6), SP.spid)+''.''+CONVERT (VARCHAR (6), SP.ecid)+(CASE WHEN SP.spid = @@SPID THEN '' •••'' ELSE '''' END) AS SPECID FROM [master].[sys].[sysprocesses] SP LEFT JOIN ( SELECT A.spid ,ISNULL (NULLIF (A.nt_username,''''),A.loginame) AS Login_ID_Blocking ,ROW_NUMBER () OVER ( PARTITION BY A.spid ORDER BY (CASE WHEN ISNULL (NULLIF (A.nt_username,''''),A.loginame) = '''' THEN 2 ELSE 1 END) ,A.ecid ) AS sort_id FROM [master].[sys].[sysprocesses] A ) B ON B.spid = SP.blocked AND B.sort_id = 1 LEFT JOIN ( SELECT DISTINCT X.blocked FROM [master].[sys].[sysprocesses] X ) Y ON Y.blocked = SP.spid LEFT JOIN [master].[sys].[dm_exec_requests] DMER ON DMER.session_id = SP.spid WHERE 1 = 1 'print '2 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)IF @vFilter_Active = 1BEGIN SET @vSQL_String = @vSQL_String+ ' AND (CASE WHEN SP.open_tran <> 0 THEN '''' ELSE SP.status END) NOT IN (''dormant'',''sleeping'') 'ENDprint '3 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)IF @vFilter_Blocked = 1BEGIN SET @vSQL_String = @vSQL_String+ ' AND SP.blocked <> 0 'ENDprint '4 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)IF @vFilter_System = 1BEGIN SET @vSQL_String = @vSQL_String+ ' AND SP.spid >= 51 'ENDprint '5 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)IF @vFilter_SPID IS NOT NULLBEGIN SET @vSQL_String = @vSQL_String+ ' AND SP.spid = '+CONVERT (VARCHAR (10), @vFilter_SPID)+' 'ENDprint '6 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)IF @vFilter_NT_Username_Or_Loginame IS NOT NULLBEGIN SET @vSQL_String = @vSQL_String+ ' AND CONVERT (NVARCHAR (128), ISNULL (NULLIF (SP.nt_username,''''),SP.loginame)) = '''+@vFilter_NT_Username_Or_Loginame+''' 'ENDprint '7 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)IF @vFilter_SQL_Statement IS NOT NULLBEGIN SET @vSQL_String = @vSQL_String+ ' AND (SELECT [text] FROM master.sys.fn_get_sql (SP.sql_handle)) LIKE ''%''+REPLACE (REPLACE (REPLACE ('''+@vFilter_SQL_Statement+''',''['',''[[]''),''%'',''[%]''),''_'',''[_]'')+''%'' 'ENDprint '8 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)SET @vSQL_String = @vSQL_String+ ' ORDER BY (CASE WHEN SP.blocked = 0 AND Y.blocked IS NULL THEN 999 WHEN SP.blocked = SP.spid THEN 30 WHEN SP.blocked = 0 AND Y.blocked IS NOT NULL THEN 20 ELSE 10 END) ,SP.spid ,SP.ecid 'print '9 vSQL_String===='print @vSQL_Stringprint LEN(@vSQL_String)EXEC (@vSQL_String)skip_query:GO[/code]

RE: usp_who5

Sean Smith-776614 — Wed, 11 Nov 2009 11:55:21 GMT

I know that in SQL 2000 you used to be able to do it, but I believe it was only possible one SPID at a time, and not part of a batch (notes below from BOL 2000).Any particular reason you are staying in compatibility mode 8 and not moving to 9?MS BOL 2000: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 = 52SELECT * FROM ::fn_get_sql(@Handle)

RE: usp_who5

cheiku — Wed, 11 Nov 2009 10:33:49 GMT

When I executed sp, i got following error-------------------Msg 102, Level 15, State 1, Line 35Incorrect syntax near '.'.Msg 102, Level 15, State 1, Line 35Incorrect syntax near 'B'.Msg 102, Level 15, State 1, Line 35Incorrect syntax near 'Y'.----------------When Isolated problem, found out the line raising this error is ISNULL ((SELECT [text] FROM master.sys.dm_exec_sql_text (SP.sql_handle)),'') AS SQL_StatementAfter further exploration found out underlaying error is Msg 321, Level 15, State 1, Line 1"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.I have SQL 2005 running mode 8, is there equalent to sql_handle which i can use in my enviornment?Thanks

usp_who5

Sean Smith-776614 — Thu, 29 Oct 2009 14:55:05 GMT

Comments posted to this topic are about the item [B]usp_who5[/B]