я пытаюсь выполнить запрос к удаленному серверу 2000; но запрос, создаваемый локальным сервером, некорректен, и удаленный сервер возвращает ошибку:

Префикс столбца «Tbl1002» не соответствует имени таблицы или псевдониму, используемому в запросе.

Когда вы отслеживаете удаленный сервер, вы можете видеть, что пакет sp_cursorprepexec на самом деле является недопустимым SQL; у него есть ссылка на производную таблицу Tbl1002, которой не существует.

Запрос, который я запускаю на своем локальном сервере:

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}' 

Где Employees и Positions — это представления, которые просто выбираются со связанного сервера. Чтобы устранить эту путаницу, мы удалим представления и будем использовать имена четырех частей напрямую:

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}' 

И запрос все еще терпит неудачу с:

Префикс столбца «Tbl1002» не соответствует имени таблицы или псевдониму, используемому в запросе.

Чтобы устранить путаницу с guid в предложении WHERE, мы удалим предложение 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

И это все еще терпит неудачу с:

Префикс столбца «Tbl1002» не соответствует имени таблицы или псевдониму, используемому в запросе.

Чтобы устранить любую путаницу, связанную с использованием * в COUNT, мы устраним ее и вместо этого будем считать только константу:

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

И это все еще терпит неудачу с:

Префикс столбца «Tbl1002» не соответствует имени таблицы или псевдониму, используемому в запросе.

Далее мы даже удалим связанные серверы и запустим запрос локально на машине 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

Это работает нормально.

Что такое сгенерированный запрос, как понять, что он плохой?

Используя 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

Настоящая проблема заключается в операторе SQL, который сервер попросил подготовить другой SQL Server. Обрезанный, он говорит:

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'}'

Это беспорядочное чтение, но вы можете видеть проблему, это ссылка на Tbl1002 внутри некоторых вложенных производных таблиц:

WHERE "Tbl1005"."PositionID"="Tbl1002"."PositionID"

Но только заявляя об этом снаружи; в конце:

FROM "CasinoHR"."dbo"."Positions" "Tbl1002"  

О каких версиях SQL Server здесь идет речь?

«Удаленный» сервер, к которому мы пытаемся выполнить запрос («wclhr»), — это SQL Server 2000 с пакетом обновления 4 (SP4):

Microsoft SQL Server 2000 — 8.00.2066 (Intel X86) 11 мая 2012 г., 18:41:14

При выдаче запроса мы пробовали SQL Server 2005 и SQL Server 2008 R2. Раньше это работало, когда оба сервера были SQL Server 2000.

Начиная с SQL Server 2005 и до 2008 R2, он генерирует неверный SQL!

Другие вещи, которые мы пробовали

Удивительно, ужасный хак для запуска:

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

Это не позволяет локальному SQL Server 2008 R2 генерировать недопустимый sql для машины 2000 года.

Локальные серверы не 64-разрядные, но мы все равно обновил каталоги на SQL Server 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}' 

К сожалению, оптимизатор 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'

Что на 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, похоже, имеет проблемы с областью действия коррелированных подзапросов; которые были улучшены в SQL Server 2005.

Бонусное чтение

• Microsoft Connect: sql2005 sp2 - проблема со связанным сервером (sql2000) и коррелированным подзапросом в предложении where
• Коррелированный подзапрос не работает при использовании Таблица на связанном сервере
• KB825019: FIX: запрос связанного сервера завершается сбоем с сообщением об ошибке "Утверждение (s) не может быть подготовлен" в SQL Server 2000