Scripting error between versions

rbennet8

I have a SQL 2005 SP2 server on which I loaded my database which has SQL 2000 compatibility level of 80. I ran the packager 5 and selected SQL 2000 for the script option. As long as I build the new database on the SQL 2005 server everything works great. However, if I try to build a new database on a SQL 2000 SP4 server, it fails. The reason is in the scripting for extended properties, the 'SCHEMA' Is left in the script. Please see the two examples:
This is from the saved SQL script using Packager 5 on a SQL 2005 server and scripting the database for SQL 2000
PRINT N'Creating extended properties'
GO
sp_addextendedproperty N'MS_Description', N'The id assigned to this row', 'SCHEMA', N'dbo', 'TABLE', N'action', 'COLUMN', N'action_id'
GO

This is from the saved SQL script using Packager 5 on a SQL 2000 server and scripting the same database as above.

PRINT N'Creating extended properties'
GO
sp_addextendedproperty N'MS_Description', N'The id assigned to this row', 'USER', N'dbo', 'TABLE', N'action', 'COLUMN', N'action_id'
GO

Brian Donahue

I believe that the issue is SQL Packager packages do not dynamically adjust to the version of SQL Server that you are running them against. For instance, generating a package on SQL a SQL 2005 database and then attempting to run it on a SQL 2000 database will cause this to happen.

Unfortunately there is no workaround at this time.

rbennet8

I think you missed what I was saying. I created an install executable using Packager 5 on a database which was dbcompatible 80 (SQL 2000) from a SQL 2005 server. I chose the script for SQL 2000 option in SQL Packager 5 and it produced a mostly SQL 2000 compatible executable. However, it scripted the FULL text index scripts with SQL 2005 commands not SQL 2000 comands which cause it to fail when trying to create a new database on SQL 2000 server. I think there is a problem in packager that switches to SQL 2005 mode when scripting FULL TEXT commands.

Brian Donahue

Hi,

You mean, in the package 'advanced' options, setting the compatibility mode to SQL Server 2000? I don't think that has any effect except to actually execute a command to set the db_options to compatibility mode.

I double theck this on Monday.

rbennet8

In the extra package options and the database tab. And you are probably right it is for setting the compatibility level not the scripting option.

Would be a good option. THe script wizard in Mgt Studio 2005 allows you to do this.

Brian Donahue

Hi,

That would be useful. Packager stores static scripts internally, though, and that makes it difficult to modify them. It's much easier to create the script withe the correct syntax to begin with. Packager can create packages that will run on SQL 2000 or SQL 2005, but you can't run a database package so it will work with both, unfortunately. You would need to create two different packages, one for SQL 2000 and another one for SQL 2005.