CODEWITHSUNDEEP
visual-studio-2017sql-server-data-toolssqlcmd
haku
haku

Reputation: 4505

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

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?

Upvotes: 17

Views: 10793

Answers (6)

hideki
hideki

Reputation: 1330

This is an old question with some good answers but every once in a while, I run into this issue and these little reminders about setting PostDeploy, etc. do nothing.

One thing I've seen in my experience is that there is, in fact, a syntax error but it's not obvious because it's invisible. VS and other file compare software (i.e. Beyond Compare) do not seem to see the invisible UTF-8 byte-order mark (BOM). Reference here.

Here is Beyond Compare showing a file compare after I removed the BOM. Beyond Compare showing a file compare after the BOM was removed.

Here is the BOM, before removal, displaying in SublimeText. The BOM, before removal, displaying in SublimeText.

Removing the BOM that was accidentally added from one of my scripts fixed my build issue.

Upvotes: 0

Robert Sievers
Robert Sievers

Reputation: 1355

Another reason this could happen is if a post deployment script has a BEGIN statement without a corresponding END line. In such a case, any subsequent GO in anther future script will cause this error. I stumbled across this due to my own absent-mindedness when editing one of the post-deployment scripts.

Upvotes: 1

rikencpatel
rikencpatel

Reputation: 53

I would like to share my experience here.

I got same error building my sql project but scenario was different and tricky.

I introduced new column in one of my database table and I needed to populate that column for already existing rows in that table. So basically it should be one time process and hence I decided to create post deployment script to do that. This post deployment script

  1. began with IF condition to make sure it run only once for a given database. Please note this does not allow GO statement.
  2. then Create Function to create temporary function. This needs GO statement before Create Function mainly because it makes changes in database schema. This was tricky because IF does not allow GO statement.
  3. then Update query using temp function to achieve my requirement. This is fine without GO statement
  4. then DROP FUNCTION to remove temporary function. This is also database schema change and ideally needs GO statement.

To handle this situation without any GO statement

  1. I created a variable let's say '@CreateFuntion NAVARCHAR(MAX)' and set it with whole Create Function statement.
  2. Executed Create Function using "EXEC sp_executesql @CreateFunction". This runs Create Function in separate batch. I was expecting Drop Function will need same treatment but in my case it worked without GO and "EXEC sp_executesql" may be because it was last statement in the script and would anyway run in next batch.
  3. Everything else as it is

Upvotes: 0

Ufuk Hacıoğulları
Ufuk Hacıoğulları

Reputation: 38468

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 :)

Upvotes: 1

Dean
Dean

Reputation: 111

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.

Upvotes: 7

haku
haku

Reputation: 4505

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

Upvotes: 22

Related Questions