Collation problem! dont know what to do :(

Question

Collation problem! dont know what to do :(

BenWard

SSCertifiable

Points: 5903
More actions
January 27, 2010 at 9:43 am

#138444

Dear Esteemed Colleagues. (apparently flattery gets people eveywhere?)
can anybody advis what I'm doing wrong please?
I have 2 databases, one I have just created myself called INOW6_REPORTS and one called INOW6 which is the backend to an EDM system called imagenow - I did not design or develop this database but rather it is provided by antoher company.
the table inow6.dbo.in_wf_item_arch contains several colums including queue_id and creation_time
the table inow6_reports.report_objects contains 3 columns, [object_id] (please dont shout at me for using reserved words!) report_id and [type]
Now the database INOW6_REPORTS was originally created as Latin1_General_CI_AS - dont know why but whenever I create a database on this server it seems to choose that...
when running a query that joins 2 tables from both databases:
declare @report_id int
select @report_id = 1
declare @actiondate smalldatetime
select @actiondate = '2008-01-18'
select * from inow6.dbo.in_wf_item_arch
where queue_id in
(SELECT [object_id]
from report_objects
where report_id = @report_id
and [type] = 'Queue')
and (convert(char(6),creation_time,12) = convert(char(6),@actiondate,12))
I get the follwing error:
Msg 468, Level 16, State 9, Line 9
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
i tried changing the database collation with:
ALTER DATABASE INOW6_REPORTS COLLATE SQL_Latin1_General_CP1_CI_AS
which did change the collation of the database in the database properties window but the query still has the same error.
I tried dropping and re-creating the tables to no avail. I even detatched and reatatched the database and still no joy.
I have no idea which column in which table is supposedly in Latin1_General_CI_AS nor how to change it to SQL_Latin1_General_CP1_CI_AS
Any suggestions?
Many Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

well first, changing the database collation does not change the collation of each individual varchar,char,nvarchar and nchar columns in the tables...so you'd still have the issues because those individual columns are still the collation they had before you started.

something i noticed was you were converting dates to varchars...i'd avoid that by doing this:

declare @report_id int

select @report_id = 1

declare @actiondate smalldatetime

select @actiondate = '2008-01-18'

select * from inow6.dbo.in_wf_item_arch

where queue_id in

(SELECT [object_id]

from report_objects

where report_id = @report_id

and [type] = 'Queue')

DATEADD(dd, DATEDIFF(dd,0,creation_time), 0) = DATEADD(dd, DATEDIFF(dd,0,@actiondate), 0)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BenWard SSCertifiable Points: 5903 More actions · Answer 2

Cheers buddy

I'm changing the date to chars to avoid the problem of 1/jan/2010 at 1pm being different from 1/jan/2010 at 3pm etc.

My brain is too muddled by now to analyse your suggested change - will it acheive the same result and what is the performance implication? better or worse?

Any suggestions on how to identify which column is currently in Latin1_General_CI... collation?

also what syntax would I need to use to change the collation at column level?

Cheers!

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

LutzM SSC Guru Points: 107049 More actions · Answer 3

If you compare two character fields and you run into the collation conflict you can change the collation inside the query:

SELECT [object_id]

FROM report_objects

WHERE report_id = @report_id

AND [type] = 'Queue' COLLATE Latin1_General_CI_AS

Regarding your current scenario I second Lowells suggestion to compare date type values instead of converting date values to character.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

As for the date comparision, I'd rewrite the query as follows to allow for the use of an index on creation_time if it exists:

select

*

from

inow6.dbo.in_wf_item_arch

where

queue_id in ( SELECT

[object_id]

from

report_objects

where

report_id = @report_id

and [type] = 'Queue')

--and DATEADD(dd, DATEDIFF(dd,0,creation_time), 0) = DATEADD(dd, DATEDIFF(dd,0,@actiondate), 0)

and creation_time >= DATEADD(dd, DATEDIFF(dd,0,@actiondate), 0) --creation_time equal or greater than midnight of @actiondate

and creation_time < DATEADD(dd, DATEDIFF(dd,0,@actiondate) + 1, 0) -- and less than midnight @actiondate + 1 day

BenWard SSCertifiable Points: 5903 More actions · Answer 5

Thanks all.

I'll give this a go, if I get any more problems I'll be sure to post again!

Thanks

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------