I am trying to start a request from a remote server 2000; but the request that the local server generates is incorrect and causes the remote server to return an error:
The column prefix 'Tbl1002' does not match the table name or alias used in the query.
When you trace the remote server, you can see that the sp_cursorprepexec package is essentially invalid SQL; it has a link to table Tbl1002 , which does not exist.
The request that I run on the local server:
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 selected from a linked server. To eliminate this confusion, we will eliminate the views - and directly use the names of the four parts:
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 request is still not running:
The column prefix 'Tbl1002' does not match the table name or alias used in the query.
To eliminate any confusion around guid in the WHERE , we will exclude the WHERE :
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 still fails:
The column prefix 'Tbl1002' does not match the table name or alias used in the query.
To eliminate any confusion surrounding the use of * in COUNT , we will eliminate it, and instead we will only consider the 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 still fails:
The column prefix 'Tbl1002' does not match the table name or alias used in the query.
Further, we will even exclude linked servers and run the query locally on machine 2000.
What if you run it on a remote server?
If I run this request on the most remote server:
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 great.
What is a generated query, how do you know this is bad?
Using Profiler, we can see the request coming to the remote server. This is a huge terrible mess, but it is definitely not valid. It tries to reference a view that is not in scope. The whole batch will be familiar to everyone who worked 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 problem is the SQL statement that the server requested from another SQL Server to prepare. Circumcised, he 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'}'
This is a dirty read, but you can see the problem by referring to Tbl1002 inside some nested Tbl1002 :
WHERE "Tbl1005"."PositionID"="Tbl1002"."PositionID"
But only declaring it outside; in the end:
FROM "CasinoHR"."dbo"."Positions" "Tbl1002"
What versions of SQL Server are we talking about here?
The "remote" server we are trying to query ("wclhr") is SQL Server 2000 Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2066 (Intel X86) May 11, 2012 6:41:14 PM
When issuing a query, we try to use SQL Server 2005 and SQL Server 2008 R2. It worked when both servers were SQL Server 2000.
Starting with SQL Server 2005 and continuing through 2008 R2, it generates invalid SQL!
Other things we tried
Surprisingly, a terrible hacked move:
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
This stops local SQL Server 2008 R2 from creating invalid sql for machine 2000.
Local servers are not 64-bit, but we updated the directories on SQL Server 2000 anyway . This is not a fix.
Is your original request wrong?
@Damien the Unbeliever does not believe that the problem may be the problem. Be sure, this is so. My original query works correctly with 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 converts this query into an equivalent query, but unfortunately the one that SQL Server 2000 cannot 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'
What 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 problems with correlating subqueries; which have been "enhanced" in SQL Server 2005.
Reading bonuses