CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
jacktric
jacktric

Reputation: 393

SQL Server 2005 - Incorrect syntax near the keyword 'SET'

I generated in SQL Server Management Studio 2014 this automatic update script (after visual table editing):

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION
GO

ALTER TABLE dbo.Rilevamenti ADD
    DataScadenza datetime NULL
GO

DECLARE @v sql_variant 
SET @v = N'Data di scadenza rilevata.'

EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Rilevamenti', N'COLUMN', N'DataScadenza'
GO

ALTER TABLE dbo.Rilevamenti SET (LOCK_ESCALATION = TABLE)
GO

COMMIT

SELECT
    Has_Perms_By_Name(N'dbo.Rilevamenti', 'Object', 'ALTER') as ALT_Per, 
    Has_Perms_By_Name(N'dbo.Rilevamenti', 'Object', 'VIEW DEFINITION') as View_def_Per, 
    Has_Perms_By_Name(N'dbo.Rilevamenti', 'Object', 'CONTROL') as Contr_Per

Now I want to run the script on another database running on a SQL Server 2005 instance but when I run it I get the error:

Incorrect syntax near the keyword 'SET'

Where is the syntax error?

Upvotes: 1

Views: 2565

Answers (1)

user692942
user692942

Reputation: 16672

This is what happens when you generate a script inside a later version of SSMS (SQL Server Management Studio) then you're targeting with the script.

Comment by @axmim
I believe that SET NUMERIC_ROUNDABORT OFF and SET CONCAT_NULL_YIELDS_NULL ON doesn't exists on SQL Server 2005

Is absolutely right SET NUMERIC_ROUNDABORT wasn't introduced until SQL Server 2008.

Update: Was wrong SET SET NUMERIC_ROUNDABORT is available in SQL Server 2005 (even though the first link says it applies to SQL Server (starting with 2008)) it looks like the SET (LOCK_ESCALATION = TABLE) is the cause this wasn't introduced until SQL Server 2008.

If you are going to generate scripts using the in-built Generate Scripts task in SSMS always remember to set the Script for Server Version on the Advanced Scripting Options dialog to the correct version of SQL Server you wish to target with the script.

Advanced Scripting Options

That way the script generated will be suitable for the version of SQL Server being targeted.

Upvotes: 3

Related Questions