Changing Rogue Database Collations

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Mike Pearon » Changing Rogue Database Collations

14 posts, Page 2 of 2

««1 2

Changing Rogue Database Collations

Rate Topic

Display Mode

Topic Options

Author

Message

Aardvark-240975

Posted Friday, July 15, 2005 7:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 30, 2006 8:21 AM
Points: 1, Visits: 1

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

Post #201515

Jason-368451

Posted Wednesday, November 01, 2006 11:26 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 3, Visits: 266

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

Post #319817

SanjayAttray

Posted Monday, July 07, 2008 1:51 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #529611

SanjayAttray

Posted Tuesday, July 08, 2008 12:32 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #530304

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions