| Author |
Topic |
|
eptraja
Starting Member
3 Posts |
 Posted - 2005-11-22 : 05:06:37
|
| Hai all,We have a problem over here with our sql server database.We had a very long procedure which contains some tables and virtual tables. while running the procedure it blows error, bcoz the virtual table records are not get accessed. ie, table created on that moment and gets deleted.the error is server: Msg 446 , Level 16, State 9, Procedure Proc_ESI Quarterly line 374.Cannot resolve collation conflict for equal to operation.if you need the procedure i can forward over it.pls help us .regards,raja |
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-22 : 05:18:38
|
quote:
Originally posted by eptraja
if you need the procedure i can forward over it.
Paste your stored procedure and explain in brief about it.Surendra |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2005-11-22 : 05:51:22
|
| hmmm... looks like you have a statement in your procedure that is using the a comparison operator (=, >=, <= etc). but the 2 expression are of different collationexamplewhere col1 = col2the collation for col1 and col2 different.Try using cast to change the collation from one to another-----------------[KH] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-22 : 06:40:05
|
| Might be that you are using a Temporary table, and the collation in TEMPDB is different to your database.if so explicitly use CREATE TABLE for the #TempTable before you use it (rather than doing, for example, SELECT * INTO #TempTable FROM MyTable), and make sure that each char/varchar/text column definition has an explicit COLLATE statement on itKristen |
|
|
|
eptraja
Starting Member
3 Posts |
Posted - 2005-11-22 : 06:46:41
|
| Hai Khtan, Kristen,yes, its due to collation problem. How i could collate the database.pls give me the steps and commands to proceed.Thanx in advance,regards,raja |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-22 : 06:53:38
|
| "its due to collation problem"Well that doesn't narrow it down much!Are you accessing tables in two different databases?Do you have columns within a single database that use different collations?Are you creating #TempTables WITHOUT using a CREATE TABLE statement? (If so see my answer above)Are you creating non-temporary tables in other databases, which have different database collation to your current database, without using a CREATE TABLE statement, or without explicitly stating the COLLATION?Kristen |
|
|
|
eptraja
Starting Member
3 Posts |
Posted - 2005-11-22 : 07:12:38
|
| Hai Kristen,No, we have only one databases. We have a virtual table created by using create table .....the problem is when comparing two tables ie, where #emp.id=dep.id, here the error occurs...ie, cannot resolve collation.i hope i am clear..regards,raja |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2005-11-22 : 09:00:01
|
1. check the collation of the table dep column id sp_help dep 2. what is your server collation ? Is it same as dep.id ? select serverproperty('COLLATION')-----------------[KH] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-22 : 14:38:48
|
"We have a virtual table created by using create table"The you need to explicitly put a COLLATE statement on any column in the CREATE TABLE #emp that uses char/varchar/text. Use the same COLLATE as the database itself.I reckon if you do that you will be fine.Here's an example of one of my tables:CREATE TABLE #Temp( seq int IDENTITY(1,1) NOT NULL, pID varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, lot_ID int NULL, PRIMARY KEY ( pID )) Kristen |
|
|
|
|
Procedure !!!