T-SQL error: "Multiparty identifier XY cannot be linked"
I am running dynamic SQL which gives me an error about "multipart identifiers". Since I have columns with the same name in different tables, I prefix the column name with the table alias which seems to be the problem.
Reported bugs:
An identifier with multiple parts
e.Categorydescription
cannot be linked.
An identifier with multiple partsl.FullAddress
cannot be linked.
The column Categorydescription
exists in the Events
and tables Categories
, and the column FullAddress
exists in the Events
and tables Location
, so I use e.Categorydescription
and l.FullAddress
. I cannot give up on inner joins as I need other columns from tables Location
and Categories
.
I got it wrong. The actual error is below (below) in the code where I read from the ## Results global table:
SET @s_query = 'SELECT ' + @ColNames + ' FROM ##Results
WHERE ##RowNum BETWEEN('+CONVERT(varchar(20),@PageIndex)+'-1) * '+
CONVERT(varchar(20),@PageSize)+' + 1
AND((('+CONVERT(varchar(20),@PageIndex)+' -1) * '+
CONVERT(varchar(20),@PageSize)+' + 1) + '+CONVERT(varchar(20),@PageSize)+') - 1
ORDER BY ##RowNum';
EXEC (@s_query); -- the error is from here
--because #Results# has "Categorydescription" instead of "e.Categorydescription"
source to share
No comments yet, sorry.
@ColNames - what is it? e.categorynames or categorynames?
I'm going to assume you have e.categorynames and l.fulladdress here. SQL Server absolutely needs a multi-page select link because of the same column existing in two places, however the resulting column will be just a column. You can prove this by running only part of the query as a standard select
Select Balance_Date,
FullName,
e.Categorydescription,
l.FullAddress
From [Events] e
inner join Person c ON e.Pid = c.Pid
inner join Categories cat ON e.Cid = cat.Cid
inner join Location l ON e.Lid = l.Lid';
The column names Balance_Date, FullName, CategoryDescription, and FullAddress should be displayed here (i.e. no front panel multipart identifier).
Ditch the multi-page links and just target them as category names and fulladdress or even better rename them so you know 100% that the column names in the ## results will be.
SET @s_query = '
Select ROW_NUMBER() OVER (ORDER BY Balance_Date asc) AS ##RowNum,
Balance_Date AS Balance_Date,
FullName AS Fullname,
e.Categorydescription AS CategoryDescription,
l.FullAddress AS FullAddress
INTO ##Results
From [Events] e
inner join Person c ON e.Pid = c.Pid
inner join Categories cat ON e.Cid = cat.Cid
inner join Location l ON e.Lid = l.Lid';
then make sure @ColNames contains these aliases.
source to share