SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Collation problem! dont know what to do :(


Collation problem! dont know what to do :(

Author
Message
BenWard
BenWard
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 827
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
----------------------------------------
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73941 Visits: 40974
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
BenWard
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 827
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
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24263 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97089 Visits: 38988
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



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
BenWard
BenWard
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 827
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
----------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search