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.
| Author |
Topic |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2009-11-18 : 10:57:59
|
| In a query, I'm trying to compare the values from two different server/databases, but I got an error:Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.But the Collation in Maintenance of the database properties are grey out and can not be updated. Is there any why to update this SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CI_AI in a SQL Server 2005?Thanks. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-18 : 11:35:03
|
| can specify collation in the query with COLLATE clause.post the actual query if that doesn't make sense |
 |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2009-11-18 : 12:38:05
|
| Thank you for response.The query is quite simple, just select records from one table where the key value is from other database:select * from Server1.ProdDB.dbo.client C where C.clname in (select FirmName from dbo.MarketRecods) I’m running it at Server2.MarketingDB where dbo.MarketRecods is located.But the setting were not same for the 2 servers/databases, and I don’t know how to update a property of the database or specify collation in the query. Please advise.Thanks. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-18 : 17:35:56
|
specify column names in querySELECT col1 COLLATE SQL_Latin1_General_CP1_CI_AS, col2 COLLATE SQL_Latin1_General_CP1_CI_AS, etc... |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2009-11-18 : 19:05:00
|
| Thank you russell, that works.Actually I used: select * from Server1.ProdDB.dbo.client C where C.clname in (select FirmName COLLATE DATABASE_DEFAULT from dbo.MarketRecods)We need to match one to another one or use the DATABASE_DEFAULT in the query. |
|
|
|
|
|
|
How to update database properties