Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Rogue Database Collations


Changing Rogue Database Collations

Author
Message
Mike Pearson
Mike Pearson
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations.asp



AFPeterson
AFPeterson
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 1
Looks like a good, common sense approach. Hope I never have to use it!


What's the business problem you're trying to solve?
Mike Pearson
Mike Pearson
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 1
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....



fred1234
fred1234
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 23
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



Mike Pearson
Mike Pearson
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 1
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....




Botond
Botond
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 15
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 Smile
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. Smile) Anyway: from I donn't know how many 1000 of objects, I say this is a pretty good rate. Smile)

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


Smile
dwebb
dwebb
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 111

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.

?





Niall Merrigan
Niall Merrigan
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
This article just saved me hours of work.. many thanks, works like a treat
Mauro Ciaccio
Mauro Ciaccio
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 34

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





Mauro Ciaccio
Mauro Ciaccio
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 34

....thought it was too simple.....

By writing NULL to the timestamp columns, the checksum calculation done by your Script2 then fails.

Mauro Ciaccio





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search