The column prefix '%s' does not match with a table name or alias name used in the query
i'm trying to run a query against a remote 2000 server; but the query that the local server is generating is incorrect, and causes the remote server to return the error:
The column prefix 'Tbl1002' does not match with a table name or alias name used in the query.
When you trace the remote server, you can see that the sp_cursorprepexec batch is, in fact, invalid SQL; it has a reference to a dervied table Tbl1002 that does no exist.
The query i am running on my local server is:
SELECT
P.Code, P.Name AS PositionName, P.CompCommitteeMember,
( SELECT COUNT(*)
FROM Employees E
WHERE E.PositionID = P.PositionID
) AS EmployeeCount
FROM Positions P
WHERE P.PositionID = '{D1B0912D-B1A5-11D4-BBDD-0004ACC5B8A7}'
Where Employees and Positions are views that are simply selects from the linked server. In order to eliminate that confusion, we will eliminate the views - and use four part naming directly:
SELECT
P.Code, P.Name AS PositionName, P.CompCommitteeMember,
( SELECT COUNT(*)
FROM WCLHR.CasinoHR.dbo.Employees E
WHERE E.PositionID = P.PositionID
) AS EmployeeCount
FROM WCLHR.CasinoHR.dbo.Positions P
WHERE P.PositionID = '{D1B0912D-B1A5-11D4-BBDD-0004ACC5B8A7}'
And the query still fails with:
The column prefix 'Tbl1002' does not match with a table name or alias name used in the query.
In order to eliminate any confusion around the guid in the WHERE clause, we'll eliminate the WHERE clause:
SELECT
P.Code, P.Name AS PositionName, P.CompCommitteeMember,
( SELECT COUNT(*)
FROM WCLHR.CasinoHR.dbo.Employees E
WHERE E.PositionID = P.PositionID
) AS EmployeeCount
FROM WCLHR.CasinoHR.dbo.Positions P
And it still fails with:
The column prefix 'Tbl1002' does not match with a table name or alias name used in the query.
In order to eliminte any confusion around the use of * in the COUNT, we'll eliminate it, and instead only count a constant:
SELECT
P.Code, P.Name AS PositionName, P.CompCommitteeMember,
( SELECT COUNT(1)
FROM WCLHR.CasinoHR.dbo.Employees E
WHERE E.PositionID = P.PositionID
) AS EmployeeCount
FROM WCLHR.CasinoHR.dbo.Positions P
And it still fails with:
The column prefix 'Tbl1002' does not match with a table name or alias name used in the query.
Further down we'll even eliminate the linked servers, and run the query locally on the 2000 machine.
What if you run it on the remote server itself?
If i run this query against the remote server itself:
SELECT
P.Code, P.Name AS PositionName, P.CompCommitteeMember,
( SELECT COUNT(*)
FROM Employees E
WHERE E.PositionID = P.PositionID
) AS EmployeeCount
FROM Positions P
It works fine.
What is the generated query, how do you know it is bad?
Using Profiler, we can see the query coming in to the remote server. It's a huge horrendous mess, but it's definitely invalid. It tries to reference a derived table that isn't in scope. The whole batch will be familiar to anyone who's done work with remote servers in SQL Server:
declare @P1 int
set @P1=NULL
declare @P2 int
set @P2=NULL
declare @P3 int
set @P3=557064
declare @P4 int
set @P4=98305
declare @P5 int
set @P5=0
exec sp_cursorprepexec @P1 output, @P2 output, NULL, N'SELECT "Tbl1002"."PositionID", .....
select @P1, @P2, @P3, @P4, @P5
The real issue is the SQL statement that the server has been asked by another SQL Server to prepare. Trimmed down, it says:
SELECT
"Tbl1002"."PositionID" "Col1010", ...
( SELECT "Expr1007"
FROM (
SELECT "Expr1006","Expr1006" "Expr1007"
FROM (
SELECT COUNT(*) "Expr1006"
FROM (
SELECT
"Tbl1005"."EmployeeID" "Col1043", ...
FROM "CasinoHR"."dbo"."Employees" "Tbl1005"
WHERE "Tbl1005"."PositionID"="Tbl1002"."PositionID"
) Qry1103
) Qry1104
) "Subquery_Source_Tbl"
) "Expr1008"
FROM "CasinoHR"."dbo"."Positions" "Tbl1002"
WHERE "Tbl1002"."PositionID"={guid'D1B0912D-B1A5-11D4-BBDD-0004ACC5B8A7'}'
It's a messy read, but you can see the problem, it's referencing Tbl1002 inside some nested derived tables:
WHERE "Tbl1005"."PositionID"="Tbl1002"."PositionID"
But only declaring it outside; at the end:
FROM "CasinoHR"."dbo"."Positions" "Tbl1002"
What versions of SQL Server are we talking about here?
The "remote" server that we are trying to query ("wclhr") is SQL Server 2000 with SP4:
Microsoft SQL Server 2000 - 8.00.2066 (Intel X86) May 11 2012 18:41:14
When issuing the query, we've tried from SQL Server 2005, and SQL Server 2008 R2. It used to work when both servers were SQL Server 2000.
Starting with SQL Server 2005, and continuing to 2008 R2, it is generating invalid SQL!
Other things we've tried
Surprising, a horrible hack is to run:
SELECT TOP 99.999999 PERCENT
P.Code, P.Name AS PositionName, P.CompCommitteeMember,
( SELECT COUNT(1)
FROM WCLHR.CasinoHR.dbo.Employees E
WHERE E.PositionID = P.PositionID
) AS EmployeeCount
FROM WCLHR.CasinoHR.dbo.Positions P
That stops the local SQL Server 2008 R2 from generating invalid sql for the 2000 machine.
The local servers are not 64-bit, but we upgraded the catalogs on SQL Server 2000 anyway. It didn't fix it.
Isn't your original query just as wrong?
@Damien the Unbeliever doesn't believe that the scoping can be the problem. Rest assured, it is. My original query runs correctly against SQL Sever 2000:
SELECT
P.Code, P.Name AS PositionName, P.CompCommitteeMember,
( SELECT COUNT(*)
FROM Employees E
WHERE E.PositionID = P.PositionID
) AS EmployeeCount
FROM Positions P
WHERE P.PositionID = '{D1B0912D-B1A5-11D4-BBDD-0004ACC5B8A7}'
Unfortunately, the SQL Server 2005/2008/2008R2 optimizer transforms that query into an equivalent query - but unfortunately one that SQL Server 2000 is unable to execute:
SELECT
Tbl1002.PositionID,
Tbl1002.Name AS PositionName,
Tbl1002.CompCommitteeMember,
( SELECT RecordCount
FROM (
SELECT COUNT(*) AS RecordCount
FROM (
SELECT
Employees.EmployeeID
FROM Employees
WHERE Employees.PositionID=Tbl1002.PositionID
) Qry1103
) Qry1104
) AS EmployeeCount
FROM Positions Tbl1002
WHERE Tbl1002.PositionID= 'D1B0912D-B1A5-11D4-BBDD-0004ACC5B8A7'
Which, on SQL Server 2000, gives:
Msg 107, Level 16, State 2, Line 12
The column prefix 'Tbl1002' does not match with a table name or alias name used in the query.
SQL Server 2000 seems to have scoping issues with correlated sub-queries; that were "improved" in SQL Server 2005.
Bonus Reading
Answer
Based on the reading you attached it looks like trying to truly work around this problem would require you to restructure your query so as to avoid the correlated subquery on the linked server.
One possibility could be to include your linked table as a join in a grouped select and evaluate the aggregate count in that statement.
SELECT
P.Code, P.Name AS PositionName, P.CompCommitteeMember, Count(*)
FROM Positions P
Left Join Employees E on E.PositionID = P.PositionID
WHERE P.PositionID = '{D1B0912D-B1A5-11D4-BBDD-0004ACC5B8A7}'
group by P.Code, P.Name, P.CompCommitteeMember