July 14, 2003 at 9:37 am
Hi!
I have a view, that lists "who blocks whom" information (below)
After I get the results I use dbcc inputbuffer(spid) to see the event info...
How it is possible to integrate the operation "dbcc inputbuffer(93)" into the view as calculated field?
Thanks.
create view vblocked as
select
p.spid as 'who_spid', p.loginame as 'who_loginame', p.program_name as 'who_program', p.hostname as 'who_host',
pb.spid as 'block_spid', pb.loginame as 'block_login', pb.program_name as 'block_program', pb.hostname as 'block_host'
from
master.dbo.sysprocesses p (nolock)
join
master.dbo.sysprocesses pb (nolock) on pb.spid = p.blocked
where
p.blocked <> 0
dbcc inputbuffer(399)
July 16, 2003 at 3:30 am
Are you using Sql*Server 2000 SP3 ?
If so, you can use ::fn_get_sql(@handle) in the view to get the sql associated with spid.
Check out this site
http://www.databasejournal.com/features/mssql/article.php/2189761
July 16, 2003 at 3:29 pm
Excellent find! Thanks for the post.
July 18, 2003 at 7:45 am
Just created a table to hold the locks snapshots, used:
EXEC sp_tableoption 'BLOCKS_HISTORY', 'text in row', 'on',
to be able to insert text data into this table, but got an error for this code:
...
UPDATE BLOCKS_HISTORY
SET who_sql =
(SELECT text FROM ::fn_get_sql(@who_sql_handle))
WHERE (dt=@dt) and (who_sql_handle=@who_sql_handle)
...
---------------------------------------------------------
Server: Msg 279, Level 16, State 3, Line 64
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
What way can I insert text data from fn_get_sql function into my table?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply