Error on running database script

Question

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 ?

I've also change COMPATIBILITY_LEVEL = 100 (version 2008) to COMPATIBILITY_LEVEL = 90 (version 2005) , but it doesn't work too . — zey
– zey, Commented Jul 3, 2013 at 4:19

marc_s · Accepted Answer · 2013-07-03 05:16:34Z

2

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.

answered Jul 3, 2013 at 5:16

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

@zey: well, since those features aren't present in SQL Server 2005 - yes, there's no other way, really. You should check out the Generate script wizard - it has a setting that allows you to define which version of SQL Server to generate the script for. Try to set that to 2005 and see what the result would be

zey Over a year ago

Fortunately marc_s , it has Script for Server Version options which can change script version to 2005 :)

Collectives™ on Stack Overflow

Error on running database script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related