Error executing package to create database

mgoswell

Hi,

We have had an external developer who has developed a database in SQL 2005 Express. Unfortunately, the live platform is SQL 2000. I'm not going to bore you with the caveat's as I'm sure whoever reads this has been down the same road as me with getting data onto SQL2000 from SQL2005 (although why anyone would willingly want to do this is beyond me!) Oh, hang on... I am! :evil:

Anyway, the package was created successfully in the form of an EXE which executes fine against any install of SQL2005 but not against 2000. All I packaged was the tables, SP's & data.

The question is.... Can a package that was created using a basic DB within SQL2005 be used to create the same database on SQL2000? The database was in a Compatibility Mode of (80) when packaged.

DTS Import\Export was the only workable solution in the end however it was painfully slow as the servers are not on the same VLAN and a temporary route was created specifically for this purpose. The original plan was to package to DVD and then run locally. BCP would have been the last stand before admitting defeat.

A long winded post for a simple question I grant you, so apologies for those who passed out before the end.

JonathanWatts

Hi Matt,

Sorry about the trouble that you are having. Can you double check that there is no SQL 2005 syntax in the objects contained within the express database? Also can I ask what types of failures that you are getting from the package?

Regards,

Jonathan

mgoswell

Hi Johnathan,

Thanks for the prompt reply. The package always falls over when creating the user:

Incorrect Syntax near 'LOGIN'

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'appuser')
CREATE LOGIN [appuser] WITH PASSWORD = 'p@ssw0rd'

I believe this is not a supported syntax within SQL2000...?

It never gets any further than this. I have deleted the user so no user exists with that name and have even tried creating an empty database with no users (except dbo) and performing an upgrade. Both new and upgrade fail with the error above.

Now that you have mentioned the SQL2005 syntax, I apologise for wasting your time!

I suppose the workaround would be to replace the CREATE LOGIN syntax with the sp_addlogin syntax within the schema script and re-execute against SQL2000. ? Please confirm this is correct.

Thanks (and apologies) again.

JonathanWatts

Matt,

Our problem is that mode 80 databases on SQL2005 will actually accept SQL2005 syntax. Therefore we face the problem of which syntax to use when creating a new package of a SQL2005 mode 80 database. I will raise a suggestion to the development team to see if we can do something about the SQL Syntax used when the package is created, but I have no idea on timescales.

As to why you are getting this error on a completely empty database, you have me puzzled. I have justed tried this packaging an empty db from my SQL Express box to my SQL 2000 box and did not have a problem. Can you give me any more information about the empty db? Was there any information on summary tab as to what was happening?

Finally, there may be a work-around to your issue. If you create a completely empty database on SQL 2000 Server and then create an upgrade package from your SQL2005 Express database to this empty SQL2000 database the correct syntax should be used.

You will of course have to create another completely empty database whenever you want to run the package as an upgrade package will not automatically create a database.

Hope some of this helps.

Regards,

Jonathan

mgoswell

Hi Jonathan,

The databse which was packaged (SQL2005) was not empty - The empty database was where the package was being applied to (SQL2000). Apologies for not making this clear.

Is it possible for me to send you the package (not including the data) so you can execute it against your SQL2000 server please? I have tried it on 3 different servers here and they all terminate with the same error, syntax. Since your reply about syntax, I'm sure this is where the issue lies as SQL2000 does not support 'CREATE LOGIN'. What would be nice though is the option to continue on various error types. Is there such an option?

JonathanWatts

Matt,

Sorry, I may have over complicated my reply.

I agree with you that CREATE LOGIN is invalid SQL 2000 syntax and that this package will not execute against any SQL 2000 Server because of this.

Your best bet will be to get the package recreated if that is possible. Ensure that when the package is recreated the SQL 2005 Express db is upgraded against an empty SQL 2000 Server db. This should force SQL Packager to use SQL 2000 syntax when creating the login.

Unfortunately allowing execution to continue after errors can cause problems with dependancies and therefore execution is stopped after errors.

Regards

Jonathan