Collation Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Collation Problem

Author

Topic

samu_78_nyc
Starting Member

11 Posts

Posted - 2008-01-17 : 21:16:11

Hi all,
I am a DB2 DBA and need some assistance with SQL server collation problem. I am trying to execute a stored procedure
on a newly installed PROD SQL server 2005 and get the following error. The collation property for PROD is

master,Latin1_General_CI_AS,90
tempdb,Latin1_General_CI_AS,90
model,Latin1_General_CI_AS,90
msdb,Latin1_General_CI_AS,90
PROD,SQL_Latin1_General_CP1_CI_AS,90

Error:
Executing the query "exec PROC_FINAL"
failed with the following error: "Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The same store procedure successfully executes on a test server with the following collation property.

TEST
master,SQL_Latin1_General_CP1_CI_AS,90
tempdb,SQL_Latin1_General_CP1_CI_AS,90
model,SQL_Latin1_General_CP1_CI_AS,90
msdb,SQL_Latin1_General_CP1_CI_AS,90
TEST,SQL_Latin1_General_CP1_CI_AS,90

My initial thought was to re install SQL server with SQL_Latin1_General_CP1_CI_AS and dropped and re created the instance.
I could only find Latin1_General_CI_AS in selecting the collation during installing SQL server.

my questions are:

1. Is the collation the root cause for my problem?
2. Where do I select SQL_Latin1_General_CP1_CI_AS during a fresh install as I could not find the same in the drop down menu.
3. Is there a way of change the existing collation?

I apologize for the long description.
-Thanks all.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-17 : 23:29:36

in that particular which you are executing, at every mapping point (i mean at every declaration inside the function or procedure) place this statement
ex: declare @myvalue varchar(20) collate database_default

let me know wether it worked or not

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-18 : 00:25:08

Dear Samu,
answers for your questions
1)yes collation is the root cause no doubt
2)SQL_Latin1_General_CP1_CI_AS is the default collation for sql server.
3)you can change the collation with the command
ALTER DATABASE MyDatabase COLLATE required_collation

now work with that and let me know

Vinod
Even you learn 1%, Learn it with 100% confidence.

samu_78_nyc
Starting Member

11 Posts

Posted - 2008-01-18 : 10:17:41

Hi Vinod,
Thanks for your reply.
I cannot use alter database to change the master db. I did change the test db with the new collation and having the same issue again.
I have not declared the values inside the function as I have to migrate numerous procedures and would like to fix the problem at the database level instead of procedure level.
If you can provide me with the your email I could send you some more details.
Thanks again for the reply.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-20 : 20:49:43

You have to rebuild master db with new collation, books online has details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources