Recently we migrated objects in SQL Server 7.0 to SQL Server 2000. We use SQL tables in 'MS Access 2000' queries by linking those tables to the Access MDB file. After the migration, a query accessing this SQL table is running for a long time and it never completed, so i had to cancel the query half way always.
This is weird and I have no idea why this is happening after migrating to SQL Server 2000. The same query was taking few minutes to complete, when it was using an object in SQL Server 7.0.
Can any one of you help me to figure out the cause for this problem?
I would assume that the cause is due to a new execution plan being determined. I suppose it could also be a bug, but I'm assuming you are on the most current service pack and it has been stable by my experience.
A plan change could have resulted from a variety of things. It could be because the new installation uses different default settings such as a change to the ANSI_NULLS or QUOTED_IDENTIFIER settings that impact the execution plan or the comparison logic. It could be due to a lack of statistics being established yet on the new server. It could be due to a decision logic change in the plan generator. If you still have access to a SQL 7.0 version of your system I'd evaluate the plan from query analyzer in both environments and try to identify what changed. It may lead you to the source of the change.
Did you try recreating the link after the migration? I had to recreate mine after our migration but we not only migrated to SQL 2000 but to a new server and domain.
Access stores the connection information and if it is different than before the link needs to be recreated
Viewing 3 posts - 1 through 2 (of 2 total)