March 29, 2013 at 5:38 pm
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.
March 29, 2013 at 5:48 pm
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
March 29, 2013 at 5:56 pm
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.
March 29, 2013 at 6:02 pm
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
March 29, 2013 at 6:29 pm
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.
March 29, 2013 at 6:37 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply