Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Collation problem! dont know what to do :( Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 9:43 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 379, Visits: 690
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
----------------------------------------
Post #854509
Posted Wednesday, January 27, 2010 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #854524
Posted Wednesday, January 27, 2010 10:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 379, Visits: 690
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
----------------------------------------
Post #854527
Posted Wednesday, January 27, 2010 10:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 6,842, Visits: 13,363
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
Post #854534
Posted Wednesday, January 27, 2010 10:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:41 PM
Points: 20,734, Visits: 32,510
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




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)
Post #854544
Posted Thursday, January 28, 2010 2:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 379, Visits: 690
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
----------------------------------------
Post #854995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse