September 25, 2008 at 8:22 am
2. use xxxdb go create trigger.
Not sure on #1
September 25, 2008 at 8:24 am
Steve, check where I start the EXEC ... that is what I'm doing. Unfortunately it does not work.
September 25, 2008 at 9:25 am
Why not use sp_msforeachdb?
Would allow you to get that created....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 9:32 am
I was able to resolve it with a double exec ...
January 14, 2010 at 7:43 pm
Could you give some specifics on your solution. (double exec)
January 18, 2010 at 6:57 am
Simply wrap another EXEC around the existing EXEC. Just make sure you have all your single quotes setup properly.
September 15, 2010 at 4:12 pm
Adam
I am trying the same but getting hard time to change the single quotees i have in the trigger while wrapping double EXEC
September 16, 2010 at 7:06 am
Tara-1044200 (9/15/2010)
AdamI am trying the same but getting hard time to change the single quotees i have in the trigger while wrapping double EXEC
Usually easiest way is to do a find/replace on the single quote.
October 13, 2011 at 9:37 am
Hi Adam ,
I am working on the same requirement and having the same error
"'CREATE TRIGGER' must be the first statement in a query batch."
I appreciate if you could post the amended script.
Regards
Murali
October 13, 2011 at 9:47 am
Just add a GO on the line before the CREATE TRIGGER.
October 13, 2011 at 9:57 am
Hi ,
I am using the following code :
DECLARE @DBNAME VARCHAR(128)
DECLARE DBLIST_CURSOR CURSOR FOR
SELECT NAME FROM sys.databases WHere name not in ( 'msdb','master','model','tempdb')
OPEN DBLIST_CURSOR
FETCH NEXT FROM DBLIST_CURSOR INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @ssql VARCHAR(8000)
Set @ssql = 'USE [' + @DBNAME + ']
go
create trigger DDLChanges
on database
for
CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,
CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
CREATE_PROCEDURE,ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_STATISTICS,DROP_STATISTICS, UPDATE_STATISTICS,
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,
CREATE_TRIGGER,ALTER_TRIGGER, DROP_TRIGGER,
CREATE_USER,ALTER_USER, DROP_USER,
CREATE_VIEW,ALTER_VIEW, DROP_VIEW
as
set nocount on
declare @data xml
set @data = EVENTDATA()
insert into DBA_Maint.dbo.DDLChangeLog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''varchar(256)''),
@data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''varchar(50)''),
@data.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''varchar(256)''),
@data.value(''(/EVENT_INSTANCE/ObjectType)[1]'', ''varchar(25)''),
@data.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''varchar(max)''),
@data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''varchar(256)'')
)
'
EXEC( @SSQL)
FETCH NEXT FROM DBLIST_CURSOR INTO @DBNAME
END
Close DBLIST_CURSOR
DEALLOCATE DBLIST_CURSOR
Error:
Msg 111, Level 15, State 1, Line 2
'CREATE TRIGGER' must be the first statement in a query batch.
October 13, 2011 at 9:59 am
Change GO for ;
October 13, 2011 at 10:13 am
No luck mate
October 13, 2011 at 10:23 am
Looks like from the previous posts that you need to add another EXEC() after the use.
Viewing 14 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply