Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changing Rogue Database Collations Expand / Collapse
Author
Message
Posted Friday, May 16, 2003 12:00 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 04, 2007 4:36 AM
Points: 27, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations.asp


Post #12268
Posted Tuesday, May 27, 2003 7:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 01, 2005 11:41 AM
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?
Post #62641
Posted Tuesday, May 27, 2003 7:27 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 04, 2007 4:36 AM
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....




Post #62642
Posted Wednesday, September 10, 2003 11:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 23, 2014 10:36 PM
Points: 16, Visits: 13
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



Post #62643
Posted Wednesday, September 10, 2003 5:27 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 04, 2007 4:36 AM
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....





Post #62644
Posted Monday, December 15, 2003 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 19, 2012 3:10 AM
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 :)
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



:)
Post #62645
Posted Thursday, May 27, 2004 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 12:21 PM
Points: 39, Visits: 92

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.

 

 

?




Post #118070
Posted Wednesday, June 16, 2004 10:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 16, 2004 10:43 AM
Points: 1, Visits: 1
This article just saved me hours of work.. many thanks, works like a treat
Post #121395
Posted Thursday, November 11, 2004 5:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 09, 2008 7:12 AM
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




Post #145813
Posted Thursday, November 11, 2004 5:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 09, 2008 7:12 AM
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




Post #145815
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse