Post reply

One for the books!?

  • October 26, 2005 at 9:08 am

    #95612

    Hey all

    I'm only putting this here cos it's one of those quite silly issues that i'd just never encountered before ... but is certainly one to take note of!?

    I've been doing some optimisations on some sprocs, during which i started using #temp tables (rather than table variables - Search for my other posts for the reasons!) and have been merrily developing and testing on a representative box with representative data! Being supremely happy that it all works fine (nay, PERFECTLY! ), the day comes to copy the sprocs to the live server!

    As you've guessed, two of them immediately start failing ... with a very suprising "Collation Conflict when doing update" error (summat like that!). As such, i did a trawl comparing the tables involved only to find that none of them had any special collation set on any of the columns and were all "database default"!?

    Still confused, i did my usual technique of "the long, hard stare" ... and it was then that i noticed the similarity between the sprocs, in that they were both using the same technique involving #temp tables to get around the perfromance issues i was solving! It was then that i "twigged" and went to the properties of both "TempDB" and "MyDatabase" to find what i suspected ... the Default Collations of these two DB's were different ... although annoying comparable (SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS).

    The annoying thing now is that i've had to hardcode the collation of the fields in the #temp table to be the same as the default collation for "MyDatabase", which obviously solves the problem, but is certainly uncomfortable to me as a developer (ie. if i have to copy the sproc to another DB with yet another default collation!)!

    So there you go ... a bit of a cautionary tale for you ... BEWARE CONFLICTING COLLATION SETS WHEN USING #TEMP TABLES!?

     

    ...and another thing ... is this guy trying to fart or summat!?

  • October 26, 2005 at 9:16 am

    #600239

    That's certainly one for the gotcha book, thanx for the info .

  • October 27, 2005 at 5:49 am

    #600530

    It happens for table variables as well.

    Both temp tables and table variables are created in tempdb.

    It only happens if you join or compare on text data.

    *edit*

    If you're being fancy, using dynamic SQL etc, then the db context can change and affect things.

    If you had a proper table in your db that you persisted and simply issued TRUNCATE instead of using #temp table this also may not have happened.

    I've had this problem before and depending on how it's coded, the collation error can occur and run time not CREATE time - a table var may give the error at create, but #temptable

    Top tip: don't declare the temp table/table var with a specifc collation: just use "COLLATE Database_Default" instead to coerce the collation without hardcoding a specific collation. This works for table vars, not sure about #temp table as I don't tend to use them because of the overhead.

    *end edit*

  • October 27, 2005 at 10:02 am

    #600638

    It's not really one for the books. It's just a case of something 'different' from one server to the next. Now whether this was by design or not is another issue. In any event, it is a strong argument for 'standards' when building and maintaining servers.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 27, 2005 at 10:13 am

    #600645

    MY ... that IS a high horse!?

  • October 27, 2005 at 11:29 am

    #600692

    It's not the horse vinny, it was the first 100 or so servers that I built that taught me the virtues of standards and defined processes ... as a matter of fact of my present 25 servers, 1 on them has 'binary' collation, not by choice, but by vendor requirement. So the issue is not new, it's just 'something different'.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 28, 2005 at 2:50 am

    #600831

    i think you've completely missed the spirit of the posting! the fact that it is "something different", and the fact that after 7 years of dealing with SQL Server this was the first time i ever encountered it DOES mean it's one for the books and probably isn't encountered often!

    So after 10000 database development projects, 600 SQL Server builds and with the 26 servers i currently maintain ... i think i'm allowed to point out these little ideosyncracies!

    Anyway ... am off to bed ... ur boring me!?

  • October 28, 2005 at 4:21 am

    #600842

    Thanks for the posting. As a contractor, I run into a lot of strange configuration but this one is certainly "one for the books".

    Since tempdb is recreated from the model db on instance start-up, is the collation of the model db also unexpected ? If so, then creation of new user databases could also have some unexpected side effect.

    What happens if one changes the collation of tempdb?

    ALTER DATABASE tempdb COLLATE SQL_Latin1_General_CP1_CI_AS

    SQL = Scarcely Qualifies as a Language

  • October 28, 2005 at 4:40 am

    #600846

    I think what's happened with the "MyDatabase" is that it was created on a different SQL Server with the slighlty different default collation, and has then been detached and re-attached on the production server! I didn't fancy changing the collation on tempdb because it was using the default collation for that server ... i did try to change the collation of "MyDatabase" however, but that failed as well (had issues with some UDF's as i recall!) ... so just bit the bullet and altered my sprocs as per my original post! By this time, i really couldn't be bothered chasing around anymore on servers and DB's i didn't configure or create!?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply