I have undertaken the following process to convert a database to unicode support. This is sql 2000 SP4
- Create a new database dbnew
- Script the old database dbold with all objects, everything, and dependencies
- Global replace varchar with nvarchar (etc etc) in the script
- Execute the script to create all objects into dbnew
- (Objects all exist fine)
- Startup DTS and choose olddb as the source, newdb as the destination
- On DTS step 3 choose "Copy Objects and Data between Sql Server Databases"
- Untick "Create destination objects"
- Change copy data to append data (all tables in dbnew are empty)
- Tick copy all objects
- Untick "Use default options" and clear every option (so hopefully we are only copying data)
- Click next and run
DTS gets through the first "phase" to 100% but then it fails on a duplicate key error on a table that has a unique key on its (now nvarchar) description field
Yet in Query Analyser I can do "insert into failingtable select * from olddb..failingtable" and the data comes across fine.
So why is it failing in DTS ? And are there any other options or settings I can try ?
thanks
One development on this..
The tables that are getting across are showing the nvarchar data as chinese symbols (where the source db was varchar not nvarchar, so just A-Z ascii etc). So I think this problem translates to how to get DTS to copy varchar data into nvarchar fields
I have been reading this article
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
Which implies that everything is ok copying varchar to nvarchar, not so in my case. I think possible m$ only tested their software with US collection sequence and not UK default. ? Otherwise I'm lost.
sql
No comments:
Post a Comment