SSMS in SQLCMD mode - Incorrect Syntax

Question

It is my day for weird errors.

I use a database project and as a part of Post Deployment, we use SQL Scripts to populate data in our tables.

alter authorization on database::atlas to sa;
go
ALTER DATABASE ATLAS SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

:r C:\Scripts\Script.DisableTriggers.sql
:r C:\Scripts\dbo\Script.dbo.PaymentMethod.sql
:r C:\Scripts\dbo\Script.dbo.TransactionEntryMethod.sql
:r C:\Scripts\dbo\Script.dbo.TransactionTypeGroup.sql
:r C:\Scripts\dbo\Script.dbo.TransactionType.sql
:r C:\Scripts\Script.EnableTriggers.sql

each of these scripts is very similar to the one below

SET IDENTITY_INSERT [dbo].[TransactionTypeGroup] ON
INSERT INTO [dbo].[TransactionTypeGroup] (TransactionTypeGroupID,TransactionTypeGroupName) VALUES 
(1,'Inquiry')
, (2,'Points')
, (3,'Board')
, (4,'Guest Meals')
, (5,'Cash Equivalency')
, (6,'Deposits')
, (7,'Void/Void Tender')
, (8,'Refund')
SET IDENTITY_INSERT [dbo].[TransactionTypeGroup] OFF
GO

When I get my output back, I get the following error:

(1 row(s) affected)

(1 row(s) affected)

(25 row(s) affected)

(11 row(s) affected)

(2 row(s) affected)

(598 row(s) affected)

(3 row(s) affected)
Msg 102, Level 15, State 1, Line 234
Incorrect syntax near 'OFFSET'.

I have searched my entire SQL directory, and there is no word called OFFSET in any SQL file or in any command that I am issuing. But yet I get that error..

Msg 102, Level 15, State 1, Line 234
Incorrect syntax near 'OFFSET'.

What is going on here?

Answer 1

Did you check if a trigger is firing in Script.EnableTriggers.sql? If you don't know your trigger code, try this:

select * from sys.sql_modules where definition like '%OFFSET%'

Let me know if you find anything.

Thanks, Eric

Answer 2

This is baadddddddd

It will concatenate commands together from file to file

One file ended this way

SET IDENTITY_INSERT TABLEONE OFF

Next file started this way

SET IDENTITY_INSERT TABLETWO ON

But the first file did not have a blank line after the SET IDENTITYINSERT OFF.

SSMS was putting the two files together and executing them, so the output became

SET IDENTITY_INSERT TABLEONE OFFSET IDENTITY_INSERT TABLETWO ON

and that's how this error came up

Msg 102, Level 15, State 1, Line 234
Incorrect syntax near 'OFFSET'.

Horrendous.

I just submitted a bug into Microsoft Connect.