Migrating stored procedures from SQL Server 2000 compatibility mode to a SQL Server 2005 database
Home > IT Answers > SQL Server > Migrating stored procedures from SQL Server 2...
SQL Server Ask the Experts
2445 pts.
0
Q:
Migrating stored procedures from SQL Server 2000 compatibility mode to a SQL Server 2005 database
SQL Server 2000, Stored Procedures, SQL Server 2005, SQL Server migration
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?
ASKED: Jun 24 2008  8:00 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46795 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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.
Last Answered: Jun 24 2008  10:30 PM GMT by Mrdenny   46795 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Mrdenny   46795 pts.  |   Jun 24 2008  10:30PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

 
0