CODEWITHSUNDEEP
sql-server-2005sql-server-2008-r2
zey
zey

Reputation: 6103

Error on running database script

I generate script of my database (types of data to script = schema and data) from SQL Server 2008 R2 and run this script in SQL Server 2005, it shows me these errors:

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

Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'HONOR_BROKER_PRIORITY'.

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sys.sp_db_vardecimal_storage_format'.

at State 1, Line 1

ALTER DATABASE [MYDATABASE] SET COMPATIBILITY_LEVEL = 100

at State 6, Line 1

ALTER DATABASE [MYDATABASE] SET HONOR_BROKER_PRIORITY OFF

at State 62, Line 1 

EXEC sys.sp_db_vardecimal_storage_format N'MYDATABASE', N'ON'

How can I fix this ?

Upvotes: 1

Views: 2311

Answers (1)

marc_s
marc_s

Reputation: 754598

ALTER DATABASE [MYDATABASE] SET COMPATIBILITY_LEVEL = 100

SQL Server 2005 doesn't have this command yet - new feature of SQL Server 2008.

You need to use this instead:

EXEC sp_dbcmptlevel AdventureWorks, 90;

Level 90 = SQL Server 2005 - that'll work; level 100 is SQL Server 2008 which will not work in SQL Server 2005 obviuosly.....

ALTER DATABASE [MYDATABASE] SET HONOR_BROKER_PRIORITY OFF
EXEC sys.sp_db_vardecimal_storage_format N'MYDATABASE', N'ON'

Those are both new features in SQL Server 2008 or newer and don't have any equivalent in SQL Server 2005. The vardecimal storage format isn't supported (nor needed) in SQL Server 2005.

Upvotes: 2

Related Questions