Insert Question - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert Question

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-05-22 : 14:17:52

I need to insert the results of DBCC INPUTBUFFER with the SPID number into a table, how would you do this?
The SPID # is coming from a variable and I also use this variable on the DBCC INPUTBUFFER Command.

---
http://www.ssisdude.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 14:24:50

DBCC INPUTBUFFER truncates the query, so I'm not sure that you'd want to use this. Have you tried inserting into a temp table via INSERT INTO/DBCC?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-05-22 : 14:29:11

Hey Tara,
This below works as long as I don't have a column called spid on the InputBuffer table, I'm going to add that because I need to have that sent out to someone else in a report format. But I don't know how to add the spid to the insert statement.

INSERT InputBuffer
EXEC ('DBCC INPUTBUFFER ('+@Spid+')')

---
http://www.ssisdude.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 14:31:20

Show us the DDL for InputBuffer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-05-22 : 14:37:27

Before the change I want to make I had:

INSERT InputBuffer
EXEC ('DBCC INPUTBUFFER ('+@Spid+')')

CREATE TABLE dbo.InputBuffer
(EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(255) NULL)

This above works.

Now, I need to add the Spid to this table and I don't know how to add it. The Spid is coming from a variable DBCC command and I need that inserted into the table.

CREATE TABLE dbo.InputBuffer
(Spid INT,
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(255) NULL)

---
http://www.ssisdude.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 14:47:59

You can't go directly into InputBuffer now that it has the Spid column since the output of DBCC INPUTBUFFER doesn't match your table. So you'll need to insert into a temp table (with the old layout) and then move that data into InputBuffer and also provide @Spid.

INSERT INTO #Temp(...)
EXEC ...

INSERT INTO InputBuffer(...)
SELECT @Spid, EventType, Parameters, EventInfo
FROM #Temp

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-05-22 : 15:19:48

I almost got it, but I still don't get the results I want. Spid 66 and 68 met the criteria imposed by the select statement below and the results I get are:

I'm not sure why, but I'm getting 2 different results for Spid 68.
select * from InputBuffer2
Spid EventType Parameters EventInfo
66 Language Event 0 select product...
68 Language Event 0 select product...
68 Language Event 0 select cpr...

Code:
DECLARE C CURSOR FOR
SELECT CAST(p.spid as VARCHAR(3))
FROM master.dbo.sysprocesses p
WHERE last_batch < DATEADD(mi, -5, GETDATE())
AND dbo.fncGetNumLocks(p.spid, DB_ID('EngDataMart')) > 1
GROUP BY p.spid, p.loginame, p.hostname, dbo.fncGetNumLocks(p.spid, db_id('EngDataMart'))
OPEN C
FETCH NEXT FROM C INTO @Spid
WHILE @@FETCH_STATUS=0
BEGIN
INSERT InputBuffer1
EXEC('DBCC INPUTBUFFER ('+@Spid+')')

INSERT INTO InputBuffer2
SELECT @Spid, EventType, Parameters, EventInfo
FROM InputBuffer1

FETCH NEXT FROM C INTO @Spid
END
CLOSE C
DEALLOCATE C

---
http://www.ssisdude.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 15:27:21

Probably because the spid ran another query, so it's now in the cursor.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-05-22 : 16:38:39

You're probably right, I thought the cursor would use one Spid at the time.

---
http://www.ssisdude.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources