June 23, 2008 at 4:24 am
When I try to run this code
DECLARE @login varchar(255)
SET @login = 'user'
CREATE TABLE #Hierarchy([login] nvarchar(255), parent nvarchar(255), Level int, HierarchyString VARCHAR(MAX))
INSERT INTO #Hierarchy([login], parent, Level, HierarchyString)
SELECT login,
parent,
0, --Top Level
CAST(' '+CAST(BuID AS CHAR(50))+' ' AS VARCHAR(8000))
FROM plwawmoqp01.MDS.dbo.MDS_Exch_Employees
WHERE login = @login
DECLARE @Level int
SET @Level = 0
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
INSERT INTO #Hierarchy([login], parent, Level, HierarchyString)
SELECT empl.login, empl.parent, @Level, h.HierarchyString + CAST(empl.BuID AS CHAR(50))+' '
FROM plwawmoqp01.MDS.MDS.dbo.MDS_Exch_Employees empl
INNER JOIN #Hierarchy h
ON empl.parent = h.login
AND h.Level = (@Level - 1)
END
SELECT * FROM #Hierarchy order by login
DROP TABLE #Hierarchy
server throws this error
Msg 468, Level 16, State 9, Line 21
Cannot resolve the collation conflict between "Polish_CI_AS" and "SQL_Polish_CP1250_CI_AS" in the equal to operation when I run this code
so I've changed line
ON empl.parent = h.login
to
ON empl.parent COLLATE Polish_CI_AS = h.login
because collation of plwawmoqp01.MDS server is SQL_Polish_CP1250_CI_AS
and collation of local db is Polish_CI_AS
but another erro occured
Msg 457, Level 16, State 1, Line 21
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
What should I change in this query?
June 23, 2008 at 6:28 am
I've resloved it.
Here is working code
DECLARE @login varchar(255)
SET @login = 'user'
CREATE TABLE #Hierarchy([login] nvarchar(255), przelozony nvarchar(255), Level int, HierarchyString VARCHAR(MAX))
INSERT INTO #Hierarchy([login], przelozony, Level, HierarchyString)
SELECT login,
przelozony,
0, --Top Level
CAST(' '+CAST(ID_dzialu AS CHAR(50))+' ' AS VARCHAR(8000))
FROM plwawmoqp01.MDS.dbo.MDS_Exch_Employees
WHERE login = @login
DECLARE @Level int
SET @Level = 0
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
INSERT INTO #Hierarchy([login], przelozony, Level, HierarchyString)
SELECT empl.login, empl.przelozony, @Level, h.HierarchyString + CAST(empl.ID_dzialu AS CHAR(50)) COLLATE SQL_Polish_CP1250_CI_AS +' '
FROM plwawmoqp01.MDS.dbo.MDS_Exch_Employees empl
INNER JOIN #Hierarchy h
ON empl.przelozony = h.login COLLATE SQL_Polish_CP1250_CI_AS
AND h.Level = (@Level - 1)
END
SELECT * FROM #Hierarchy order by login
DROP TABLE #Hierarchy
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply