Migrating stored procedures from SQL Server 2000 compatibility mode to a SQL Server 2005 database

Tags:
SQL Server 2000
SQL Server 2005
SQL Server migration
Stored Procedures
I recently migrated some reports and stored procedures from a SQL Server 2005 database server in SQL Server 2000 compatibility mode to a SQL Server 2005 database. Afterwards, we have not been able to get the stored procedures to run to completion. We have found two issues: one is collation between columns on certain tables and the second is the date parameter being passed to the stored procedure. The procedure runs in 5 seconds on our SQL Server 2000 compatible database and never finishes on SQL Server 2005. Why are these issues occuring?

Answer Wiki

Thanks. We'll let you know when a new response is added.

It could be any number of things. If you are having collation problems you’ll need to resolve those before performance will get any better. Having to collate the data on the fly from one collation to another will cause the SQL Server to need to do a full table scan instead of an index seek.

I’m not sure what the data error could mean without having the actual error message. If you could post that to the forum it would be most helpful. On the SQL 2005 database do you have all the indexes in place? Is the SQL 2000 compatible database running in SQL 2000, or SQL 2005? When you moved to the SQL 2005 database did you update the statistics or rebuild the indexes?

Sorry I can’t provide a quick answer, but your issue requires more questions before answers can be given.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following