Re: select ... from sysprocesses

#1

Take a peek at the system function fn_get_sql (SQL 2000, sp3) in SQL Server
Books Online.

--
- Anith
( Please reply to newsgroups only )

#2

db dude (db_guy_2002 (AT) yahoo (DOT) com) writes:

Quote:

When I run the query "select * from sysprocesses", I see a field "cmd"
that contains some information about what kind of sql statement was
executed. But it does not contain the entire text of the sql query
that was run. Is there anyway, I can get the text for sql statement
also besides all the other information (such as spid, uid, dbid,
hostname, programname, etc).

There are two ways to get this information. DBCC INPUTBUFFER(spid)
gives you most recently submitted command from the client. The other
is fn_get_sql that Anith mentioned. This function was added to SQL2000
in SP3. You get the handle column from sysprocesses and passes it
to fn_get_sql, which returns you the complete text of the currently
executing scope. That is, if the client invoked stored procedure A,
which invoked stored procedure B, which inserted data into a table
with a trigger, and the trigger is currently executing, you get the
text of the trigger. The columns stmt_start and stmt_end helps you
to get the exact current statement.

--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: select ... from sysprocesses

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: select ... from sysprocesses - 08-06-2003 , 03:01 PM

Re: select ... from sysprocesses - 08-06-2003 , 04:35 PM