Collation problem with UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Collation problem with UNION

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-08 : 09:33:10

I am creating a temp table:


	CREATE TABLE #Temp_OFFICE ( 
		[Code] nvarchar(50),
		[Key] nvarchar(5) NULL,
		[Name] nvarchar(50) NULL,
		[FranchiseName] nvarchar(50) NULL,
		[PhoneType] nvarchar(5) NULL,
		[Address] nvarchar(50) NULL,
		[City] nvarchar(50) NULL,
		[State] nvarchar(50) NULL,
		[PostalCode] int NULL,
		[LicenseState] nvarchar(50) NULL,
		[LicenseCountry] nvarchar(50) NULL,
		[Email] nvarchar(50) NULL,
	)

and then using UNION to merge it with another table.

I am getting this error:
Cannot resolve collation conflict for column 1 in SELECT statement.

I read in a search on this forum that you should use collate when creating a temp table, such as:


	CREATE TABLE #Temp_OFFICE ( 
		[Code] nvarchar(50),
		[Key] nvarchar(5) 
		[Name] nvarchar(50)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[FranchiseName] nvarchar(50) NULL,
		[PhoneType] nvarchar(5) NULL,
		[Address] nvarchar(50)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[City] nvarchar(50)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[State] nvarchar(50)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[PostalCode] int NULL,
		[LicenseState] nvarchar(50)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[LicenseCountry] nvarchar(50) NULL,
		[Email] nvarchar(50) NULL,
	)

Is this true? I am also very unsure as to exactly which columns to add the collate ( COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) to?

Please any help would be appreciated.

ty

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 09:37:33

Add it to any character columns, yiou should only need to do this if the collation for your database is different from the default collation..

The other way to get around this to add COLLATE SQL_Latin1_General_CP1_CI_AS to your joins in your SQL..

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-08 : 10:01:53

quote:
Originally posted by RickD

yiou should only need to do this if the collation for your database is different from the default collation..

I apologize, but I don't know how to check for this? How do you check to see a database's collation?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 10:07:34

Easiest way is the properties for the database in EM, it's under the general tab..

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-08 : 10:26:58

thank you...

Seems the collation was different than the default. I added the COLLATE statement to each char in the temp table, and I now get past the "Collation conflict" problem.

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 04:15:53

"Add it to any character columns, you should only need to do this if the collation for your database is different from the default collation.."

I would add "or if it might be different on any server you move this database/application to in the future!"

That's our rationale for always putting COLLATION statements on all CREATE TABLE (Permanent or temporary) statements, and not using tables created using:

SELECT *
INTO #MyTempTable
FROM MyOtherTable

style syntax.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources