• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is my first time using this forum, so bare with me if I am not following the correct protocol. I have searched through the answers that were given for similar cases, but was not able to make sense of it. First of all I am not a DB I am a server admin, and everything else. So my knowlege of SQL is very limited.

We have a custom program used for the construction industry (starbldr) that connects to a SQL 2005 server. The problem is some of the reports crash with the following error noted below. I worked with the companies support to try to resolve it and we tried a variety of actions but none of them resolved the problem. So we now believe the problem may be with the structure of the database installation. When I look at the properties of the server it shows Latin1_General_CI_AS and is greyed out. The actual database that we connect to has a collation of SQL_Latin1_General_CP1_CI_AS which I confirmed with the company is correct. So my guess is the Database structure of the SQL server is conflicting with the database that we use for this program. I tried to alter the database properties of the server but it was greyed out.
So I guess my question is, how do I change the default properties of the server and is this safe?

Thanks again for your support.
Leroy
Application: GEACERR 02/03/2009 5:02:46 PM
Error: 
This application has encountered a problem:
 
Message: Error in clsCnn [-2147221503]
 
Procedure: clsQE.qeExecSP
 
Suggestion: If this problem persists, report the information items above to Technical Support.
 
Details: 
-2147221503: Method  of class clsCnn failed.
-2147221503:  sp_APOpenItemReport (1, 1, 14, '11', 'V', 0, 0, 0, 0, '29000', 'ZZZZZZ', '', '','', '','', '','', 'ZZ', '', 'ZZ', '', 'ZZ', '', 'ZZ', '', '', 1, 1901, 12, 9999, '', '','', '', '', '', '', '', '', '', '', 'N')
-2147217900: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
468: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Open in new window

0
wolfpack1825
Asked:
wolfpack1825
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Unfortunately you cant change the Collation level of a server.
If you want to change the collation of the server, either you have to use rebuildm utility or reinstall it.
And if you do those you wont be able to restore the current databases in it.

But you can change the Collation levels of all databases present in the server by using

alter database dbname collate collationname

If you want to do it, then use the Rebuidm utility and after that You need to use the ALTER TABLE command to change the collation of an existing column. You can't change the collation of an entire table with one command, but you can change the collation for all columns within the table.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
One more thought..
You can handle it in the procedure sp_APOpenItemReport.

whenever you are joining tables, use the below syntax:

select a.*, b.*
from a, b
where a.col1 collate database_default = b.col1 collate database_default
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now