Red Gate forums :: View topic - Scripting error between versions
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager Previous Versions
SQL Packager Previous Versions forum

Scripting error between versions

Search in SQL Packager Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
rbennet8



Joined: 20 Feb 2006
Posts: 49

PostPosted: Mon Mar 12, 2007 6:07 pm    Post subject: Scripting error between versions Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Fri Mar 16, 2007 3:50 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
rbennet8



Joined: 20 Feb 2006
Posts: 49

PostPosted: Sat Mar 17, 2007 7:40 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Sun Mar 18, 2007 9:59 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
rbennet8



Joined: 20 Feb 2006
Posts: 49

PostPosted: Sun Mar 18, 2007 10:15 pm    Post subject: Reply with quote

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. Sad

Would be a good option. THe script wizard in Mgt Studio 2005 allows you to do this.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Mon Mar 19, 2007 5:09 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group