title: dynamic sql error: 'CREATE TRIGGER' must be the first statement in a query batch
body: <p>As part of some administrative tasks, we have many tables that each need a trigger created. The trigger will set a flag and the date in the Audit database when an object has been modified. For simplicity, I have a table with all the objects that need triggers created.</p>
  
  <p>I am trying to generate some dynamic sql to do this for each object, but I am getting this error:<br>
  <code>'CREATE TRIGGER' must be the first statement in a query batch.</code></p>
  
  <p>Here is the code to generate the sql.</p>
  
  <pre><code>CREATE PROCEDURE [spCreateTableTriggers]
  AS
  
  BEGIN
  
  DECLARE @dbname     varchar(50),
          @schemaname varchar(50),
          @objname    varchar(150),
          @objtype    varchar(150),
          @sql        nvarchar(max),
          @CRLF       varchar(2)
  
  SET     @CRLF = CHAR(13) + CHAR(10);
  
  DECLARE ObjectCursor CURSOR FOR
  SELECT  DatabaseName,SchemaName,ObjectName
  FROM    Audit.dbo.ObjectUpdates;
  
  SET NOCOUNT ON;
  
  OPEN    ObjectCursor ;
  
  FETCH NEXT FROM ObjectCursor
  INTO    @dbname,@schemaname,@objname;
  
  WHILE @@FETCH_STATUS=0
  BEGIN
  
      SET @sql = N'USE '+QUOTENAME(@dbname)+'; '
      SET @sql = @sql + N'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'''+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]'')) '
      SET @sql = @sql + N'BEGIN DROP TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]; END; '+@CRLF
      SET @sql = @sql + N'CREATE TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates] '+@CRLF
      SET @sql = @sql + N'   ON '+QUOTENAME(@schemaname)+'.['+@objname+'] '+@CRLF
      SET @sql = @sql + N'   AFTER INSERT,DELETE,UPDATE'+@CRLF
      SET @sql = @sql + N'AS '+@CRLF
      SET @sql = @sql + N'IF EXISTS(SELECT * FROM Audit.dbo.ObjectUpdates WHERE DatabaseName = '''+@dbname+''' AND ObjectName = '''+@objname+''' AND RequiresUpdate=0'+@CRLF
      SET @sql = @sql + N'BEGIN'+@CRLF
      SET @sql = @sql + N'    SET NOCOUNT ON;'+@CRLF
      SET @sql = @sql + N'    UPDATE  Audit.dbo.ObjectUpdates'+@CRLF
      SET @sql = @sql + N'    SET RequiresUpdate = 1'+@CRLF
      SET @sql = @sql + N'    WHERE   DatabaseName = '''+@dbname+''' '+@CRLF
      SET @sql = @sql + N'        AND ObjectName = '''+@objname+''' '+@CRLF
  
      SET @sql = @sql + N'END' +@CRLF
      SET @sql = @sql + N'ELSE' +@CRLF
      SET @sql = @sql + N'BEGIN' +@CRLF
      SET @sql = @sql + N'    SET NOCOUNT ON;' +@CRLF
      SET @sql = @sql + @CRLF
      SET @sql = @sql + N'    -- Update ''SourceLastUpdated'' date.'+@CRLF
      SET @sql = @sql + N'    UPDATE  Audit.dbo.ObjectUpdates'+@CRLF
      SET @sql = @sql + N'    SET SourceLastUpdated = GETDATE() '+@CRLF
      SET @sql = @sql + N'    WHERE   DatabaseName = '''+@dbname+''' '+@CRLF
      SET @sql = @sql + N'        AND ObjectName = '''+@objname+''' '+@CRLF
      SET @sql = @sql + N'END; '+@CRLF
  
      --PRINT(@sql);
      EXEC sp_executesql @sql;
  
      FETCH NEXT FROM ObjectCursor
      INTO    @dbname,@schemaname,@objname;
  
  END
  
  CLOSE ObjectCursor ;
  DEALLOCATE ObjectCursor ;
  
  END
  </code></pre>
  
  <p>If I use <code>PRINT</code> and paste the code to a new query window, the code executes without any problem.</p>
  
  <p>I have removed the <code>GO</code> statements as this was also giving errors.</p>
  
  <p>What am I missing?<br>
  Why am I getting an error using <code>EXEC(@sql);</code> or even <code>EXEC sp_executesql @sql;</code>?<br>
  Is this something to do with the context within <code>EXEC()</code>?<br>
  Many thanks for any help.</p>