Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ChrisO
Starting Member
2 Posts |
 Posted - 2007-04-05 : 09:21:24
|
| Hi,I am trying to write a generic database procedure that will write to a database table in the event of an error (so it will be executed if @@ERROR <> 0) I am able to get details such as the user name, procedure being run etc, but I want to also know what SQL statement the procedure fails at. Is there any way of retrieving from the system tables what SQL is running in any given SPID?I can get the cmd column from the master.dbo.sysprocesses table but that only returns me the fact the statement is a "SELECT" or "INSERT" etc, not the entire statement. We get around this problem in VB6 by storing every SQL statement being executed in a global variable that gets written out on a crash, but dont really want to do that here as it produces much more code....Are there any tips for finding out what SQL is running under a process using system variables or system tables?Thanks |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-04-05 : 09:37:12
|
| Have you taken a look at SQL Profiler?Also - note that what you want is easy with SQL Server 2005 (DDL triggers) if upgrading is an option. |
 |
|
|
ChrisO
Starting Member
2 Posts |
Posted - 2007-04-05 : 10:12:26
|
| unfortunately, upgrading to 2005 isnt an option (yet).I'll have a look at SQL profiler, but is that going to be available from inside a DB proc - I thought it was an external tool for monitoring.....Just checked sql profiler - the 'textdata' column it produces when tracing T-SQL is exactly what I need - where does it get that from, one of the system tables? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
How to find out what SQL is running