January 14, 2009 at 8:01 pm
Dear All
Is it possible to migrate date from DB1 to DB2 with new collation?
My problem is that some of the table’s collations are different from the DB setting.
I would like to create a empty DB and copy (restore? Export/import?) all the record to the new one. What are the correct steps? Please advise.
Thank you very much.
KK
January 15, 2009 at 3:59 am
You're on the right way. Using Export/Import will transfer the data but not the collation.
[font="Verdana"]Markus Bohse[/font]
January 15, 2009 at 7:07 pm
Thanks,
Should I manually create all the tables or I can just Import/export the data?
January 15, 2009 at 8:07 pm
I got the below erros during the export/import operation.
- Execute the transfer with the TransferProvider. (Error)
Messages
ERROR
: errorCode=-1071636471 description=An OLE DB error has occurred.
Error code: 0x80040E14.
An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80040E14
Description: "Statement(s) could not be prepared.".
An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80040E14
Description: "Could not use view or function 'dbo.rep_child_v'
because of binding errors.".
An OLE DB record is available.
Source: "Microsoft SQL Native Client"
Hresult: 0x80040E14
Description: "Cannot resolve the collation conflict
between "Chinese_Taiwan_Stroke_CI_AS" and "Chinese_Taiwan_Stroke_BIN" in the equal to operation.
".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
(Microsoft.SqlServer.DtsTransferProvider)
For help,
click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
How can it be fixed? please advise. Thanks
January 16, 2009 at 12:48 am
When you simly copy the tables to another database, without creating the tables first, the new tables will be created with the default collation of the new database.
The error you got indicates that you're trying to export a view. The view is based on a query which seems to do some comparison on a char column. Is it possible that the view definition contains the COLLATE function?
[font="Verdana"]Markus Bohse[/font]
January 16, 2009 at 4:27 am
Hey there,
I found an article a while back which is incredible. You can change the collation of a db and re-format the data in situ. So in your case you could either backup and restore on new server, or just convert where it is (if downtime is acceotable).
Its quite fast to.
HTH 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 20, 2009 at 2:02 am
Thanks Schadenfreude-Mei and MarkusB.
I will try it. Thanks a lot.
January 22, 2009 at 1:02 am
It works. Thanks again.
January 22, 2009 at 4:45 am
Just in case
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply