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 

Answer 2

I had this same error because I forgot to end one of the scripts being included in the post deployment script with a GO statement. What makes it hard fix is that the error will point to the first line in the next script instead of the script where the GO statement is missing.

Answer 3

I ran into this issue while I was trying to create database users in a SQL Database project. Setting the build action to None is no use because then your script doesn't run during the deployment.

I was using a script like this to create the users:

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name='$(DbUserName)')
    BEGIN
        CREATE USER [$(DbUserName)] WITH PASSWORD = '$(DbPassword)';
        ALTER ROLE [db_owner] ADD MEMBER [$(DbUserName)];
    END

I had two SQLCMD variables in the project file and setting a default value for one of them actually resolved the issue. It's really weird but I hope this helps some poor soul one day :)