GO (batch separator)

  • If i copy/paste the output of the dynamic query and run it -then it works fine but when i run it using within dynamic sql using EXEC then it give me the following error:

    Incorrect syntax near 'GO'.

    Msg 111, Level 15, State 1, Line 8

    'CREATE VIEW' must be the first statement in a query batch.

    Msg 102, Level 15, State 1, Line 128

    What is the work around this?

    Thanks.

  • Without seeing the code you're trying to run, pretty much no idea.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/29/2013)


    Without seeing the code you're trying to run, pretty much no idea.

    Here is the Code Snippet

    DECLARE @sql NVARCHAR(MAX)=''

    DECLARE @CreateViewSQL NVARCHAR(MAX)=''

    DECLARE @IfExistsSql NVARCHAR(MAX)=''

    SET @IfExistsSql=

    'IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'''+'['+@ClientName+']'+'.'+'['+@ViewName+']'+'''))

    DROP VIEW' +' ['+@ClientName+']'+'.'+'['+@ViewName+']' +CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)

    SET @CreateViewSQL=REPLACE(REPLACE(REPLACE(@ViewDefinition,'Schema1',@ClientName),'<>','='),'Schema2',@DomainName)

    SELECT @sql=@IfExistsSql+@CreateViewSQL + CHAR(13)+CHAR(10)+'GO'

    EXEC(@SQL) --this gives me the error I mentioned above.

    but if i use PRINT(@SQL) and paste it in a new window-it executes. Looks like its the problem with 'GO' batch separator.

  • Because GO is not a T-SQL statement. It's a client tool command. SQL Server has no idea what it means.

    Split that into two pieces of dynamic SQL, one that drops the view, one that creates it, the IF doesn't need to be dynamic, so you can do something like this.

    IF EXISTS ...

    BEGIN

    SET @sql = 'DROP VIEW ...'

    EXEC (@SQL)

    END

    SET @sql = 'CREATE VIEW ...'

    EXEC (@SQL)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/29/2013)


    Because GO is not a T-SQL statement. It's a client tool command. SQL Server has no idea what it means.

    Split that into two pieces of dynamic SQL, one that drops the view, one that creates it, the IF doesn't need to be dynamic, so you can do something like this.

    IF EXISTS ...

    BEGIN

    SET @sql = 'DROP VIEW ...'

    EXEC (@SQL)

    END

    SET @sql = 'CREATE VIEW ...'

    EXEC (@SQL)

    Thank You for prompt reply. I have been crawling through internet and haven't been able to trace a solution/work around. It has to be dynamic because I am looping through like around 100 scripts (views) for a client and i have a bunch of clients. Splitting the dynamic sql sounds like and idea but i am not sure how to go about it and if thats possible here.

  • Sure it's possible. You'd do it pretty much like this (based on the portion of code that you posted)

    DECLARE @CreateViewSQL NVARCHAR(MAX)=''

    DECLARE @DropViewSql NVARCHAR(MAX)=''

    IF EXISTS (SELECT 1 FROM sys.views v INNER JOIN sys.schemas AS s ON v.schema_id = s.schema_id WHERE s.name = @ClientName AND v.name = @ViewName)

    BEGIN

    SET @DropViewSql='DROP VIEW' +' ['+@ClientName+']'+'.'+'['+@ViewName+']'

    EXEC (@DropViewSql)

    END

    SET @CreateViewSQL=REPLACE(REPLACE(REPLACE(@ViewDefinition,'Schema1',@ClientName),'<>','='),'Schema2',@DomainName)

    EXEC (@CreateViewSQL)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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