SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Table collation error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 01/20/2009 :  08:52:36  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/20/2009 :  08:57:34  Show Profile  Reply with Quote
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?


try like above
Go to Top of Page

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 01/20/2009 :  09:05:13  Show Profile  Reply with Quote
Unfortunately that did not help, but I appreciated the suggestion.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/20/2009 :  09:07:03  Show Profile  Reply with Quote
what was the initial error?
Go to Top of Page

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 01/20/2009 :  09:13:28  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/20/2009 :  09:15:41  Show Profile  Reply with Quote
and what does column definition show in sp_help <table>
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000