Any chance of upgrading to SQL 2000, or even SQL 2005? You can run SQL 2000 and SQL 7.0 on the same box. The SQL 2000 instance needs to be a named instance, is all.

Can you just apply the same service pack to the destination server as what is on the source server? That will fix your problem immediately and permanently.

SQL 7.0 made major schema changes from sp1 to sp2, and from sp3 to sp4.

To follow Jim's analogy, there are differences in stored procedures. So what's the problem, can't you just use the old ones? If you think that the database shouldn't complain when you restore to an older version, then I think your applicaiton ought to be as accomdating. Note that I'm being hugely sarcastic here, not serious at all.

-PatP

Note that if you were to copy from the 961 to the 1063 you'd have no problem, just like you could copy outlook data from sp1 to sp2. The problem lies in going backward, from 1063 to 961, the other direction works fine.

-PatP

I think that we are losing track of the problem here. The inital problem is a different sort order. Whether i upgrade to from 961 to 1063 the problem is still going to be there or do you think that this will sort it out ?? if so ill go right ahead and do it straight away. I cant upgrade to SQL 200 or higher sql server 7 is the company standard

I'm dead certain that installing the upgrade will be part of any solution, but I have a tiny doubt as to it being a complete solution. You'll have to bring both database engines to the same service pack level at some point in the process no matter what, but you might also need to manually drop the database from the destination server before you can restore the backup.

-PatP

I Upgraded the service pack so they are now both the same and tried the restore it didnt work.

next i copied the mdf and ldf file and tried to reattach to server but i got the same error

Server: Msg 1816, Level 16, State 1, Line 1
Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server.
Server: Msg 1816, Level 16, State 1, Line 1
Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server.
Warning: sort order 52 in database differs from server sort order of 50.
Warning: Unicode language locale 1033 in database differs from server Unicode language locale of 33280.
Warning: Unicode comparison flags 196609 in database differs from server Unicode comparison flags of 0.
Warning: sort order 52 in database differs from server sort order of 50.
Warning: Unicode language locale 1033 in database differs from server Unicode language locale of 33280.
Warning: Unicode comparison flags 196609 in database differs from server Unicode comparison flags of 0.



is there anything else i can do.

Normally I'd suggest that you contact Microsoft Premier Support Services (MS-PSS). The problem is that SQL Server 7.0 is now on Extended Support, which means that it will be expensive.

If you want to pursue additional help here, we can do that, but it means that you're going to need to "open the kimono" pretty wide, exposing a lot of infrastructure details about your server and network that I wouldn't recommend making public. At this point you need to make the call as to whether free support over the internet is worth that exposure, or if you want to pursue paid support.

A normal installation of SQL 7.0 does not have the problems you are experiencing. I've tested this, just to be sure and I can restore a flat binary DB onto a default collation, as well as a rather "kinky" collation that was inherited from a 4.2 upgrade. Something pretty far outside of "standard issue" is causing your problem, and we'll need to dig into your configuraiton one step at a time to find it.

If you do want to pursue this, run the following query on both servers:This will give us the basic breadcrumbs needed to figure out what the next question in the process needs to be.

-PatP