February 22, 2009 at 3:07 am
I try to create short administrative query in order to see all open processes, their resources and their sql command.
I can't understand what is wrong,
Please help!
SELECT (SELECT text FROM ::fn_get_sql(sql_handle)) AS SQL_Command,
loginame, spid, dbid, cpu, physical_io, memusage, last_batch, status, hostname, program_name, cmd
FROM master.dbo.sysprocesses
WHERE spid>50
ORDER BY cpu DESC
the error message is:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'sql_handle'.
The environment is SQL 2000 (Ent Edition).
Thank you,
February 22, 2009 at 3:25 am
I don’t think that you can pass a column from a query as a parameter to fn_get_sql function. I think that you have to get the sql_handle to a variable and use the variable as the input parameter.
In the future pleas post SQL Server 2000 questions in the forum for SQL Server 2000
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2009 at 3:35 am
Hi Adi,
I tried that before and got another error message.
DECLARE @Handle binary(20)
select @Handle= sql_handle FROM master.dbo.sysprocesses
SELECT (SELECT TEXT FROM ::fn_get_sql(@Handle)) AS SQL_Command,
loginame, spid, dbid, cpu, physical_io, memusage, last_batch, status, hostname, cmd
FROM master.dbo.sysprocesses
WHERE (loginame Not Like '%CLAL-INS\sqlservacc%') AND (spid>50)
ORDER BY cpu DESC
And the error message:
Msg 279, Level 16, State 3, Line 3
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
I have no idea what to do next... :crazy:
(In the future, I'll open new issues under the right forum).
February 22, 2009 at 3:53 am
The data type that fn_get_sql returns is text. This data type has some restrictions. One of the restrictions is that it can not be used in correlated sub query. You can just convert it to varchar(8000), but you should know that if fn_get_sql will return data that is bigger then 8000 bytes, you’ll get an error message.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 24, 2009 at 8:58 am
Hi, Ij know that I also had similar issues with table variable vs extended stored procedures in SQL 2k but I had some work around and in some instances I just used temp tables.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply