Использование OPENQUERY (хранимая процедура exec) для создания новой временной таблицы завершается с ошибкой 11526.

На моем ПК для разработки установлена ​​полная версия SQL Server 2012.

Я пытаюсь следовать примерам здесь, которые показать, как создать новую временную таблицу, используя хранимую процедуру в качестве источника данных. Я пытаюсь объединить результаты нескольких хранимых процедур в одну временную таблицу (структура столбца/определение различных наборов результатов идентична).

Чтобы проверить, работает ли сантехника, я выдаю этот запрос:

 SELECT * FROM OPENQUERY("FOO\SQL2012", 'exec mySchema.myStoredProc')

Но я получаю эту ошибку из этого простого запроса выбора проверки сантехники:

Сообщение 11526, уровень 16, состояние 1, процедура sp_describe_first_result_set, строка 1
Не удалось определить метаданные, поскольку инструкция 'insert #tmp(foo1, foo2, foo3) select 'O' as foo1, foo2, foo3' в процедуре myStoredProc используется временная таблица.

Если я правильно понимаю ошибку, OPENQUERY зависит от того, может ли сервер извлекать типы данных столбца из постоянного определения в базе данных, а временная таблица, созданная в моей хранимой процедуре, будучи эфемерной, не имеет постоянного определение. Если это так, есть ли какой-либо параметр, который указывает OPENQUERY сделать все возможное и попытаться сделать разумное предположение о типах данных столбца?

Вот фиктивный SP, с которым я тестирую:

create proc testproc
as
begin

create table #test
(id int, name varchar(5) );

insert into #test(id,name)values(1,'xxx');
select * from #test;
--drop table #test;   -- tried dropping and not dropping, same error either way
end

metadata sql-server-2012 openquery
person Tim    schedule 12.01.2013    source источник

Ответы (4)


arrow_upward
13
arrow_downward

Попробуй это:

SELECT *
FROM OPENQUERY("FOO\SQL2012", 'SET FMTONLY OFF; EXEC mySchema.myStoredProc;') X;

Причина этого в том, что при выполнении хранимой процедуры на связанном сервере поставщик сначала пытается определить форму результирующего набора строк. Он делает это, выдавая SET FMTONLY ON;, а затем запуская ваш оператор. В хранимой процедуре, которая не использует временные таблицы, это прекрасно работает. Парсер запросов в основном выполняет пробный прогон, фактически не извлекая все данные, а только метаданные (вроде показа предполагаемого плана выполнения).

Проблема в том, что когда хранимая процедура действительно использует временные таблицы, она завершается ошибкой, потому что метаданные временной таблицы не существуют: их нельзя собрать с помощью метаанализа, который работает для хранимых процедур, которые не используйте временные таблицы. Таким образом, решение состоит в том, чтобы вручную SET FMTONLY OFF; выполнить хранимую процедуру в пакете.

Имейте в виду, что использование этого метода заставит хранимую процедуру запуститься дважды. Первый раз для сбора метаданных (данные отбрасываются), а второй раз для фактического возврата данных. Если вызываемая хранимая процедура является особенно дорогостоящей или имеет побочные эффекты, вам может потребоваться сделать поправку.

Наконец, обратите внимание, что этот трюк работает не для каждой хранимой процедуры. Есть вещи, которые могут делать хранимые процедуры, которые просто мешают работе. Я не знаю всех возможностей, но одна из них возвращает несколько наборов записей.

В ответ на ваше обновление о том, что SET FMTONLY OFF не работает: можете ли вы реструктурировать свой SP, чтобы не использовать временную таблицу или использовать постоянную таблицу с сеансовым ключом? Любой из этих вариантов может подойти. В SQL Server 2012 у вас также есть возможность передавать данные с параметрами, возвращающими табличное значение.

Вы можете прочитать статью Эрланда Соммарскога Как обмениваться данными между хранимыми процедурами, так как она может вдохновить вас на то, как это сделать. ваша цель.

person ErikE    schedule 13.01.2013
comment
Извините за долгую задержку с ответом. Не заметил уведомления. Я получаю ту же ошибку при использовании подхода SET FMTONLY OFF. - person Tim; 31.07.2013
comment
Спасибо @ErikE! Ты проливаешь свет на темноту - person G21; 12.11.2015
comment
Я всегда обходил это, используя табличную переменную (тьфу). Я ненавижу это делать, но это работает. - person PseudoToad; 07.07.2016
comment
@Gizmo Проблема с табличными переменными заключается в том, что для них не создается статистика, как это делают временные таблицы ... - person ErikE; 07.07.2016
comment
@ErikE ... есть много проблем с табличными переменными, и их никогда не следует использовать, если нет другого варианта. В данном случае это был единственный способ заставить его работать в 2012 году (отсюда и «тьфу»). ИМХО, следует использовать только переменные таблицы времени: 1) когда содержимому временного объекта (таблицы) необходимо пережить откат, 2) когда возникают проблемы с перекомпиляцией и 3) когда все остальное терпит неудачу. - person PseudoToad; 11.07.2016

arrow_upward
3
arrow_downward

Добавление «WITH RESULT SETS [NONE | UNDEFINED]» в конец вызова EXEC должно решить эту проблему. http://technet.microsoft.com/en-us/library/ms188332.aspx

person techmell    schedule 29.11.2013
comment
Я получаю эту ошибку: Cannot process the object "exec testproc WITH RESULT SETS NONE". The OLE DB provider "SQLNCLI11" for linked server "FOO\SQL2012" indicates that either the object has no columns or the current user does not have permissions on that object. - person Tim; 09.01.2014

arrow_upward
1
arrow_downward

если у гостевого сервера sp нет набора результатов (выборочный запрос) в начале, связанный сервер не может проанализировать набор результатов. вариант, который я использую, следующий:

create procedure test
as

    if 1=2
    select a, b, c from table

    declare @variable varchar(10)
    ----...and rest of your procedure...

    --in the end
    select a, b, c from table
person Peter Jacob    schedule 15.01.2015
comment
Я не понимаю цели первого if-теста: если 1=2 (т.е. ложь). Выполняется ли в любом случае оператор select? Поставщик ищет фактический набор результатов или ищет оператор SQL, который мог бы создать набор результатов? - person Tim; 15.09.2015

arrow_upward
0
arrow_downward

Если удаленная процедура возвращает набор результатов, важно запустить процедуру с SET NOCOUNT ON, иначе первый набор результатов будет количеством строк без каких-либо метаданных, и всегда будет генерироваться ошибка.

Затем можно определить результирующий набор, используя WITH RESULT SETS((field1 type1, field2 type2...)) в конце оператора EXEC.

(Это не дает прямого ответа на вопрос, но у меня была та же ошибка, потому что моя хранимая процедура создала временную таблицу для хранения промежуточных результатов.)

person grahamj42    schedule 03.09.2020