April 23, 2007 at 2:10 pm
Im working on populating a fact table and Im running into a problem trying to pull a dimension table primary key. The value Im using in the lookup is a char field with alphanumeric data in it and I think that is causing it to fail.
When I ran a general query joining on those fields I get a "Cannot resolve the collation conflict" error. I went into the DB properties for both databases and made set them to use the same collation "Latin1_General_CI_AI". That also didnt help so I added "COLLATE Latin1_General_CI_AI" into the general query and that worked.
Does anyone know if there is a limitation on the lookup control to only use integers or if there is something Im missing? I have tried to using cast to make sure both fields are of the same type and length but nothing seems to work.
If you dont know of anything specific then does any one know if I can force a lookup control to use a specific collation?
April 24, 2007 at 1:16 pm
Well I figured out how to correct the problem I was running into. I used the scripting wizard to generate the code to create the database objects /w having collate option set to true. Looking at the code for both database I noticed that they had different collation settings on the tables and views even though the database extended properties showd they matched.
I then used the Replace option on one of the databases to make the collation match the other. Then I also set the lookup controls "Cache Type" property to None, I found a blog entry in a web search that pointed out how this property can cange the lookup results.
The lookup controls are now pulling the right data.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply