Unable to resolve Collation conflict 2

[1DMF]

Hi,

I have the following SP (snippet)

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- Set default date
	Set @StartDate = coalesce(@StartDate, '2008-01-01') 
	Set @EndDate = coalesce(@EndDate, Convert(char(10), GetDate(), 121))
		
    -- create members table for joining
	CREATE TABLE #members  (   
		Adv_MemNo varchar(15) NOT NULL,
		Adviser varchar(101) NOT NULL,
		CompanyName varchar(100) NOT NULL,
    )

	-- populate members table
	INSERT INTO #members (Adv_MemNo,Adviser,CompanyName)   
	SELECT [Membership Number]  AS Adv_MemNo, FirstName  + ' ' + LastName  AS Adviser, CompanyName
	FROM  [MyServer].[MyDB].[dbo].[Contacts]
	WHERE ContactTypeID LIKE @Members + '%' AND CompanyName IS NOT NULL AND 
			[Membership Number] IS NOT NULL AND FirstName IS NOT NULL AND LastName IS NOT NULL
			AND [Membership Number] LIKE 'M00%'
	[b]COLLATE DATABASE_DEFAULT[/b];

I keep getting the following error

Msg 468, Level 16, State 9, Procedure sp_MySP, Line 42
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

You can see I have highlighted the fact I have included the local default collation on the data being selected and assume the temp table is being created using the default collation.

I am obviously missing something, but can't work it out.

Line 42 is the FROM clause

All help appreciated.

1DMF


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[bborissov]

What this query gove you as a result:

SELECT col.name, col.collation_name
FROM sys.columns col
WHERE object_id = OBJECT_ID('Contacts')

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

assume the temp table is being created using the default collation

Unfortunately, this is an incorrect assumption. When you create a temp table, it is created in the TempDB database and will use the default collation for TempDB.

You can force the temp table to get created with the default collation of the user database by doing this:

	CREATE TABLE #members  (   
		Adv_MemNo varchar(15) [!]COLLATE DATABASE_DEFAULT [/!]NOT NULL ,
		Adviser varchar(101) [!]COLLATE DATABASE_DEFAULT [/!]NOT NULL,
		CompanyName varchar(100) [!]COLLATE DATABASE_DEFAULT [/!]NOT NULL,
    )

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George, you are awesome as usual!

Bit of a pain having to apply a collation to every column in the TempDB table DDL, is this a fixed database with an unchangeable collation?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

I didn't notice that you are using a linked server and a variable in the same query. When you declare a variable it will take the collation of the database that you run the code in, so @member is using the collation of the user database. You are comparing this variable to a column in the linked server database which probably has a different collation. As such, I suggest you change the where clause to this:

WHERE ContactTypeID COLLATE DATABASE_DEFAULT LIKE @Members + '%' 
      AND CompanyName IS NOT NULL 
      AND [Membership Number] IS NOT NULL 
      AND FirstName IS NOT NULL 
      AND LastName IS NOT NULL
      AND [Membership Number] COLLATE DATABASE_DEFAULT LIKE 'M00%';

You don't need the collate clause for the NULL checks.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Changing the default collation for TempDB is painful. Very painful. In fact, most people advise that you uninstall/reinstall SQL with the correct collation.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

I didn't notice that you are using a linked server and a variable in the same query.

Yes that's right, though I had the collate on the end of the where clause which I understood to apply to the entire where clause, not just one column, is this incorrect?

It's running fine now I have collated the TempDB table #members,

I believe both SQL servers are the same collation both running 2008 R2.

If I remove the collation from the #members DDL, but apply it to the linked server where clause as you show, the collation error comes back, so it definitely seems to be the TempDB collation causing the problem , not the collation of the linked server columns.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

I had the collate on the end of the where clause which I understood to apply to the entire where clause, not just one column, is this incorrect?

This is incorrect. You need to apply it to every column that you are comparing string data. Collations only apply to strings because the collation defines how strings are sorted and compared. Case sensitive, case insensitive, accent sensitive, etc... Numbers and other data types don't have these problems.

Consider this... suppose you had a table of logins for your app. You store username and password. Now you want to join this table with another. You probably want to compare the user name is case insensitive but the password should be compared case sensitive. The only way to do this is to put the collation on each column.

Regarding my 2nd post... I had read Boris's post where he was asking about the collation of the linked server, noticed the line where you were getting the error and assumed that it was due to the comparison between the local variable and the string columns in the linked server database. My mistake for making an assumption. However, under different circumstances, I could have been right, right?




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Appreciate you clarifying this for me, never had a problem before, but that's because so far the temp intermediate table used for storing the linked server data (rather than trying to do a distributed transaction - so now use a 'pull' table, as per your guidance).

I could have been right, right?

I've never known you not to be

Boris -> sorry didn't mean to ignore you, the result for all columns returned in the query is 'Latin1_General_CI_AS' , if I run the same query on the other server where the SP resides for tables in the join, I get 'Latin1_General_CI_AS' , so as I thought, both user databases in both SQL servers are the same, it was the TempDB collation causing the problem.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music