Collate question

[WyldGynjer]

I have set up a database (DB1) with the collation name of: Latin1_General_BIN

Then I set up a second database (DB2) with the collation name of:
SQL_Latin1_General_CP1_Cl_AS

When I try to SELECT whatever from DB1 where whatever is IN (SElect Whatever from DB2) I get the following error:
Server: Msg 446, Level 16, State 9, Line 1

The reason for the error - I believe is because of my different collation names....

so...

My question is - If I do an ALTER DATABASE DB2 COLLATE Latin1_General_BIN ---> what does this actually do? Will it solve my problem? I've not set any columns' collation different than the default....

my goal is to just get my select statement to work w/o the errors....I can't easily recreate the database - I can - but I have alot of data in there and I don't want anything to get screwed up....

Any thoughts?

 

[JayKusch]

I beleive once the collation is set during an install of DB create, it is permanent. I beleive you will need to recreate the DB w/ the correct collation.

Thanks

J. Kusch

 

[Luvsql]

You can add in a collate syntax into your select statement without having to alter the database. Put the collate on the field that is in both databases ie:

COLLATE SQL_Latin1_General_CP1_CI_AS

 

[mrdenny]

I'll have to agree with Jay and LuvSQL. To do this without recreating your database will require you to use the COLLATE command on all your queries. The only way to change the collation type on a database is to recreate the database, and then reload your data.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[WyldGynjer]

Well, I went ahead and did the Alter Database statement - it changed everything except the CHAR fields, so then I went and changed each and every one of those.

I didn't lose any data, so I hope that I didn't mess anything up.

I had to change a few of my queries in my programs though - but the good thing is - I don't have many - I'm still in the beginning stages of it all

Thanks for your input!