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:

我正在尝试对一个远程的2000服务器运行一个查询;但是本地服务器生成的查询是不正确的，并导致远程服务器返回错误:

The column prefix 'Tbl1002' does not match with a table name or alias name used in the query.

列前缀“Tbl1002”与查询中使用的表名或别名不匹配。

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.

当您跟踪远程服务器时，您可以看到sp_cursorprepexec批处理实际上是无效的SQL;它引用了一个不存在的dervied表Tbl1002。

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.

列前缀“Tbl1002”与查询中使用的表名或别名不匹配。

In order to eliminate any confusion around the guid in the WHERE clause, we'll eliminate the WHERE clause:

为了消除WHERE子句中guid的任何混淆，我们将消除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 it still fails with:

但它还是失败了:

The column prefix 'Tbl1002' does not match with a table name or alias name used in the query.

列前缀“Tbl1002”与查询中使用的表名或别名不匹配。

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.

列前缀“Tbl1002”与查询中使用的表名或别名不匹配。

Further down we'll even eliminate the linked servers, and run the query locally on the 2000 machine.

更进一步，我们甚至会删除链接服务器，并在2000年的机器上本地运行查询。

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:

使用Profiler，我们可以看到向远程服务器发送的查询。这是一个巨大的可怕的混乱，但它绝对是无效的。它试图引用一个不在范围内的派生表。对于那些在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:

真正的问题是，服务器被另一个SQL服务器要求准备的SQL语句。裁剪的过程中,它说:

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:

这是一个混乱的读取，但是你可以看到问题，它在一些嵌套的派生表中引用Tbl1002:

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:

我们正在尝试查询的“远程”服务器(“wclhr”)是使用SP4的SQL server 2000:

Microsoft SQL Server 2000 - 8.00.2066 (Intel X86) May 11 2012 18:41:14

微软SQL Server 2000 - 8.00.2066 (Intel X86) 2012年5月11日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.

在发出查询时，我们尝试了SQL Server 2005和SQL Server 2008 R2。它曾经在两台服务器都是SQL Server 2000的时候工作。

Starting with SQL Server 2005, and continuing to 2008 R2, it is generating invalid SQL!

从SQL Server 2005开始，继续到2008年的R2，它正在生成无效的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.

这将阻止本地SQL Server 2008 R2生成2000机器的无效SQL。

The local servers are not 64-bit, but we upgraded the catalogs on SQL Server 2000 anyway. It didn't fix it.

本地服务器不是64位的，但是我们还是升级了SQL Server 2000的目录。它没有解决它。

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:

不信的人不相信这个范围是问题。请放心,它是。我的原始查询正确地运行于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:

不幸的是，SQL Server 2005/2008/2008R2优化器将该查询转换为等效查询，但不幸的是，SQL Server 2000无法执行:

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:

在SQL Server 2000上，

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.

SQL Server 2000似乎对相关子查询有范围问题;这在SQL Server 2005中得到了“改进”。

Bonus Reading

Microsoft Connect: sql2005 sp2 - issue with linked server (sql2000) and correlated subquery in where clause
Microsoft Connect: sql2005 sp2 -与链接服务器(sql2000)和相关子查询在where子句中进行连接。
Correlated subquery fails when using a Table on Linked Server
当使用链接服务器上的表时，相关子查询失败。
KB825019: FIX: A linked server query fails with the error message "Statement(s) could not be prepared" in SQL Server 2000
修正:在SQL server 2000中，一个链接的服务器查询失败，错误消息“语句(s)不能准备”。

1 个解决方案

#1

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

#1

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

秒客网

列前缀“%s”与查询中使用的表名或别名不匹配。

What if you run it on the remote server itself?

What is the generated query, how do you know it is bad?

What versions of SQL Server are we talking about here?

Other things we've tried

Isn't your original query just as wrong?

Bonus Reading

1 个解决方案

#1

#1

相关文章