Clever Geek Handbook

SSMS in SQLCMD mode - invalid syntax

This is my day for strange mistakes.

I am using a database project and as part of Post Deployment, we are using SQL Scripts to populate the 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 scenarios 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 any command I issue. But still I am getting this error.

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

What's going on here?

+2


source to share


2 answers


It's baadddddddd

It will concatenate commands together from file to file

One file ended this way

SET IDENTITY_INSERT TABLEONE OFF

The following file started this path

SET IDENTITY_INSERT TABLETWO ON

But the first file didn't have a blank line after SET IDENTITYINSERT was turned off.



SSMS put these two files together and executed them, so the result became

SET IDENTITY_INSERT TABLEONE OFFSET IDENTITY_INSERT TABLETWO ON

and how this error appeared

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

Terrible.

I just submitted a bug to Microsoft Connect.

+3


source


Have you checked if a trigger fires in Script.EnableTriggers.sql? If you don't know your startup code, try this:

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


Let me know if you find anything.

Thanks Eric

+1


source






More articles: