DTS Info to new server

[Luvsql]

I am testing the DTS process for transferring our GP data to a new server, in order to change the collation from binary to DOCI. I have tried their recommended bcp process, but it's way too combersume and too many steps.

I have recreated all our databases onto the new server, on the DOCI sort order. I have installed GP and logged into each company and installed all third parties sp and tables etc. I then truncated all tables in all databases.

I DTS's one database as a test (went pretty quick), but it failed on a few databases due to "Violation of primary key" on a few tables. The first one is the CM20300 table. NOne of the keys on this table, that are char or text, are case sensitive Ie the key values are either generated by GP or the ID fields are forced uppercase by the application.

I tried one of our bigger databases and even more failed due to primary key violations.

My question is, how are primary key violation values in the table, if SQL does not allow it? Is there any maintenance we can run to check for these duplicate values in all tables?

 

[bawa63]

I would suggest rather than truncating all the tables and populating them again, take a backup of your old database and restore it on the new database. I guess it would leave the sort order the same of the New database.

 

[Luvsql]

My whole point of doing this is to change the sort order. If I restore from a backup, it restores the original sort order.

 

[jymm]

I ran into something similar and this article seemed to get me going in the right direction...

http://databasejournal.com/features/mssql/article.php/1587631