Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems with Collation on nested Temp Table Statement

Status
Not open for further replies.
dnfrantum

dnfrantum

Programmer
Oct 23, 2001
175
0
0
US
Within a particular set of results from a query, I want to number the lines, e.g. Order 1 has 5 lines, I want to process the results to read
Order 1,0
Order 1,1
Order 1,2
Order 1,3
Order 1,4

I am using the following code to achieve this, but I can't seem to get it to work. I am getting the 2 errors:
Code: 
 Cannot resolve the collation conflict between
Code: 
Ambiguous Column 'RecordKey'

Here is my code snippet:
Code: 
DECLARE @RecordKey varchar(100)
DECLARE @NumAtCard AS NVARCHAR(20)
SET @NumAtCard = (Select max(T0.NumAtCard) FROM OINV T0 WHERE T0.Series = '37' and T0.NumAtCard not like '500%')
SELECT T0.RecordKey,T0.LineNum,T0.ItemCode,T0.LineTotal,T0.Price,T0.Quantity,T0.TaxCode,T0.TaxLiable,T0.WarehouseCode,
'3' as SalesPersonCode,0 as processed
INTO ##orderlines
FROM dbo.[@GoorinWEBOrderLines] T0  
WHERE T0.RecordKey > @NumAtCard
ORDER BY RecordKey ASC 


CREATE TABLE ##orderlines_2(LineNum Int Identity(0,1), RecordKey varchar(100))
WHILE (SELECT count(*) FROM ##orderlines WHERE processed = 0) > 0

BEGIN
CREATE TABLE ##orderlines_1 (RecordKey varchar(100))
INSERT INTO ##orderlines_1(RecordKey)
SELECT TOP 1 isnull(RecordKey,'')
FROM ##orderlines
WHERE processed = 0

INSERT INTO ##orderlines_2(RecordKey)
SELECT RecordKey
FROM ##orderlines T0 INNER JOIN ##orderlines_1 T1 ON T0.RecordKey = T1.RecordKey

SELECT TOP 1 @RecordKey = isnull(RecordKey,'')
FROM ##orderlines_1
 


UPDATE ##orderlines set processed = 1 where RecordKey = @RecordKey
DROP TABLE ##orderlines_1
END

Thanks in advance,
Donald
 
Sort by date Sort by votes
Cannot resolve the collation conflict between
Click to expand...

This is a major pain. This means that the collation for the column in your user database does not match the default collation for your tempDB. I wrote a free too named SQLCop that can thoroughly check your database for collation problems. I encourage you to download this tool and run it against your database. You are likely to find other issues too.

But... to explain...

First, you should understand that collations are used to control how things are sorted, and how they are compared. For example, should BLAH = blah ? Collations are pretty complicated. Mostly they control case sensitivity and accent sensitivity stuff.

When you create a temp table or table variable, it is created in the TempDB. If you do not specify the collation, it will default to the default collation for the database (in this case TempDB). However, if you specify the collation, and you make them match, you will be able to avoid this problem.

Let's take a closer look at this part.

Code: 
SELECT T0.RecordKey,
       T0.LineNum,
       T0.ItemCode,
       T0.LineTotal,
       T0.Price,
       T0.Quantity,
       T0.TaxCode,
       T0.TaxLiable,
       T0.WarehouseCode,
       '3' as SalesPersonCode,
       0 as processed
INTO   ##orderlines
FROM   dbo.[@GoorinWEBOrderLines] T0  
WHERE  T0.RecordKey > @NumAtCard
ORDER BY RecordKey ASC

The easiest, and most reliable way to fix this problem is to specify the collation, like this:


Code: 
SELECT T0.RecordKey [!]Collate Database_Default[/!],
       T0.LineNum,
       T0.ItemCode,
       T0.LineTotal,
       T0.Price,
       T0.Quantity,
       T0.TaxCode,
       T0.TaxLiable,
       T0.WarehouseCode,
       '3' as SalesPersonCode,
       0 as processed
INTO   ##orderlines
FROM   dbo.[@GoorinWEBOrderLines] T0  
WHERE  T0.RecordKey > @NumAtCard
ORDER BY RecordKey ASC

This will still cause a temp table to be created in TempDB, but since you specify the collation for the column, it will use that instead of TempDB's default. Since you are running this code in the user database, Database_Default will be the default collation for the user database.

You should specify the collation in your other temp tables too, especially for the RecordKey column because it appears as though that is the only column you are comparing things too.

Let's take a look at this part (near the end)
Code: 
UPDATE ##orderlines set processed = 1 where RecordKey = @RecordKey

@RecordKey is a variable in the user database. Since you didn't specify the collation when you declared the variable, it will have the user databases default collation. The problem is, you are comparing it to a column in temp table which has the collation of tempdb.

Make sense?

Ambiguous Column 'RecordKey'
Click to expand...

This error message usually occurs when you have a query that uses multiple tables and you don't specify which table's column to use. Looking at your code, the problem is probably here:

Code: 
INSERT INTO ##orderlines_2([!]RecordKey[/!])
SELECT RecordKey
FROM ##orderlines T0 INNER JOIN ##orderlines_1 T1 ON T0.RecordKey = T1.RecordKey
both temp tables have a RecordKey column. I know this because I see that you are joining on that column. So... which RecordKey column are you referring to in the insert (highlighted) part? If you specify the table to use, this error will go away, like this:


Code: 
INSERT INTO ##orderlines_2([!]T0.[/!]RecordKey)
SELECT RecordKey
FROM ##orderlines T0 INNER JOIN ##orderlines_1 T1 ON T0.RecordKey = T1.RecordKey

Now SQL Server knows to use the RecordKey column of the T0 table. There is no longer any ambiguity.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Here is my revised code:
Code: 
DECLARE @RecordKey varchar(100)
DECLARE @NumAtCard AS NVARCHAR(20)
SET @NumAtCard = (Select max(T0.NumAtCard) FROM OINV T0 WHERE T0.Series = '37' and T0.NumAtCard not like '500%')
[COLOR=red][b]SELECT T0.RecordKey COLLATE Database_Default as RecordKey[/b][/color],T0.LineNum,T0.ItemCode,T0.LineTotal,T0.Price,T0.Quantity,T0.TaxCode,T0.TaxLiable,T0.WarehouseCode,
'3' as SalesPersonCode,0 as processed
INTO ##orderlines
FROM dbo.[@GoorinWEBOrderLines] T0  
WHERE T0.RecordKey > @NumAtCard
ORDER BY RecordKey ASC 


CREATE TABLE ##orderlines_2(LineNum Int Identity(0,1), RecordKey varchar(100)) 
WHILE (SELECT count(*) FROM ##orderlines WHERE processed = 0) > 0

BEGIN
CREATE TABLE ##orderlines_1 (RecordKey varchar(100))
INSERT INTO ##orderlines_1(RecordKey)
SELECT TOP 1 isnull(RecordKey,'')
FROM ##orderlines
WHERE processed = 0

INSERT INTO ##orderlines_2(RecordKey)
[COLOR=red][b]SELECT RecordKey COLLATE Database_Default as RecordKey[/b][/color]
FROM ##orderlines T0 INNER JOIN ##orderlines_1 T1 ON T0.RecordKey = T1.RecordKey

SELECT TOP 1 @RecordKey = isnull(RecordKey,'')
FROM ##orderlines_1
 


UPDATE ##orderlines set processed = 1 where RecordKey = @RecordKey
DROP TABLE ##orderlines_1
END

Here is the error returned:

Code: 
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation.
Msg 209, Level 16, State 1, Line 23
Ambiguous column name 'RecordKey'

Thanks in advance,
Donald
 
Upvote 0 Downvote
Please ignore the ambiguous error, I have solved that one.

Thanks in advance,
Donald
 
Upvote 0 Downvote
I see 2 more obvious ones:

Code: 
DECLARE @RecordKey varchar(100)
DECLARE @NumAtCard AS NVARCHAR(20)
SET @NumAtCard = (Select max(T0.NumAtCard) FROM OINV T0 WHERE T0.Series = '37' and T0.NumAtCard not like '500%')
SELECT T0.RecordKey COLLATE Database_Default as RecordKey,T0.LineNum,T0.ItemCode,T0.LineTotal,T0.Price,T0.Quantity,T0.TaxCode,T0.TaxLiable,T0.WarehouseCode,
'3' as SalesPersonCode,0 as processed
INTO ##orderlines
FROM dbo.[@GoorinWEBOrderLines] T0  
WHERE T0.RecordKey > @NumAtCard
ORDER BY RecordKey ASC 


CREATE TABLE ##orderlines_2(LineNum Int Identity(0,1), RecordKey varchar(100) [!]COLLATE Database_Default[/!]) 
WHILE (SELECT count(*) FROM ##orderlines WHERE processed = 0) > 0

BEGIN
CREATE TABLE ##orderlines_1 (RecordKey varchar(100) [!]COLLATE Database_Default[/!])
INSERT INTO ##orderlines_1(RecordKey)
SELECT TOP 1 isnull(RecordKey,'')
FROM ##orderlines
WHERE processed = 0

INSERT INTO ##orderlines_2(T0.RecordKey)
SELECT RecordKey COLLATE Database_Default as RecordKey
FROM ##orderlines T0 INNER JOIN ##orderlines_1 T1 ON T0.RecordKey = T1.RecordKey

SELECT TOP 1 @RecordKey = isnull(RecordKey,'')
FROM ##orderlines_1
 


UPDATE ##orderlines set processed = 1 where RecordKey = @RecordKey
DROP TABLE ##orderlines_1
END

It's possible that I may have missed other ones, too. In this case, I can't really test the code before posting it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
190
mikrom
mikrom
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
105
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
169
BobSuruncle
BobSuruncle
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
265
Chris Miller
Chris Miller
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
116
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top