Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CorpDirect
Yak Posting Veteran
92 Posts |
 Posted - 2006-09-19 : 21:03:38
|
| Good day,Hope someone can assist with transferring database objects and data to an instance with a different collation. Briefly, here is why we need to do this:For several years we have used SQL Server with Latin1_General_BIN collation for all our applications. Recently we purchased a module for our ERP system that requires a case-insensitive collation; the vendor recommends Latin1_General_CI_AS. In order for the integration with our ERP system to be successful, databases for both applications must be in the same instance, with the same collation.We are running SQL Server 2000 SP3a (still waiting for a patch to the ERP app to handle SP4). I have created a new instance using collation Latin1_General_CI_AS, and created matching empty databases with datafile and logfile sizes and extents as they should be. I've also used the sp_help_revlogin procedure from Microsoft ([url]http://support.microsoft.com/kb/246133/[/url]) to transfer logins to the new instance.Now I'm trying to use DTS to export existing database objects, permissions, etc. to the new instance. I'm not very familiar with DTS so am using the wizard: right-click the database and choose "Export"... When doing this I select the source and target databases of the same name; the target exists with the desired collation Latin1_General_CI_AS.BOL says I can check "Use collation" when creating the DTS Export package, and objects will be converted to use the target's collation. The DTS package successfully transfers objects and permissions. However, regardless of whether the "Use collation" option is checked, the objects created in the target database use collation Latin1_General_BIN, just like the source! (This is verified by querying INFORMATION_SCHEMA.COLUMNS and viewing COLLATION_NAME for columns on created databases.)One recent topic ([url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55484[/url]) deals with the topic of changing collation, but doesn't really address how to get your databases into the new server/instance.Can anyone offer suggestions?Thanks very much,Daniel |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-20 : 10:35:40
|
If you'd like to use DTS, then I'd suggest creating the schema first on the destination with the correct collation. Don't let DTS handle creating these objects. Once done, try the wizard again. I'd prefer bcp as well for this, but we're old 6.5 DBAs.  Tara Kizer |
 |
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-09-20 : 10:49:03
|
Thanks for the replies!Since I've never used bcp for any but the simplest exercise, I feel less likely to make a mistake using GUI-driven DTS. With your suggestions, here's what I plan to try:- generate a script for each database using Enterprise Manager- right-click database, All Tasks, Generate SQL Script- General tab: script all objects- Formatting tab: select all except "Only script 7.0 compatible..."- Options tab: select all except "script database" (already created); file format Unicode; create one file
- modify each script to use target collation- replace "COLLATE Latin1_General_BIN" with "COLLATE Latin1_General_CI_AS" throughout the file
- execute scripts using Query Analyzer in each target database on target instance
- export data from old to new instance using DTS
Does that sound like it would work? Do you foresee any problems with this, or would you suggest any changes to the plan?Thank you,Daniel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-20 : 12:09:38
|
| Your plan sounds correct.Tara Kizer |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-10-02 : 06:27:20
|
This weekend I tried implementing my plan, with minor changes as follows. Unfortunately I'm getting collation errors right away, even though I think everything has been moved to the new collation. Please look this over for me!- generated a script for all objects in each database
- edited each script to remove COLLATE <collation> clause throughout
- split each script into:1) tables, views, procedures, functions; and2) indexes, constraints, triggers (so that there would be no impact from these during data import)
- created "transfer instance" with desired collation, Latin1_General_CI_AS
- created databases in transfer instance
- executed script 1) for each database in transfer instance (create tables, views, procedures, functions)
- used DTS to transfer data- right-click target database, choose All Tasks -> Import Data- choose original database with Latin1_General_BIN collation as source- choose matching target database in transfer instance- choose "Copy objects and data between SQL Server databases"- select "Copy data" only- deselect "Use Collation"- deselect "Copy all objects"; choose all tables- deselect "Use default options"; choose "Copy full text indexes", "Generate Scripts in Unicode" and "Use quoted identifiers when copying objects"- save package on SQL Server (transfer instance), then execute from Enterprise Manager (Local Packages, right-click and Execute)
- execute script 2) for each database in transfer instance (create indexes, constraints, triggers)
- uninstall original (source) production instance; reinstall with same name and port, and desired collation: Latin1_General_CI_AS
- back up databases in transfer instance
[*]restore databases to new production instance
Following this I tried some basic operations within our ERP system, which stores its data in several SQL databases. Right away I got the following error (passed through the application):"Error 1526: Cannot resolve collation conflict for equal to operation."I ran: SELECT DISTINCT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS ...against each database in the new instance, and got back only NULL and Latin1_General_CI_AS in every database. No columns use the old collation, yet the error still occurs!Can anyone suggest what I may have done wrong? I've spent all night looking for this error, and am now reinstalling the instance again with the original Latin1_General_BIN collation in hopes of restoring my original backups to get our ERP system back online for staff this morning!Any suggestions would be greatly appreciated.Thank you, |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-02 : 09:22:51
|
"indexes, constraints, triggers (so that there would be no impact from these during data import)"I would leave Clustered Indexes in place for the load (and I would insert in clustered-index order, and provide a hint to that effect if you are using a Bulk Load method).Clustered Indexes are typically also the PKs, and you didn't mention leaving the PKs to Phase II, so you've probably got this covered "Can anyone suggest what I may have done wrong"Just to double check:TEMPDB and your database have the same collation on that machine?Can you confirm that there is no other database / server which is being referenced in the query [which is generating the "Cannot resolve collation conflict" error] which has a different collation? (You said "I tried some basic operations ... which stores its data in several SQL databases")Stick profiler on when you test the EPR system so you can see explicitly what syntax is causing the error, then stick that in QA and try it so you can narrow it down - e.g.BEGIN TRANSACTIONSELECT *FROM Database1.dbo.Table1 AS T1 JOIN Database2.dbo.Table2 AS T2 ON T2.Col2 = T1.Col2 COLLATE Latin1_General_CI_ASROLLBACK If the explicit COLLATE fixes it then there is a different between the two that needs investigation / fixing(The transaction is to stop any accidental changes to the system whilst you are experimenting - clearly it won't be needed for a simple SELECT ... but!)(I don't know enough about DTS to know how it behaves in this regard, I would have used BCP Out & In, or INSERT INTO TargetDB.dbo.MyTable SELECT MyCol1, MyCol2 ... FROM SourceDB.dbo.MyTable, and probably with an explicit COLLATE statement on every char/varchar/text column.)Kristen |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-10-02 : 14:41:39
|
| Hi Kristen,Thanks for your thoughtful response. To answer your questions:TEMPDB and all other databases have the same collation within each instance. After the migration is done only the instance being tested is running, so no confusion can occur. I have double-checked and every database in the "new" instance (and every object within it, database, table and column) all use the same collation: Latin1_General_CI_AS.As for the "several SQL databases" bit, the ERP application uses 3 different databases for its data; all of these reside within the single instance, and are named [e.g.] ERPSYS, ERPTEMP, and ERPCOMPANY. Some of the app's stored procedures or direct SQL queries make use of both true #temp tables and "temp tables" created in its ERPTEMP database.Thanks for the suggestion to try explicit collation on a query captured by SQL Trace. I will certainly give this a go to see what happens.Since posting this morning I have turned up one interesting bit: the tables involved when the error is reported are all newly created -- in fact since the migration -- in the ERPTEMP database by the application during its transction processing. New tables that I create myself automatically use the new collation, which is default in this instance/database.A mounting body of evidence points to the application...Regards,Daniel |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-03 : 02:03:54
|
| "A mounting body of evidence points to the application..."Oh Goodie! I do love a public execution! |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-10-06 : 15:52:07
|
Sure enough, it's the app! I have scoured the release notes, change logs, install guides, user manuals, administration guide, technical specifications and every other scrap of documentation I could find on this ERP application. There is no mention of a collation requirement ANYWHERE. Neither our reseller nor our paid support reps could find anything more than I found, which as simply "any collation is supported".Finally, after I had gone through many hours of work and many more of frustrated troubleshooting, the following turned up in a knowledge base article (which was not available during last month as they company was upgrading their knowledge base system):quote:
Latin1_General_BIN This collation is required for columns with character datatypes because it needs to sort and compare data based on binary sort... This is how it mimics VFP behavior while sorting and comparing SQL Server data.
VFP is Visual FoxPro. The app (born of an older accounting system) is written in Visual FoxPro. The code that creates those temp tables during transaction processing is in FoxPro classes. NO ONE COULD TELL ME THIS BEFORE I WASTED ALL THAT TIME!Ready... Aim... FIRE!Would that I could kill this thing off... but alas, that's a dream for another year. Business processes are too deeply wedded with the ERP system to make that change possible now.Thanks to all for your help on this. The collation migration attempt was a success; the project failed, trapped under weight of this old VFP carcass.Regards,Daniel |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-07 : 01:52:32
|
| We are server-collation-neutral in our application. We achieve that by explicitly putting a COLLATE on every CREATE TABLE (including #TEMP and @TableVAR) and never using SELECT ... INTO ... FROMWe also explicitly put a COLLATE on any comparison with a column in another database (for us that really only means system tables in MASTER, as any other copy of our own database is going to have our collation on our columns)So assuming that you have an appropriate Binary COLLATE on the application's own tables I reckon temporary tables is your main issue - and that can be coded against [in the application]You can of course make the whole server Latin1_General_BIN and then just code any other databases on that server that want a different/looser collation as I have indicated - just on the off chance that any of that helps!"Ready... Aim... FIRE!"Excellent!Kristen |
|
|
|
kkoolsam
Starting Member
4 Posts |
Posted - 2009-01-02 : 02:43:56
|
| HiI need to check weather the Collation needs to be same between source database and destination database? What will be impact on data if there will be difference?SAM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-02 : 03:07:00
|
quote:
Originally posted by kkoolsam
HiI need to check weather the Collation needs to be same between source database and destination database? What will be impact on data if there will be difference?SAM
please dont reopen old threads. post this as a new thread as it will increase visibility and you will get solutions quicker. Whats the purpose behind comparing collations? are you tryng to transfer objects? |
|
|
|
|
|
|
|
|
migrate to new collation