Executing result without copying and pasting

I must be overlooking something because I cant figure out how to execute the
results of a query. The query result is over 8000 characters long so I cant
put it in a variable without having more than one and appending them togethe
r
(which caused some other issues when I have tried that).
In this boundaries I have to work within I also cant create the query as a
stored proc and then run it, nor am I able to output to file and then execut
e
the file. I have tried dbcc oututbuffer/inputbuffer, but it has to be
executed from another window/login.
A little background is the original querys output creates another query, and
the output will be different depending on which database the query is ran on
.
The query consists of several cursors. Until this point I have been
copying and pasting the results output into the input and running it. I need
to simplify this for others to run so that it is a one step process.
Thanks for pointing me in the right direction.I *think* I follow what you're saying (let me know
if I'm wrong), but you're having a problem with
a proc creating a secondary query that you can't
run via dynamic sql because the size is too big.
Am I close?
If so, look up the EXECUTE statement in BOL
Here's the snippet to pay close attention to
Using EXECUTE with a Character String
Use the string concatenation operator (+) to create large strings for
dynamic execution. Each string expression can be a mixture of Unicode and
non-Unicode data types.
Although each [N] 'tsql_string' or @.string_variable must be less than 8,000
bytes, the concatenation is performed logically in the SQL Server parser and
never materializes in memory. For example, this statement never produces the
expected 16,000 concatenated character string:
EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')
Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE
statement is executed.
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:12D2E96B-588C-468E-9391-2330015EAA31@.microsoft.com...
> I must be overlooking something because I cant figure out how to execute
the
> results of a query. The query result is over 8000 characters long so I
cant
> put it in a variable without having more than one and appending them
together
> (which caused some other issues when I have tried that).
> In this boundaries I have to work within I also cant create the query as a
> stored proc and then run it, nor am I able to output to file and then
execute
> the file. I have tried dbcc oututbuffer/inputbuffer, but it has to be
> executed from another window/login.
> A little background is the original querys output creates another query,
and
> the output will be different depending on which database the query is ran
on.
> The query consists of several cursors. Until this point I have been
> copying and pasting the results output into the input and running it. I
need
> to simplify this for others to run so that it is a one step process.
> Thanks for pointing me in the right direction.
>|||Right now it is a sql query that is ran, then the results(output) are copied
and pasted into another query window and that query is ran. IT isnt a sp at
this point.
Below is the syntax of the first query
set quoted_identifier off
go
declare @.table_name varchar(50),
@.column_name varchar(50),
@.column_dtype varchar(25)
declare table_names cursor for
select name from sysobjects where type = 'U' and name not in
('dtproperties') And name in (select o.name from sysobjects o inner join
syscolumns c on o.id = c.id and c.name = 'rowguid' and o.xtype = 'U' and
o.name not like 'conflict%' and o.name not like 'msm%')
order by name asc
open table_names
fetch next from table_names
into @.table_name
while @.@.fetch_status = 0
begin
print 'create trigger ' + @.table_name + '_audit_update on ' + @.table_name
print 'for update'
print 'not for replication'
print 'as'
print ''
declare table_columns cursor for
select name,xtype from syscolumns where id =
(select id from sysobjects where name = @.table_name)
open table_columns
fetch next from table_columns
into @.column_name,@.column_dtype
while @.@.fetch_status = 0
begin
if @.column_dtype not in ('35','34','99','61','108')
begin
print 'if update (' + @.column_name + ') and ((select top 1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
print 'begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id, req_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, req_id from inserted'
end
else
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid from inserted'
end
print 'end'
print ''
end
else if @.column_dtype in ('35','99')
begin
print 'if update (' + @.column_name + ')'
print 'begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, case_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
print 'end'
print ''
end
else if @.column_dtype in ('61','108')
begin
print 'if update (' + @.column_name + ') and ((select top 1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
print 'or (select top 1 ' + @.column_name + ' from inserted) is
null and (select top 1 ' + @.column_name + ' from deleted) is not null'
print 'or (select top 1 ' + @.column_name + ' from deleted) is
null and (select top 1 ' + @.column_name + ' from inserted) is not null'
print 'begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id, req_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
print 'end'
print ''
end
else
begin
print 'if update (' + @.column_name + ')'
print 'begin'
print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+','+"'"+'Image has been
updated'+"'"+ ',system_user,@.@.spid from deleted d, ' + @.table_name + ' r
where r.rowguid = d.rowguid'
print 'end'
print ''
end
fetch next from table_columns into @.column_name,@.column_dtype
end
close table_columns
deallocate table_columns
print 'go'
fetch next from table_names into @.table_name
end
close table_names
deallocate table_names
go
I take this output (a create trigger statement for each table in my
database) and run it against the database. The first query is a script
generator, when its output is ran the triggers are created.|||I kludged together something ugly that may give you an idea of how to go
about getting your result.. Test it out on test system first! You could
get rid of some of the @.sql variables. I only went with so many because I
wasn't sure how big this trigger code could get and I don't have a real spec
to go by.
Also, the code could be rewritten. You don't need to use a cursor and I
think some of this code
could be condensed as well.
Anyway, here's the kludge that will build the triggers and put them into the
database (for what it's worth)
I would experiement with condensing it and cleaning it up.
set quoted_identifier off
go
declare @.table_name varchar(50),
@.column_name varchar(50),
@.column_dtype varchar(25),
@.sql1 varchar(8000),
@.sql2 varchar(8000),
@.sql3 varchar(8000),
@.sql4 varchar(8000),
@.sql5 varchar(8000),
@.sql6 varchar(8000),
@.sql7 varchar(8000),
@.sql8 varchar(8000),
@.sql9 varchar(8000),
@.sql10 varchar(8000),
@.sql11 varchar(8000),
@.sql12 varchar(8000),
@.sql13 varchar(8000)
set @.sql1 = ''
set @.sql2 = ''
set @.sql3 = ''
set @.sql4 = ''
set @.sql5 = ''
set @.sql6 = ''
set @.sql7 = ''
set @.sql8 = ''
set @.sql9 = ''
set @.sql10 = ''
set @.sql11 = ''
set @.sql12 = ''
set @.sql13 = ''
declare table_names cursor for
select name from sysobjects where type = 'U' and name not in
('dtproperties') And name in (select o.name from sysobjects o inner join
syscolumns c on o.id = c.id and c.name = 'rowguid' and o.xtype = 'U' and
o.name not like 'conflict%' and o.name not like 'msm%')
order by name asc
open table_names
fetch next from table_names
into @.table_name
while @.@.fetch_status = 0
begin
set @.sql1 = 'create trigger ' + @.table_name + '_audit_update on ' +
@.table_name
set @.sql1 = @.sql1 + ' for update'
set @.sql1 = @.sql1 + ' not for replication'
set @.sql1 = @.sql1 + ' as'
set @.sql1 = @.sql1 + ' '
declare table_columns cursor for
select name,xtype from syscolumns where id =
(select id from sysobjects where name = @.table_name)
open table_columns
fetch next from table_columns
into @.column_name,@.column_dtype
while @.@.fetch_status = 0
begin
if @.column_dtype not in ('35','34','99','61','108')
begin
set @.sql2 = ' if update (' + @.column_name + ') and ((select top
1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
set @.sql2 = @.sql2 + ' begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
set @.sql2 = @.sql2 + ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
set @.sql2 = @.sql2 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id, req_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
set @.sql3 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id)'
set @.sql3 = @.sql3 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, case_id from inserted'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
set @.sql4 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, req_id)'
set @.sql4 = @.sql4 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid, req_id from inserted'
end
else
begin
set @.sql5 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id)'
set @.sql5 = @.sql5 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name +
' as varchar(4000)),system_user,@.@.spid from inserted'
end
set @.sql5 = @.sql5 + ' end'
set @.sql5 = @.sql5 + ' '
end
else if @.column_dtype in ('35','99')
begin
set @.sql5 = @.sql5 + ' if update (' + @.column_name + ')'
set @.sql5 = @.sql5 + ' begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
set @.sql6 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id, req_id)'
set @.sql6 = @.sql6 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
set @.sql7 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, case_id)'
set @.sql7 = @.sql7 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
set @.sql8 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id, req_id)'
set @.sql8 = @.sql8 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
set @.sql9 = ' insert dbo.audittrail (mod_date, upd_type, tbl_name,
rec_primkey,
col_name, curr_val, username, session_id)'
set @.sql9 = @.sql9 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
set @.sql9 = @.sql9 + ' end'
set @.sql9 = @.sql9 + ' '
end
else if @.column_dtype in ('61','108')
begin
set @.sql9 = @.sql9 + ' if update (' + @.column_name + ') and
((select top 1 ' +
@.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
deleted))'
set @.sql9 = @.sql9 + ' or (select top 1 ' + @.column_name + '
from inserted) is
null and (select top 1 ' + @.column_name + ' from deleted) is not null'
set @.sql9 = @.sql9 + ' or (select top 1 ' + @.column_name + '
from deleted) is
null and (select top 1 ' + @.column_name + ' from inserted) is not null'
set @.sql9 = @.sql9 + ' begin'
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
begin
set @.sql10 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id, req_id)'
set @.sql10 = @.sql10 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('case_id') and c.name not in
('req_id'))>0
begin
set @.sql11 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id, case_id)'
set @.sql11 = @.sql11 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
and o.name= @.table_name and c.name in('req_id') and c.name not in
('case_id')) >0
begin
set @.sql12 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id, req_id)'
set @.sql12 = @.sql12 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from deleted
d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
end
else
begin
set @.sql13 = ' insert dbo.audittrail (mod_date, upd_type,
tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
set @.sql13 = @.sql13 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
@.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
@.table_name + ' r where r.rowguid = d.rowguid'
end
set @.sql13 = @.sql13 + ' end'
set @.sql13 = @.sql13 + ' '
end
else
begin
set @.sql13 = @.sql13 + ' if update (' + @.column_name + ')'
set @.sql13 = @.sql13 + ' begin'
set @.sql13 = @.sql13 + ' insert dbo.audittrail (mod_date,
upd_type, tbl_name,
rec_primkey, col_name, curr_val, username, session_id)'
set @.sql13 = @.sql13 + ' select getdate(),'+"'"+'UPDATE'+"'"+','+"'" +
@.table_name +
"'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+','+"'"+'Image has been
updated'+"'"+ ',system_user,@.@.spid from deleted d, ' + @.table_name + ' r
where r.rowguid = d.rowguid'
set @.sql13 = @.sql13 + ' end'
set @.sql13 = @.sql13 + ' '
end
fetch next from table_columns into @.column_name,@.column_dtype
end
close table_columns
deallocate table_columns
print
@.sql1+@.sql2+@.sql3+@.sql4+@.sql5+@.sql6+@.sql
7+@.sql8+@.sql9+@.sql10+@.sql11+@.sql12+@.
sql13
exec(@.sql1+@.sql2+@.sql3+@.sql4+@.sql5+@.sql6
+@.sql7+@.sql8+@.sql9+@.sql10+@.sql11+@.sq
l12+@.sql13)
fetch next from table_names into @.table_name
end
close table_names
deallocate table_names
go
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:23BE3CE1-009E-4382-B0A9-1D834FE54D09@.microsoft.com...
> Right now it is a sql query that is ran, then the results(output) are
copied
> and pasted into another query window and that query is ran. IT isnt a sp
at
> this point.
> Below is the syntax of the first query
> set quoted_identifier off
> go
> declare @.table_name varchar(50),
> @.column_name varchar(50),
> @.column_dtype varchar(25)
> declare table_names cursor for
> select name from sysobjects where type = 'U' and name not in
> ('dtproperties') And name in (select o.name from sysobjects o inner join
> syscolumns c on o.id = c.id and c.name = 'rowguid' and o.xtype = 'U' and
> o.name not like 'conflict%' and o.name not like 'msm%')
> order by name asc
> open table_names
> fetch next from table_names
> into @.table_name
> while @.@.fetch_status = 0
> begin
> print 'create trigger ' + @.table_name + '_audit_update on ' +
@.table_name
> print 'for update'
> print 'not for replication'
> print 'as'
> print ''
> declare table_columns cursor for
> select name,xtype from syscolumns where id =
> (select id from sysobjects where name = @.table_name)
> open table_columns
> fetch next from table_columns
> into @.column_name,@.column_dtype
> while @.@.fetch_status = 0
> begin
> if @.column_dtype not in ('35','34','99','61','108')
> begin
> print 'if update (' + @.column_name + ') and ((select top 1 '
+
> @.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
> deleted))'
> print 'begin'
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid, case_id, req_id from inserted'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id') and c.name not in
> ('req_id'))>0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid, case_id from inserted'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('req_id') and c.name not in
> ('case_id')) >0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid, req_id from inserted'
> end
> else
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',' + 'rowguid' + ','+"'"+ @.column_name + "'"+', cast(' + @.column_name
+
> ' as varchar(4000)),system_user,@.@.spid from inserted'
> end
> print 'end'
> print ''
> end
> else if @.column_dtype in ('35','99')
> begin
> print 'if update (' + @.column_name + ')'
> print 'begin'
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
> from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id') and c.name not in
> ('req_id'))>0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, case_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('req_id') and c.name not in
> ('case_id')) >0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey,
> col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
> @.table_name + ' r where r.rowguid = d.rowguid'
> end
> print 'end'
> print ''
> end
> else if @.column_dtype in ('61','108')
> begin
> print 'if update (' + @.column_name + ') and ((select top 1 '
+
> @.column_name + ' from inserted) <> (select top 1 ' + @.column_name + ' from
> deleted))'
> print 'or (select top 1 ' + @.column_name + ' from inserted)
is
> null and (select top 1 ' + @.column_name + ' from deleted) is not null'
> print 'or (select top 1 ' + @.column_name + ' from deleted) is
> null and (select top 1 ' + @.column_name + ' from inserted) is not null'
> print 'begin'
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id', 'req_id')) >1
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id, case_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id, r.req_id
> from deleted d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('case_id') and c.name not in
> ('req_id'))>0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id, case_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.case_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> if (select count(c.name) from syscolumns c,sysobjects o where o.id=c.id
> and o.name= @.table_name and c.name in('req_id') and c.name not in
> ('case_id')) >0
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id, req_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid,r.req_id from
deleted
> d, ' + @.table_name + ' r where r.rowguid = d.rowguid'
> end
> else
> begin
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+',cast(r.' +
> @.column_name + ' as varchar(4000)),system_user,@.@.spid from deleted d, ' +
> @.table_name + ' r where r.rowguid = d.rowguid'
> end
> print 'end'
> print ''
> end
> else
> begin
> print 'if update (' + @.column_name + ')'
> print 'begin'
> print 'insert dbo.audittrail (mod_date, upd_type, tbl_name,
> rec_primkey, col_name, curr_val, username, session_id)'
> print 'select getdate(),'+"'"+'UPDATE'+"'"+','+"'" + @.table_name +
> "'"+',d.' + 'rowguid' + ','+"'" + @.column_name + "'"+','+"'"+'Image has
been
> updated'+"'"+ ',system_user,@.@.spid from deleted d, ' + @.table_name + ' r
> where r.rowguid = d.rowguid'
> print 'end'
> print ''
> end
> fetch next from table_columns into @.column_name,@.column_dtype
>
> end
> close table_columns
> deallocate table_columns
> print 'go'
> fetch next from table_names into @.table_name
> end
> close table_names
> deallocate table_names
> go
>
> I take this output (a create trigger statement for each table in my
> database) and run it against the database. The first query is a script
> generator, when its output is ran the triggers are created.