db dude (db_guy_2002@yahoo.com) writes:
> 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@algonet.se

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