Changing Rogue Database Collations
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations.asp
-
Looks like a good, common sense approach. Hope I never have to use it!
What's the business problem you're trying to solve? -
Cheers for that - *sigh* I have a client that FOUR different collations to look after - still haven't convinced them to take the plunge yet....
-
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
-
Hi Fred1234 - yes recreating a table with and indentity column would reseed that table to 1 - you'd have to create that table and reseed the new table to the correct identity value. But I expect that's what you did....
-
Hi Mike!
You saved me from a lot of trouble!
For a smaller database it took me a half day to get done this job done "manual".
When I got to a bigger one, I sad no way I am going to do the same thing on this "manual", so I started looking, that's how I ran into your article.
Thanx again!
Your information saved me a lot of time and energy - I did not had to invent the wheel again 🙂
It worked 99% smooth. I had a little problem with a few (3) tables when I was migrating the data (1 - had computed columns, 2 - had special check constraints), but I've corrected those by hand. :)) Anyway: from I donn't know how many 1000 of objects, I say this is a pretty good rate. :))
And again:
Thank you very much!
Thank you very much for sharing your knowledge!
Best regards,
Boti
Edited by - botond.baika on 12/15/2003 06:17:26 AM
🙂 -
what if you just used the DTS copy sqlserver objects task and set the ScriptOptionEx integer value to 8388608
which translates to;
Do not script the collation clause if source is an instance of SQL Server version 7.0 or later. The default is to generate collation.not script the collation clause if source is an instance of SQL Server version 7.0 or later. The default is to generate collation.
?
-
This article just saved me hours of work.. many thanks, works like a treat
-
Hi,
Let me add my voice to the chorus of compliments. GREAT WORK!
The only problem I encountered was with timestamp columns, for which I kept getting the following error:
Server: Msg 273, Level 16, State 1, Line 3
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
So I had to modify all my statements to remove the insert NULL values into the timestamp columns. I would immagine the best thing though would be to not include those columns in the INSERT scripts at all. Do you agree?
Mauro Ciaccio
-
....thought it was too simple.....
By writing NULL to the timestamp columns, the checksum calculation done by your Script2 then fails.
Mauro Ciaccio
-
Hi there.
I am currently looking at ways of migrating databases to a SAN / SQL Server Cluster environment and you guessed it, the collation on the new Virtual Server is different from the original.
I tried this script out and it works very well with one exception. On scripting the 'xxxx_Old' database the scripted views were in the wrong order of creation and had to be re-arranged before running and creating the 'xxxx' database.
I have also had a look at DTS copy sqlserver objects task which also seems to do the job.
Any comments on which method is better to use?
Many Thanks for sharing your ideas.
Aardvark
-
Thank you!!
This process worked great except my master database collation was different from my new re-created database so I had to modify the script.
I was getting error
"Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."
so i modify these two parts of the script
-- Add the tables that have no Foreign-Key relationships...
INSERT INTO #Hierarchy
SELECT -1, [name] COLLATE SQL_Latin1_General_CP1_CI_AS, ' - '
FROM dbo.sysobjects
WHERE [name]COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT DISTINCT Parent FROM #Hierarchy)
AND [Name]COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT DISTINCT Child FROM #Hierarchy)
AND xtype = 'U'
-- =====================================================
-- Identify tables that will require identity insert...
-- =====================================================
UPDATE #TableInfo
SET IdentityField = 'Y'
FROM dbo.sysobjects
INNER JOIN dbo.syscolumns
ON dbo.sysobjects.id = dbo.syscolumns.id
WHERE dbo.syscolumns.status = 0x80
AND dbo.sysobjects.name COLLATE SQL_Latin1_General_CP1_CI_AS = #TableInfo.TableName
Just thought I would make the post in case anyone else has the same problem
-
HI,
When I execute script1 from the article I get this error:
Server: Msg 536, Level 16, State 3, Line 210
Invalid length parameter passed to the substring function.
My database default collation is set to Latin1_General_CS_AI
Any idea or resolution would be appreciated.
Thanks.
SQL DBA.
-
Anyone here?
Just to add. Error is in this line.
SET @strColumnList = left(@strColumnList,len(@strColumnList)-1).
I tried differently but could not get the result I wanted. like.
SET @strColumnList = LEFT(@strColumnList, CHARINDEX(' ', @strColumnList) - 1)
SET @strColumnList = LEFT(@strColumnList, NULLIF(CHARINDEX(' ', @strColumnList) - 1, -1))
SQL DBA.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply