Upgrade from 7 to 2000

[AWithers]

We have recently upgraded our database from SQL 7 to SQL 2000 and have encountered a problem with respect to the new collation features.

A number of our stored procedures create temporary tables, some of these did not now work following the upgrade and the following error is reported

Server: Msg 446, Level 16, State 9, Procedure sp_yourprocedure, Line ??
Cannot resolve collation conflict for equal to operation.

To get over this additional information is required in the column defintions

e.g. create table ##temp_table
(clientname varchar(50) collate latin1_general_cs_as )

One further thing to note, this cannot be done in advance of the upgrade as the collate syntax is not supprted by SQL 7

I hope this is useful to someone when considering/implementing an upgrade

Andy

 

[GinaD]

I have a client who is running both SLQ 2000 DB and SQL 7.0 DB's he is trying to access both the SLQ 7 and SQL 2000 DB's using Enterprise manager with a SQL 7.0 Client. They can not see the Sql 7.0 Databases but they can see the SQL 2000? Any Ideas