2

I am using the following script:

DECLARE @dbName NVARCHAR(20) = 'ABC';
EXEC ( N' USE ' + @dbName + '
GO

-- Create Schema
CREATE SCHEMA meta
GO

-- Create Log Table
CREATE TABLE meta.LogAudit(
[EventDate] [datetime] NOT NULL DEFAULT (getdate()),
[EventType] [nvarchar](64) NULL
)
GO
'
);

but it throws me the following error.

Msg 111, Level 15, State 1, Line 5
'CREATE SCHEMA' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'GO'.

Why is that?

--

Edit:
This answer seems to be answering my question: dynamic sql error: 'CREATE TRIGGER' must be the first statement in a query batch

So it seems that in my situation I cannot program it dynamically. My whole code works in the following way:

USE DB
GO

CREATE SCHEMA SCH
GO

CREATE TABLE SCH.TABLE
GO

CREATE TRIGGER TRG
GO

So as SCHEMA and TRIGGER needs to be first query statement, it cannot be written in this way.

Community
  • 1
  • 1
DNac
  • 2,663
  • 8
  • 31
  • 54
  • Possible duplicate of [Execute Dynamic Query with go in sql](http://stackoverflow.com/questions/14473223/execute-dynamic-query-with-go-in-sql) – Tanner Dec 14 '16 at 11:55
  • 2
    You *can* program it dynamically. Just split the strings and execute them separately – Panagiotis Kanavos Dec 14 '16 at 12:32
  • @Dnac try the dynamic sql for schema creation from my updated answer below. I'm sure something similar can be created for trigger too. Just check if schema creation works for you. – andrews Dec 14 '16 at 12:34
  • Another option is to use `sqlcmd.exe` or the SMO objects to generate, [as shown in this question](http://stackoverflow.com/questions/40814/execute-a-large-sql-script-with-go-commands). – Panagiotis Kanavos Dec 14 '16 at 12:35
  • @PanagiotisKanavos agreed, I will have to come up with a bit different approach. Moreover, I do not run this for every DB, but only for some of them but I suppose that should not be serious problem either. – DNac Dec 14 '16 at 12:35
  • @DNac you can use the same approach with SMO. In your case though, I'd save the text first, so that I can version, diff etc, before applying the script – Panagiotis Kanavos Dec 14 '16 at 12:36

2 Answers2

2

Try removing comma after [EventType] [nvarchar](64) NULL, and see if the error message changes.

So you have 2 problems:

  1. As @Tanner has pointed out, you cannot use GO in dynamic SQL.
  2. You still have that trailing comma in the meta.LogAudit table columns definition.

Try running this code:

DECLARE @dbName NVARCHAR(20) = 'ABC';
declare @sql nvarchar(max) = N'exec '+ @DBName + '..sp_executesql N''CREATE SCHEMA meta'''
execute(@sql)
declare @sql2 nvarchar(max) = '
-- Create Log Table
CREATE TABLE '+ @DBName + '.meta.LogAudit(
[EventDate] [datetime] NOT NULL DEFAULT (getdate()),
[EventType] [nvarchar](64) NULL
)'
exec sp_executesql @sql2,N''

It will allow you to programmatically create schema in the specified Database as opposite to using current database.

andrews
  • 2,173
  • 2
  • 16
  • 29
  • That extra comma is of course typo, my real table has more columns, I removed those for making the situation a bit more clear. Didnt know I must not use GO in dynamic SQL though. However, when I remove it, the "CREATE SCHEMA" part still throws an error as it needs to be first statement in a query batch. – DNac Dec 14 '16 at 12:09
  • @DNac see the updated answer. I have tried it locally against my db in SQL Server 2008 R2 and it worked. – andrews Dec 14 '16 at 12:17
  • Thanks. I have rewrote my code in this manner and it works fine. Glad I have learned something new. – DNac Dec 15 '16 at 08:10
  • @DNac did you manage to apply same dynamic sql pattern to the subsequent trigger creation, which you have mentioned you needed too? – andrews Dec 15 '16 at 08:18
  • I generate 4 dynamic SQLs (create schema, table, trigger and alter trigger to enable it on) and then run one by one. – DNac Dec 15 '16 at 08:20
-1

You can use following script for solution to your requirement with the help of unsupported undocumented stored procedure sp_MSForEachDB

EXEC sp_MSForEachDB '
Use [?]; 
IF ''[?]'' = ''[ABC]'' 
begin
    -- Create Schema
    exec sp_executesql N''CREATE SCHEMA meta''
end
'
EXEC sp_MSForEachDB '
Use [?]; 
IF ''[?]'' = ''[ABC]'' 
begin 
    -- Create Log Table
    CREATE TABLE meta.LogAudit(
    [EventDate] [datetime] NOT NULL DEFAULT (getdate()),
    [EventType] [nvarchar](64) NULL,
    )
end'
Eralper
  • 6,461
  • 2
  • 21
  • 27