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 |
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 2009-01-20 : 08:52:36
|
I have a script for MS Server 2005:(there aren't many records so I haven't bothered with fulltext search)use Debtselect * from dbo.Diary as dJOIN Debtor as dtON d.DebtorID = dt.DebtorIDJOIN Debt as dbON db.DebtID = dt.DebtIDJOIN Portfolio as pON p.Code = db.Codewhere (Description like '%refund%' or Description like '%overpayment%' or Description like '%over payment%') AND (db.Code = 'P0010' or db.Code = 'P0011') which find a collation error between the tables. I have tried tagging 'collate database_default' onto the script so:use Debtselect * from dbo.Diary as dJOIN Debtor as dtON d.DebtorID = dt.DebtorIDJOIN Debt as dbON db.DebtID = dt.DebtIDJOIN Portfolio as pON p.Code = db.Codewhere (Description like '%refund%' or Description like '%overpayment%' or Description like '%over payment%') collate database_defaultAND (db.Code = 'P0010' or db.Code = 'P0011') collate database_defaultwithout success.How can I resolve this please? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 08:57:34
|
quote: Originally posted by OldMySQLUser I have a script for MS Server 2005:(there aren't many records so I haven't bothered with fulltext search)use Debtselect * from dbo.Diary as dJOIN Debtor as dtON d.DebtorID = dt.DebtorIDJOIN Debt as dbON db.DebtID = dt.DebtIDJOIN Portfolio as pON p.Code = db.Codewhere (Description like '%refund%' or Description like '%overpayment%' or Description like '%over payment%') AND (db.Code = 'P0010' or db.Code = 'P0011') which find a collation error between the tables. I have tried tagging 'collate database_default' onto the script so:use Debtselect * from dbo.Diary as dJOIN Debtor as dtON d.DebtorID = dt.DebtorIDJOIN Debt as dbON db.DebtID = dt.DebtIDJOIN Portfolio as pON p.Code = db.Codewhere (Description like '%refund%' or Description like '%overpayment%' or Description collate database_default like '%over payment%') AND (db.Code collate database_default = 'P0010' or db.Code collate database_default = 'P0011') without success.How can I resolve this please?
try like above |
|
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 2009-01-20 : 09:05:13
|
Unfortunately that did not help, but I appreciated the suggestion. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 09:07:03
|
what was the initial error? |
|
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 2009-01-20 : 09:13:28
|
quote: Originally posted by visakh16 what was the initial error?
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 09:15:41
|
and what does column definition show in sp_help <table> |
|
|
|
|
|