SQL72007: The syntax check failed 'Unexpected end of file occurred.' in batch near :

Question

In SSDT project (using VS2017/VS2015, SSDT version 15.1.61702.140), I cannot get my project to build. The compiler keeps complaining about the sql statement in my PostDeploymentScript (yes, I have set the BuildAction property to PostDeploy). The sql statement is:

if ('$(env)' = 'dvp')    
BEGIN
    PRINT 'creating users for dvp'
    :r .\SecurityAdditions\usersdvp.sql 
END
ELSE IF ('$(env)' = 'qat')
BEGIN
    PRINT 'creating users for qat'
    :r .\SecurityAdditions\usersqat.sql
END 

The actual error message is:

D:\My\File\Path\PostDeploymentScript.sql (lineNum, col): Error: SQL72007:
The syntax check failed 'Unexpected end of file occurred.' in the batch near:

The line num referred in the error message in the last line (end). Any idea what's causing this?

Answer 1

Apparently the problem was due to the GO statements I had in the files I was referencing. Having GO statements inside if else block is invalid. Here is an article explaining that. I was able to get it work by removing all GO statements from the referenced files and by splitting if else to two if.

IF ('$(env)' = 'dvp')
BEGIN 
    :R .\SecurityAdditions\UsersDVP.sql
END

IF ('$(env)' = 'qat')
BEGIN
    :R .\SecurityAdditions\UsersQAT.sql
END
GO