SQLServerCentral / Discuss Content Posted by Mike Pearon / Article Discussions / Article Discussions by Author / Changing Rogue Database Collations / Latest Posts

RE: Changing Rogue Database Collations

SanjayAttray — Tue, 08 Jul 2008 12:32:15 GMT

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))

RE: Changing Rogue Database Collations

SanjayAttray — Mon, 07 Jul 2008 13:51:32 GMT

HI,When I execute script1 from the article I get this error:Server: Msg 536, Level 16, State 3, Line 210Invalid length parameter passed to the substring function.My database default collation is set to [b]Latin1_General_CS_AI[/b]Any idea or resolution would be appreciated.Thanks.

RE: Changing Rogue Database Collations

Jason-368451 — Wed, 01 Nov 2006 23:26:00 GMT

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.TableNameJust thought I would make the post in case anyone else has the same problem

RE: Changing Rogue Database Collations

Aardvark-240975 — Fri, 15 Jul 2005 07:14:00 GMT

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

RE: Changing Rogue Database Collations

Mauro Ciaccio — Thu, 11 Nov 2004 05:51:00 GMT

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

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

Mauro Ciaccio

RE: Changing Rogue Database Collations

Mauro Ciaccio — Thu, 11 Nov 2004 05:43:00 GMT

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 3Cannot 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

RE: Changing Rogue Database Collations

Niall Merrigan — Wed, 16 Jun 2004 10:45:00 GMT

This article just saved me hours of work.. many thanks, works like a treat

RE: Changing Rogue Database Collations

dwebb — Thu, 27 May 2004 10:57:00 GMT

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.

RE: Changing Rogue Database Collations

Botond — Mon, 15 Dec 2003 06:11:00 GMT

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,BotiEdited by - botond.baika on 12/15/2003 06:17:26 AM

RE: Changing Rogue Database Collations

Mike Pearson — Wed, 10 Sep 2003 17:27:00 GMT

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....

RE: Changing Rogue Database Collations

fred1234 — Wed, 10 Sep 2003 11:04:00 GMT

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 AMEdited by - fred1234 on 09/10/2003 11:09:04 AM

RE: Changing Rogue Database Collations

Mike Pearson — Tue, 27 May 2003 07:27:00 GMT

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....

RE: Changing Rogue Database Collations

AFPeterson — Tue, 27 May 2003 07:18:00 GMT

Looks like a good, common sense approach. Hope I never have to use it!

Changing Rogue Database Collations

Mike Pearson — Fri, 16 May 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations.asp