Collared by Collation error 446
-
Hello there,
I have a collation conflict between a database table and a UDF table variable in the same database.
I have a production server running 2000 standard edition which has a server collation of SQL_Latin1_General_CP1_CI_AS.
I recently installed a training server, but the CD I had available only gave me the option of Latin1_General when I installed, giving a server collation of Latin1_General_CI_AS.
So I created a blank copy of my production database and set the collation to SQL_Latin1_General_CP1_CI_AS using ALTER DATABASE ...COLLATE ...
Then I copied the production database into the blank training database using DTS. Everything copied successfully except the UDF I have listed below, which failed. When I tried to apply it manually, I got 'Error 446: Cannot resolve collation conflict for equal to operation'.
The problem occurs in the WHERE ManagerID IN part of the INSERT. I am guessing that the conflict might arise because the collation of the table variable @Reports does not match the collation of the database table CurrentContract to which I am joining it. Do table variables therefore assume the collation setting of the server or of the tempdb database ?
I cannot change the collation setting for tempdb, because it is a system database.
So is there any way I can change the table variable collation, or can I force the installation to use a collation which is not listed in the install program drop-down?
Much obliged for any suggestions,
David
FYI: The function returns a list of employee IDs who report directly or indirectly to the employee ID passed as a parameter.
==============Function====================
CREATE FUNCTION ctfn_LineReports (@strUserID CHAR(8))
RETURNS @Reports TABLE (userid CHAR(8), orglevel INTEGER, managerid CHAR(8))
AS
BEGIN
DECLARE @intOrgLevel INTEGER
SET @intOrgLevel=0
INSERT INTO @Reports (userid, orglevel, managerid)
SELECT @strUserID, @intOrgLevel, managerid
FROM currentcontract WITH (NOLOCK)
WHERE userid=@strUserID
WHILE (SELECT COUNT(*) FROM @Reports WHERE 0">orglevel=@intOrgLevel)>0 AND @intOrgLevel<10
BEGIN
SET @intOrgLevel=@intOrgLevel+1
INSERT INTO @Reports (userid, orglevel, managerid)
SELECT userID, @intOrgLevel, managerid
FROM CurrentContract WITH (NOLOCK)
--Problem in this WHERE clause
WHERE ManagerID IN
(SELECT UserID FROM @Reports WHERE orglevel=@intOrgLevel-1)
AND UserID NOT IN
(SELECT UserID FROM @Reports)
END
RETURN
END
If it ain't broke, don't fix it...
-
You can use "collate" in the where clause to join fields with dis-similar collations.
How about:
INSERT INTO @Reports (userid, orglevel, managerid)
SELECT userID, @intOrgLevel, managerid
FROM CurrentContract WITH (NOLOCK)
--Problem in this WHERE clause
WHERE ManagerID COLLATE Latin1_General_CI_AS IN -- or vice versa
(SELECT UserID FROM @Reports WHERE orglevel=@intOrgLevel-1)
AND UserID NOT IN
(SELECT UserID FROM @Reports)
-
Hi, thanks for that. I didn't realise you could use COLLATE at the column level too.
Your suggestion worked, I also tried the following, which also worked and could be applied to my production database so that I can run the DTS export without errors.
RETURNS @Reports TABLE (userid CHAR(8) COLLATE SQL_Latin1_General_CP1_CI_AS, orglevel INTEGER, managerid CHAR(8) COLLATE SQL_Latin1_General_CP1_CI_AS)
Many thanks
David
If it ain't broke, don't fix it...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply