Problems with transferring from SQL 6.5 to 2000

[amcg]

The database application is a fairly simple one, using an Access 97 front end communicating with a SQL Server 6.5 back end. Typical operations carried out on the data would be straightforward enough, i.e. adding/deleting/editing records.

We want to transfer the data from 6.5 to a new SQL 2000 server and I've written some fairly extensive test programs for the application. We transferred a test copy of the database onto 2000 and ran the same test program multiple times, always getting very strange results.

So, what I'm wondering is if there's some fundamental difference in the way that SQL 2000 communicates with Access? The SQL is fine - we ran it directly through 2000 and got the "correct" results. The problem only occurs when we run the program using the VB code in Access.

Any help would be greatly appreciated, as the sooner we get the database onto 2000 the better.

 

[allanon]

We have done the same thing and are not having any problem....You will have to give an example of how it is behaving strangely. I would think that you are using SQL Server for your DSN.

 

[allanon]

We have done the same thing and are not having any problem....You will have to give an example of how it is behaving strangely. I would think that you are using SQL Server for your DSN.

 

[amcg]

In answer to your question, yes.

As requested, a few examples...

Part of the test program simply counts the amount of records in each SQL table before and after every operation.

An initial record count is also done before any operations are carried out in order to initialise the test.

The first problem: After the initialising count, SQL 2000 regularly returns between 30 and 300 more records in some of the tables than actually exist.

Next, much of the time 2000 will not allow the test program to add a record to a certain table. This is not permissions related, and I cannot work out why this problem is occuring.

Then, when a record does get added to a table, it has got added anywhere from 2 to 4 times. But, according to the resulting test report, they were all added at once (i.e. it isn't a looping problem).

We are certainly confused.

Out of curiosity, would it make a difference whether the database was simply copied from 6.5 to 2000 or if it was actually upgraded using the 2000 wizard? (As far as I know the data was simply copied into 2000 but I could well be mistaken)

 

[allanon]

Let us hope that the data was not just copied. There are some HUGE differences in 2000 from 6.5. If you just copied the data I would suspect your data right from the beginning. Field types have been changed. Warning messages and error handling is different. Nulls are handled a LOT differently.

STEP1. Use the upgrade wizard. Even with the wizard you may encounter some growing pains but they will be less.

STEP2. Upgrade all your sql drivers.. MDAC must be upgraded on ALL client machines.

With prior versions of SQL Server (6.5 and under) nulls were ignored in agregate functions. In SQL Server 7.0 and up nulls cause warning messages. This will cause results to be VERY different in the versions.

I am right now in the process of upgrading our SQL Server from 6.5 and I have to say it is a TON of work. We have had three people working on it for 4 months now and are expecting to spend another 2 months on it for just 8 applications (databases).

 

[amcg]

Thanks for your reply, it actually hit on a number of things which we had considered to be real candidates to be our chief problem-causers. I've passed these pointers on to my SQL Server guy and he'll get to it.

Thanks again.