Developer forum

Forum » Development » Collation error in DW7

Collation error in DW7

E-mail notifications

Reply
Posted on 26/11/2009 11:40:42
Hi Guys,

I have a custom module that selects data from 3 tables and inner joins them.
But after converting the solution til DW7 i get a collation error when i run my code.

This is a simplified version of the SQL that wont run in DW7:
SELECT     *
FROM         EcomProductsRelatedGroups INNER JOIN
                      EcomProductsRelated ON EcomProductsRelatedGroups.RelatedGroupID = EcomProductsRelated.ProductRelatedGroupID
INNER JOIN
                      EcomProducts ON EcomProductsRelated.ProductRelatedProductRelID = EcomProducts.ProductID

I took a look in the database and noticed that the collation for the ID fields in the 3 tables wheren't the same.

For some reason DW7 converted some of them to collation SQL_Latin1_General_CP1_CI_AS but not all, and that confuses me.

I'm my head it doesn't make much sense to not have all columns use the same collation so we as module developers can compare data as we like in the database.

Can someone tell me why this was changed in DW7?

Regards
 Martin


Replies

 
Posted on 27/11/2009 15:09:40
Reply
Hi Martin,

Which is the solution?


Regards
Morten Snedker
 
Posted on 30/11/2009 10:07:48
Reply
Hi Morten,

I sent you the adress by mail.

Regards
 Martin
 
Posted on 30/11/2009 10:59:02
Reply
The error you get is known error that occurs in regards of the SQL-Server setup. Since the error is accounted for in our API, it leaves two work-arounds:

1)
Use the API to get your collection of groups.


2)
Insert a COLLATE directly on your joins:

...INNER JOIN EcomProducts ON EcomProducts.ProductID COLLATE database_default = EcomProductsRelated.ProductRelatedProductID COLLATE database_default...



Regards
Morten Snedker


 
Posted on 30/11/2009 12:39:58
Reply
Okay, i'll try your workaround.

Do you know why the collations where changed?
Seems kinda odd to me.

//Martin

 

You must be logged in to post in the forum