Scripting error between versions

Forum for users of SQL Packager database archive utility

Moderator: David Atkinson

Scripting error between versions

Postby rbennet8 » Mon Mar 12, 2007 6:07 pm

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
rbennet8
 
Posts: 49
Joined: Mon Feb 20, 2006 10:00 pm

Postby Brian Donahue » Fri Mar 16, 2007 3:50 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby rbennet8 » Sat Mar 17, 2007 7:40 pm

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.
rbennet8
 
Posts: 49
Joined: Mon Feb 20, 2006 10:00 pm

Postby Brian Donahue » Sun Mar 18, 2007 9:59 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby rbennet8 » Sun Mar 18, 2007 10:15 pm

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.
rbennet8
 
Posts: 49
Joined: Mon Feb 20, 2006 10:00 pm

Postby Brian Donahue » Mon Mar 19, 2007 5:09 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests