Collation conflict 2

[Glasgow]

I am using a SQL 2005 server whose collation is set to SQL_Latin1_General_CP1_CI_AS. I am manipulating a database on that server whose collation is set to
Latin1_General_CI_AS and, as far as I can see, all relevant fields within the tables on that database also have Latin1_General_CI_AS collation. When I create a new table within the database, the fields are also set as Latin1_General_CI_AS.

However, I am importing data from a MySQL database on a remote linked server using this SQL:

SELECT * INTO TmpWebMbr FROM OPENQUERY (WebRemote, 'SELECT * FROM Mbr')

and all the fields within the newly created table appear to have been assigned SQL_Latin1_General_CP1_CI_AS collation which then gives me problems when I start comparing fields.

Is there a way I can force the SELECT INTO to generate fields with Latin1_General_CI_AS collation? I suspect if I did an explicit CREATE TABLE first that may resolve my problem but are there any other options?

 

[djj55]

When comparing you can use

where table1.value1 = table2.value2 collation database_default -- which side of = depends on collation

however there will be no indexing.

As you indicated creating the table first is one solution.



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Glasgow]

Thanks - I didn't know about the database_default option. Otherwise I've been doing much the same thing but explicitly referencing Latin1_General_CI_AS collation. It's just a bit of a bind doing it all over the place and it does make the code look a bit cluttered.

I think I may go for CREATE TABLE. Shame I can't some how apply a COLLATE on the SELECT INTO.

 

[gmmastros]

I'm not 100% sure, but I suspect that if you listed the columns you were selecting from and specified the collation, then you wouldn't need to create the table first. Ex:

SELECT * INTO TmpWebMbr FROM OPENQUERY (WebRemote, 'SELECT Col1 Collate Latin1_General_CI_AS, Col2 Collate Latin1_General_CI_AS, Col3 Collate Latin1_General_CI_AS FROM Mbr')

That seems like a lot of work though. Probably better just to create the table first.

as far as I can see, all relevant fields within the tables on that database also have Latin1_General_CI_AS collation.

There's a couple of ways to know for sure. I would suggest that you download and install SQLCop[/cop]. You can use this free tool to see if you have collation problems. On the left, expand SQLCop->Column->Collation Mismatch and SQLCop -> Configuration -> Database Collation

Of course, the other way to know is to query system tables and/or views.

-[url=http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=10]George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Thanks George - I see where you're coming from. Not sure if this would work when the remote DB is MySQL (which I don't know as well as MS SQL) as it is in this case. I assume the Collation Names would at least be different. I'll probably stick with one of the other options.

I'll take note of the tool you suggested, thanks. I'd just been opening the tables in SSMS object browser and looking at a few fields at random to establish what I assume is a pattern throughout the database.

 

[gmmastros]

Glasgow,

I do not recommend this tool lightly. I wrote it, with the help of some friends like Denis Gobo and Ted Krueger. This tool has approximately 50 checks that run against your database. The check I was recommending will compare the collation of all string columns (char, nchar, varchar, nvarchar, text, and ntext) to see if the collation for the column matches the default collation of your database. There are valid reasons why you might want a different collation, but the tool makes it easy to find the mismatches.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

OK you convinced me! I downloaded it and I've found about six months work to do .

The only collation conflicts are with the tables affected by the logic under discussion in this thread. But the other problems - well deep down I knew a lot of them were there, the tool just re-awoke my conscience. Oh for the luxury of more time.

Wish I'd noticed the 'Expand all' tickbox at the beginning.

Very impressive. Thanks again.