Table collation error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Table collation error

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 Debt
select * from dbo.Diary as d

JOIN Debtor as dt
ON d.DebtorID = dt.DebtorID

JOIN Debt as db
ON db.DebtID = dt.DebtID

JOIN Portfolio as p
ON p.Code = db.Code

where
(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 Debt
select * from dbo.Diary as d

JOIN Debtor as dt
ON d.DebtorID = dt.DebtorID

JOIN Debt as db
ON db.DebtID = dt.DebtID

JOIN Portfolio as p
ON p.Code = db.Code

where
(Description like '%refund%'
or Description like '%overpayment%'
or Description like '%over payment%') collate database_default

AND (db.Code = 'P0010' or db.Code = 'P0011') collate database_default

without 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 Debt
select * from dbo.Diary as d

JOIN Debtor as dt
ON d.DebtorID = dt.DebtorID

JOIN Debt as db
ON db.DebtID = dt.DebtID

JOIN Portfolio as p
ON p.Code = db.Code

where
(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 Debt
select * from dbo.Diary as d

JOIN Debtor as dt
ON d.DebtorID = dt.DebtorID

JOIN Debt as db
ON db.DebtID = dt.DebtID

JOIN Portfolio as p
ON p.Code = db.Code

where
(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?