[RESOLVED] SQL 2005 collation issue

Got a nasty error - never saw it before - running a very simple query.

This query first worked on a SQL 2005 dev server in my office - then worked on my laptop (sqlexpress) but failed on a new customers fresh install of sql 2005.

Code:

---

Update #Load Set C11=DD.Dept
        From #Load
        Left join Dept_t DD on DD.DeptDesc = C11

---

Got this error:

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Googled for that error - got very few links - Tibor talked about it in one of them - so I know it's real

At any rate changing the query to this

Code:

---

Update #Load Set C11=DD.Dept
        From #Load
        Left join Dept_t DD on DD.DeptDesc collate database_default = C11

---

makes it work.

Why is this happening - did the new installed server get a bad collation default?

I don't want to be three months into this customer going live to discover a simple server install issue.

Well buddy u r pro in SQL but still this is my view .....if have attached the DB copy from your machine to client sql server than just check it out if your client machine SQL Server's default collation is same as yours.....is there is possiblity that someone has set DD.DeptDesc collation different than that of default collation or collation of DB it self?

So that C11 that is from a TEMP TABLE - #LOAD1 - created locally on that machine - has a different collation then the DB that I backup/restored onto that server.

Makes sense.

So where and when do I best set the collation? On RESTORE? Of my DB? or do I set the collation of this new server to what I'm better expecting?

Is the CP1 difference due to MS SQL 2005 over MS SQL 2000???

Anyone know where the settings for this type of issue are?

I've found links on the web that talk about rebuilding the MASTER DB - I certainly don't want to do that!

once i got same sort of problem where my DB collation was Turkish and new SQL server was Latin1_General_CI_AI,Even after attaching my DB to new sql server DB's collation remains same :( so i have to manually change collation of few columns which i wanted to use Latin_General_CI_AI (to over come problem with turkish charc. is search)tough i saw many script to change entire db collation....

may be you can have a look into http://support.microsoft.com/kb/325335

Thanks for that link!

Does anyone know how the "set" the collation of the server itself? Was it selected poorly during INSTALL of MS SQL 2005? Did they change defaults for install from MS SQL 2000 to MS SQL 2005??

Yes it was probably overlooked when the database was created on the other server. Personally, I would avoid having to explicitly specify a collation for a column in my query because it's sort of introducing a concept into the query which the query really shouldn't know about. But that's just a personal preference, I didn't say it's what you ought to be doing.

The default in SQL 2005 is SQL_Latin1_General_CI_AS. Any collation type that starts with SQL_* is compatible with SQL 2000, so that helps when you've been migrating. You're probably getting your issue because the temporary table is defaulting to SQL 2005's non-compatible default which is Latin1_General_CI_AI. Because it's a migrated database, I'd think that everytime you create a temporary table, you need to explicitly define its collation to be SQL_*.

The option to set the collation should be available to you in the database properties box when you create the database or edit its properties.

I am going to mark this thread resolved.

Bottom line - inventory your sql boxes and push towards standard collations among all your installations.

This new install got a different collation then about a dozen servers I manage. Fresh install on a new server.

I am going to re-install and also look into the suggestion of using a standard command line install for MS SQL 2005 for new servers.

From John Bell, mvp over at the MS TechNet site for "SQL Server Server".

Quote:

---

Hi Steve

"Steve Z" wrote in message
> Ok - I was thinking it would be a re-install of sql on this new server.
>
> I manage a dozen customers all using our enterprise app on sql boxes. We
> regularly backup/restore to bring DB's back home for dev work - put on
> laptops and servers in the office. We are talking DB's with hundred+
> tables
> and thousands of columns - probably 1000 or so sprocs and udf's in each.
> Changing query collation syntax now would kill us.
>
> I've had customers move from MS SQL 2000 to 2005 in the past couple of
> years.
>
> I've never encountered a different collation - was that just dumb luck??
Probably. You should audit all your systems and find out what the
collations are for the instances.
Having them consistent between the instance and database is the important
things here to avoid re-writing your code!

>
> You said:
>
> "If you are not bothered about having an instance collation
> that is SQL_Latin1_General_CP1_CI_AS , then it may be easier to do a
> re-install rather than rebuilding which can be done from the command
> prompt"
>
> Is one collation preferred over the other. I'm about to suggest to this
> customer that we re-install that server (which will probably fall to me).
> We
> don't go live until July - so I can find the time. We have only setup a
> handful of windows group permissions - so the security on the new box will
> be
> easy to re-do.
>
The SQL collation can perform better in certain circumstances because the
character set is smaller.

But from a managment point of view you should standardise.

> I don't recall seeing a "collation" selection when installing - was it an
> option in one of the setup windows?

They can be dependent, but you will get differences if you update in-situ
rather than install brand newm which is what I guess happened here. This
would be complicated more if your SQL 2000 versions did not have the same
collation!

>
> Why do you suggest a command prompt install - is that easier? Or just
> better to save your install scripts?
>

I would have a standard installation either by unattended install or
parameterised scripts.

>
>
John

---

Another MVP at that site added:

Quote:

---

"Sylvain Lafontaine" wrote:

> Just a final word here: it's usually best to specify the "collate
> database_default" option when creating the temporary table instead of doing
> it in every join that follows. Of course, for this to be useful, you must
> not write a "use tempdb" statement before creating the temporary table; as
> we see sometimes.
>
> In my opinion, it's a better option doing this than trying to standardize
> all servers with the same collation because in the future, you might get a
> new client where this will be impossible to do (ie, to reinstall the
> sql-server in order to get a specific server collation).
>

---