Reputation: 48048
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?
Upvotes: 2
Views: 2522
Reputation: 48048
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.
Upvotes: 3
Reputation: 1841
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
Upvotes: 1