Using output from a stored procedure to update rows
-
I'm trying to update specific columns in a table with data returned from a DBCC but cannot figure out how to do it.
I have a table which amongst other columns, has a SPID column, and three columns mirroring the DBCC INPUTBUFFER returned dataset: EventType, Parameters and EventInfo. The SPID column is populated with n and I then want to loop through each row updating these three columns with the info from DBCC INPUTBUFFER(n).
I know I could do this with a table with three columns using INSERT INTO, but using an UPDATE statement, I don't know how to specify the columns from DBCC INPUTBUFFER I want to pull out and the columns to put them into in my table.
At the moment, knowing no better way, I was looking at looping through the rows in my table, putting the results of a DBCC INPUTBUFFER into a temp table, and pulling them from that table into my table.
Is there a better way to do this?
Thanks.
-
I'm not sure I'm understanding. Are you dropping the results from DBCC INPUTBUFFER into another table? And then are you trying to update something else? Maybe you could give more specific examples and code for your INSERT.
I can't imagine too much data here, so looping shouldn't be too much of a performance issue.
-
I've got a table which captures the recordset from sp_who2 (all the columns it returns) but using:
INSERT INTO Who2Info EXEC SP_WHO2
The table mirrors the columns in the recordset returned by sp_who2, but also has additional columns called EventType, Parameters and EventInfo - the same columns returned by DBCC INPUTBUFFER.
Straight after calling the above INSERT, I want to loop through the rows I just inserted and populate the 3 additional columns by calling DBCC INPUTBUFFER and passing in the SPID for each row. However, I'm unsure how to do this using an UPDATE statement. I don't know how to reference the specific columns returned by calls to DBCC commands, but I want to do something like:
UPDATE Who2Info
SET EventType = (EventType column from DBCC INPUTBUFFER(Who2Info.SPID)),
Parameters = (Parameters column from DBCC INPUTBUFFER(Who2Info.SPID)),
EventInfo = (EventInfo column from DBCC INPUTBUFFER(Who2Info.SPID))
WHERE EventType IS NULL
Hope this is clearer than my original post!
PS - Using SQL Server 7
-
Once you have your output from exec sp_who2 tabled, you use the same method to capture your DBCC output and join the two temp tables together on spid...
create table #temp (EventType Nvarchar(30), Paramenters int, EventInfo Nvarchar(255))
<Loop through your spids as you mentioned wanting to do>
INSERT INTO #temp
EXEC ('DBCC INPUTBUFFER(' + cast(@spid as nvarchar(20)) + ')'
<End your loop>
Now you can join #temp on your Who2Info tabel and run one mass update off of #temp. If you have a preference for sp_executesql you can do this instead..
create table #temp (EventType Nvarchar(30), Paramenters int, EventInfo Nvarchar(255))
declare @temp nvarchar(255)
<Loop through your spids as you mentioned wanting to do>
set @text = 'DBCC INPUTBUFFER(' + cast(@spid as nvarchar(20)) + ')'
insert into #temp
exec sp_executesql @text
<End your loop>
Hope that helps.
-
I did think about that but I figured I'd have to process and update one row at a time in my Who2Info table (populate #temp with results from DBCC INPUTBUFFER call, update Who2Info table, delete row in #temp, move onto next row in Who2Info) as the SPID isn't returned by the results from DBCC INPUTBUFFER.
I'm pretty green at using the EXEC command to populate rows in a table. When doing this, is it possible to pass in other values as well to populate other columns in the same row? To clarify what I'm trying to ask, I've added some text, in red, to your example - I realise this is wrong but hopefully the point will come across in it!
create table #temp (EventType Nvarchar(30), Paramenters int, EventInfo Nvarchar(255), SPID int)
<Loop through your spids as you mentioned wanting to do>
INSERT INTO #temp
EXEC ('DBCC INPUTBUFFER(' + cast(@spid as nvarchar(20)) + ')', @spid
<End your loop>
If this can be done somehow, it'll solve this and a few other problems I've faced. I've tried googling to see if this can be done but finding it difficult to pen a search phrase that'll point me in the right direction!
-
It sounds like what you're trying to do can be more easily accomplished by using traces. Is there a reason you are avoiding those?
K. Brian Kelley
@kbriankelley -
I don't think you can carry you spid the way you want, but it's not that hard to make happen without having to dump #temp. Run your update on the SPID column immediately following the EXEC statement. If done right, there should never be more than one record in your temp table with a null SPID value, so take advantage of that. You could also use a nonsense spid value as a default for your temp table and just check for that nonsense value in the WHERE clause of your update. Whatever makes you happier...
create table #temp (EventType Nvarchar(30), Paramenters int, EventInfo Nvarchar(255), SPID int)
<Loop through your spids as you mentioned wanting to do>
INSERT INTO #temp (EventType, Paramenters, EventInfo)
EXEC ('DBCC INPUTBUFFER(' + cast(@spid as nvarchar(20)) + ')'
Update #temp
Set SPID = @spid
Where SPID IS NULL
<End your loop>
-
Thank you all for the responses.
I've gone with Sean May's suggestion of populating the spid column straight after populating the other columns with DBCC INPUTBUFFER. This is a vast improvement on my original plan of inserting, copying, deleting one row at a time!
I didn't really understand the suggestion about using traces - the only traces I know about are to do with using Profiler (and of those I don't know much at all!). If you're happy to point me in the direction of some info to read up on about traces, I'd be grateful.
Once again thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply