March 17, 2008 at 3:14 am
Hi all,
I always use the default collation everywhere in the table creation.
Everything worked fine until they moved the database from an SQL server instance with SQL_Latin1_General_CP1_CI_AS default collation to another SQL server instance with Latin1_General_CI_AS ( don't know the difference between these two by the way).
The problem arises using union between a temporary table and a database table. I think SQL server don't use the default database collation but uses the default server collation for the temporary table column.
Does anybody have a solution to this problem ?
I also read that you cannot change the SQL server default collation with re-installing the server ! Is this correct ?
Thank you,
Patrick Duflot
March 17, 2008 at 6:06 am
There are three level of collation
Database level
table level
column level
I think that your problem is that the collation are define in the table level or column level are diference of the database level.
Below there is a link that explains the collation and will answer all your questions
http://www.databasejournal.com/features/mssql/article.php/3302341
Greetings,
Santiago Carela
Rep. Dom.
March 17, 2008 at 8:32 am
It looks like there is a fourth collation level : server level.
So, yes, I know the problem is that the collation differs between the temporary table in the database table... Tell me how to fix it then 😉
To sumarize :
On the first sql server instance :
- Server Collation = SQL_Latin1_General_CP1_CI_AS
- Database Collation = SQL_Latin1_General_CP1_CI_AS
- Table Collation = SQL_Latin1_General_CP1_CI_AS
- Column collation = SQL_Latin1_General_CP1_CI_AS
Here is a stored procedure which highlights the problem
CREATE PROCEDURE [dbo].[TestCollation]
AS
BEGIN
CREATE TABLE #TempCollation (
Field1 nvarchar(50) NOT NULL)
INSERT INTO #TempCollation
SELECT Field1 FROM Table1
SELECT Field1 FROM #TempCollation
UNION
SELECT Field1 FROM Table1
END
It works fine.
Now the project manager comes into the scene and moves the database on another SQL server instance where the server collation is Latin1_General_CI_AS. Here's what we got :
- Server Collation = Latin1_General_CI_AS
- Database Collation = SQL_Latin1_General_CP1_CI_AS
- Table Collation = SQL_Latin1_General_CP1_CI_AS
- Column collation = SQL_Latin1_General_CP1_CI_AS
The same stored procedure failed with
Msg 468, Level 16, State 9, Procedure TestCollation, Line 15
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the UNION operation.
How to fix it ? Is there a way to set the temporary table colltion to the database collation owning the SP ?
Thanks
Patrick Duflot
March 17, 2008 at 2:04 pm
If you just want to make the code work, then you can use the COLLATE function to get them in sync like so:
select * from TableA A
join TableB B on A.Fld1 = B.Fld1 collate Latin1_General_BIN
I have the bigger problem of wanting to change the server default. I believe I will have to re-install SQL, or rebuild Master. Not sure which is easier. Then my Master DB will be empty & I have to import everything back in ?
March 18, 2008 at 2:52 am
Patrick Duflot (3/17/2008)
CREATE PROCEDURE [dbo].[TestCollation]AS
BEGIN
CREATE TABLE #TempCollation (
Field1 nvarchar(50) NOT NULL)
INSERT INTO #TempCollation
SELECT Field1 FROM Table1
SELECT Field1 FROM #TempCollation
UNION
SELECT Field1 FROM Table1
END
Try to change your CREATE TABLE clause to...
CREATE TABLE #TempCollation (
Field1 nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL)
then your temp table will have the same collation as your database have...
March 18, 2008 at 3:05 am
Aaaaaah DATABASE_DEFAULT that's what I needed 🙂
Many thanks Jonnie !
Patrick Duflot
March 19, 2008 at 3:33 am
Be careful - changing the database settings will not change the collation of any data already in the database.
March 19, 2008 at 3:49 am
Ian is right.
You have to export data from columns defined as character fields, change column collation and then import those data.
Change of collation at column level takes place only for new inserts/updates.
Regards,
PSA
March 19, 2008 at 4:08 am
Ian and PSA
I think you are missing the point here...or am I 🙂
If your database and temDB collation differ you have to either rebuild the masterDB or create your temptables with the same collation as your database. Rebuild may not be an option because you may have other databases in the same server...
The easiest solution, in my opinion, is to create temptables with the DATABASE_DEFAULT collation "flag"...then your temptable wÃll have the same collation as your database
March 19, 2008 at 7:32 am
Jonnie Pettersson (3/19/2008)
Ian and PSAI think you are missing the point here...or am I 🙂
If your database and temDB collation differ you have to either rebuild the masterDB or create your temptables with the same collation as your database. Rebuild may not be an option because you may have other databases in the same server...
The easiest solution, in my opinion, is to create temptables with the DATABASE_DEFAULT collation "flag"...then your temptable wÃll have the same collation as your database
Why would other databases prevent you from changing the server's default collation ?? Those other databases will still keep whatever collation they already have. I'm in the same position and am about to re-install SQL (basically the same effect as rebuilding master). Then I won't have to jump through hoops with tempdb now or in the future. Otherwise, you'll always have to remember to code for collation.
March 19, 2008 at 7:42 am
Why would other databases prevent you from changing the server's default collation ?? Those other databases will still keep whatever collation they already have.
Yes! they will, but what if they use tempDB and don't use COLLATE DATABASE_DEFAULT...and I'm "sure" they won't...It's so easy to write "SELECT * INTO #TMP from tablename...
March 26, 2008 at 6:53 am
That's right, changing server's collation is not appropriate in this case.
COLLATE DATABASE_DEFAULT worked fine for me.
Another solution could be to use a table object instead of a temporary table.
It looks like their default collation is the database collation.
In my example :
DECLARE @TempCollation TABLE (Field1 nvarchar(50) NOT NULL)
Patrick Duflot
March 26, 2008 at 7:03 am
Another solution could be to use a table object instead of a temporary table.
It looks like their default collation is the database collation.
In my example :
DECLARE @TempCollation TABLE (Field1 nvarchar(50) NOT NULL)
Yes, but there are some performance issues with table objects...so I hesitate to use them unless I know for sure it's a small amount of rows.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply