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, July 15, 2005 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, November 1, 2006 11:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 9:41 PM
Points: 5, Visits: 321
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
Posted Monday, July 7, 2008 1:51 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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
Posted Tuesday, July 8, 2008 12:32 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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 »

Add to briefcase ««12

Permissions Expand / Collapse