capture sql statements

Question

Post reply

capture sql statements

Jamie-136634

Old Hand

Points: 368
More actions
July 10, 2009 at 10:19 am

#131891

hi, I have written this script but there is an error by dbcc.
how can I do this ?thank you for any help.
CREATE TABLE #sql (dateadded datetime, sql varchar(max) )
declare @sql varchar(max)
declare @int int
set @int= 1
while @int < 200
begin
set @sql = dbcc inputbuffer(@int)
insert into #sql (dateadded,sql)
Select getdate(), @sql
set @int=@int+1
end
select * from #sql

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you had a few errors in what you were trying to do;

dbcc inputbuffer returns a table with 3 columns...you have to capture the columns.

your concatenation was not quite right;

hre's a working example of what you were trying to do, but there are better ways to do this:

CREATE TABLE #sql (dateadded datetime default getdate(), Eventtype varchar(100),Parameters int,EventInfo varchar(max) )

--drop table #sql

declare @sql varchar(max)

declare @int int

set @int= 1

while @int < 200

begin

set @sql = 'dbcc inputbuffer(' + convert(varchar(30),@int) + ' ) '

insert into #sql (Eventtype,Parameters,EventInfo)

exec( @sql)

set @int=@int+1

end

select * from #sql

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jamie-136634 Old Hand Points: 368 More actions · Answer 2

hi, I get this error :

Msg 207, Level 16, State 1, Line 9

Invalid column name 'EventInfo'.

Lowell SSC Guru Points: 323518 More actions · Answer 3

did you drop your temp table so it can be recreated with the new column?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

If you are really running SQL Server 2005 then as Lowell mentions there are better ways to get this information using the DMV's and DMF's available. Here's a link to a script here on SSC that gets what you want I think.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jamie-136634 Old Hand Points: 368 More actions · Answer 5

thank you for your help

this script now works, I'll also look at the link.