Mike, thanks for this article it saved me alot of work recently when converting a database from SQL 7 to 2000 and the two server builds had different collations.

There were a couple of issues that I ran into that I wanted to let you know about.

1. When scripting the database across there were some stored procedures that were encrypted and they didn't get scripted out but the grant statements for it did. Fortunatly I had the originals and could script them in.

2. I also ran into a problem with identity fields being reset. The case in point was that we have a transactional table where records are constantly being written to by one app then read, processed and deleted by another app and an identity field is used as a key on this table. We also have a history table which stores details about transactions being processed and it uses the identity field from the transactional table as its primary key. When doing the conversion the transactional table was empty so the identity field was reset to 1 and when trying to add new transactions to the history table we got primary key violations.

Edited by - fred1234 on 09/10/2003 11:04:21 AM

Edited by - fred1234 on 09/10/2003 11:09:04 AM