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 |
|
Nnoel
Starting Member
2 Posts |
 Posted - 2005-11-01 : 07:35:01
|
| it appears that sp_who2 is returning an incorrect DBName when i catch the @@SPID and @@PROC from one Stored Procedure and pass those same values to another SP stored on a differect database.I am attempting to log when certain stored procedures are being run by executing a stored procedure at the start of each stored procedure I'm logging. The trouble is i'm tring to fetch the database name and SP name from the system using sysobjects and sp_who2 but sp_who2 is returning an incorrect database name so when I then try to use that database name to look into the database specific 'dbo.sysobjects' for the stored procedure name, it is returning the name of object that matches the ID but in the incorrect database.any thoughts ? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Nnoel
Starting Member
2 Posts |
Posted - 2005-11-02 : 04:25:49
|
| ok, I've found the problem with this one, which was fairly simple to understand but probably not the easiest to find so I'll share the answer for everyone.basically, sp_who2 can passed the @@SPID so it only returns one result, relateing to the current process, but when I grabbed the @@SPID in Stored Procedure A(located on Database X) and passed it to Stored Procedure B(located on database Y), if I had called sp_who2 while still in [A], it would have returned dbname = 'X', but because I called sp_who2 in B, and the @@SPID was the same for both procedures, as they were running in the same process, sp_who2 now returned dbname = 'Y'. Too simplify things I called DB_NAME() in A which i also passed to B along with the @@SPID |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-02 : 05:20:35
|
Thats good Idea  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Trouble with sp_who2 and Multiple DB's