Exec ms sql: EXECUTE (Transact-SQL) — SQL Server

SQL.RU | Обходим ошибку An INSERT EXEC statement cannot be nested

В SQL Server есть ограничение на инструкцию INSERT EXEC — она не может быть вложенной. Т.е. если в теле процедуры мы уже используем код INSERT EXEC, то рекордсет из этой процедуры мы не сможем вставить в таблицу. На Microsoft Connect есть фитбек с этой проблемой (Cannot have nested INSERT … EXEC) и совсем недавно эту проблему закрыли с пометкой as Won’t Fix.

Но, что делать, если нам все-таки необходимо вывести результат работы процедуры в таблицу? Именно тому, как обойти одно из ограничений сиквела и посвящён этот пост.

Для иллюстрации создадим тестовую БД TestDB и две процедуры, одна будет возвращать небольшой рекордсет, а во второй мы будем вызывать эту процедуру с использованием инструкции INSERT EXEC.

create database TestDB
go

use TestDB
go

if object_id ( 'dbo.TestProc01', 'P' ) is not null
  drop procedure dbo. TestProc01 
go

create procedure dbo.TestProc01
as
set nocount on
declare @t table ( i int )
insert into @t
values (1),(2),(3)
select * from @t
go

if object_id ( 'dbo.TestProc02', 'P' ) is not null
  drop procedure dbo.TestProc02 
go

create procedure dbo.TestProc02
as
set nocount on
declare @t table ( i int )
insert into @t
exec dbo.TestProc01
select * from @t
go

Дальше небольшой скрипт, который и эмулирует, озвученную выше, ошибку:

declare @t table ( i int )
insert into @t
exec dbo.TestProc02

А теперь несколько способов обойти эту ошибку:

1) Первый и самый правильный

По возможности не использовать вложенных инструкций INSERT EXEC, либо вместо вызова процедуры, в которой уже используется такая конструкция, взять часть скрипта из тела этой самой процедуры. Как правило, это достаточно легко можно реализовать. В моём примере достаточно заменить вызов процедуры dbo.TestProc02 на dbo.TestProc01.

2) Используем OPENQUERY или OPENROWSET

Для этого нам потребуется создать Linked Server с ссылкой нашего сервера БД на самого себя ( в моём случае это IP 127.0.0.1).

use master
go

exec sp_addlinkedserver N'127.0.0.1'
                      , N'SQL Server';
go

use TestDB
go

declare @t table ( i int )
insert into @t
select * from OpenQuery ( [127.0.0.1], 'TestDB.dbo.TestProc02' )
select * from @t

3) Используем распределённый запрос

declare @t table ( i int )
insert into @t
exec [127.0.0.1].TestDB.dbo.TestProc02
select * from @t
go
--либо:
declare @t table ( i int )
insert into @t
exec ( 'TestDB.dbo.TestProc02' ) at [127.0.0.1]
select * from @t
go

Не забываем включить службу Координатор распределенных транзакций Иначе получим ошибку:

4) Используем процедуру xp_cmdshell и утилиту SQLCMD

Но для начала включим использование процедуры xp_cmdshell

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
go

А теперь выгружаем результат работы процедуры dbo. TestProc02 в таблицу:

declare @t table ( val varchar(100) )
insert into @t
exec master..xp_cmdshell 'sqlcmd -E -q "exec TestDB.dbo.TestProc02" -h -1 -W'

select val from @t
where val is not null

5) Используем процедуру xp_cmdshell и утилиту BCP

--Выгружаем результат процедуры dbo.TestProc02 на диск
exec xp_cmdshell 'bcp "exec TestDB.dbo.TestProc02" queryout "c:\temp\Test.txt" -T -c -C RAW -r\n -t\char(3)'
--Создадим таблицу для получения результата 
if object_id ( 'dbo.tmpMyResult', 'U' ) is not null
drop table tmpMyResult 
go 
create table tmpMyResult ( val int )
--Загружаем результат с диска в таблицу
exec xp_cmdshell 'bcp TestDB.dbo.tmpMyResult in "c:\temp\Test.txt" -T -c -C RAW -r\n -t\char(3)'  
--Смотрим
select * from tmpMyResult

6) Используем CLR

Но этот вариант я не буду рассматривать в рамках этого поста.

Обзор EXEC SQL и примеры

В этой статье мы рассмотрим оператор EXEC SQL в SQL Server и рассмотрим несколько примеров.

Команда EXEC используется для выполнения хранимой процедуры или переданной ей строки SQL. Вы также можете использовать полную команду EXECUTE, аналогичную EXEC.

Синтаксис команды EXEC в SQL Server

Ниже приведен основной синтаксис команды EXEC в SQL Server.

Чтобы проиллюстрировать примеры, я создам образец хранимой процедуры и таблицы.

Выполнение хранимой процедуры

Чтобы выполнить хранимую процедуру с помощью EXEC, передайте имя процедуры и параметры, если они есть. Пожалуйста, обратитесь к приведенному ниже сценарию T-SQL для выполнения хранимой процедуры.

Мы также можем присвоить переменной значение, возвращаемое хранимой процедурой. Пожалуйста, обратитесь к следующему примеру скрипта T-SQL.

Исполняющая строка

Чтобы выполнить строку, создайте строку и передайте ее команде EXEC SQL. Пожалуйста, обратитесь к приведенному ниже примеру, который выполняет строку.

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

Создание строки из переменной и ее выполнение с помощью команды EXEC SQL может привести к внедрению нежелательного кода. Есть несколько способов избежать SQL-инъекций. Мы рассмотрим эти методы в другой статье.

Выполнение запросов на удаленном сервере

Предложение linked_server_name вместе с командой EXEC используется для выполнения запросов на удаленном сервере. Связанный сервер должен быть настроен и Опция RPC Out должна быть включена на связанном сервере для выполнения запросов на удаленном сервере.

Пожалуйста, обратитесь к следующему примеру выполнения запроса на удаленном сервере. Замените имя связанного сервера на имя вашего связанного сервера.

Если мы не укажем имя базы данных, оператор EXEC SQL выполнит запрос к базе данных по умолчанию для входа, используемого на связанном сервере.

Если вы хотите выполнить запрос в определенной базе данных, используйте в запросе «USE имя_базы_данных». Пожалуйста, обратитесь к приведенному ниже примеру.

Мы также можем выполнить запрос выбора к удаленному серверу, используя нотацию из четырех частей. Мы должны включить опцию Data Access на связанном сервере. Пожалуйста, обратитесь к приведенному ниже примеру.

Чтобы выполнить хранимую процедуру на удаленном сервере, используйте приведенный ниже сценарий T-SQL, заменив имя связанного сервера, имя базы данных и имя хранимой процедуры.

Ниже приведен пример выполнения хранимой процедуры на связанном сервере с использованием нотации из четырех частей. Здесь « TEST01V» — имя сервера, « test » — имя базы данных, а « dbo » — имя схемы.

ВЫПОЛНЕНИЕ С ПЕРЕКОМПИЛЯЦИЕЙ

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

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

После выполнения приведенного выше сценария T-SQL я выполнил приведенный ниже сценарий, чтобы проверить кэшированный план.

Пожалуйста, обратитесь к изображению ниже. Выполнение процедуры с опцией перекомпиляции не сохранило план в кэше.

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

Пожалуйста, обратитесь к изображению ниже для набора результатов вышеуказанного запроса. Мы видим, что идентификатор плана и счетчики использования одинаковы, а существующий план не изменился. EXEC WITH RECOMPILE не использовал существующий план в кеше и создал новый план, использовал его и отбросил.

ВЫПОЛНИТЬ С НАБОРАМИ РЕЗУЛЬТАТОВ

Этот параметр используется для изменения набора результатов хранимой процедуры или строки, выполняемой в соответствии с определением, указанным в предложении WITH RESULT SETS.

См. следующий пример выполнения хранимой процедуры с НАБОРАМИ РЕЗУЛЬТАТОВ.

Мы можем изменить заголовки набора результатов и тип данных возвращаемого столбца, выполнив хранимую процедуру. Это похоже на использование convert(), cast() и псевдонимов столбцов в обычном скрипте T-SQL.

Если процедура или строка T-SQL возвращает более одного набора результатов, мы должны определить несколько наборов результатов в предложении WITH RESULTS SETS, иначе будет выдана следующая ошибка: «Операция EXECUTE не удалась, поскольку в предложении WITH RESULT SETS указан 1 набор результатов. ), и инструкция пыталась отправить больше наборов результатов, чем это».

Обратитесь к следующему примеру, чтобы использовать предложение WITH RESULTS SETS в операторе EXEC SQL для нескольких наборов результатов, возвращаемых хранимой процедурой или строкой. В этом примере хранимая процедура возвращает два одинаковых набора результатов. Я определил два результата в предложении WITH RESULTS SETS, изменив тип данных и заголовки набора результатов в обоих наборах результатов.

Заключение

В этой статье мы рассмотрели различные аспекты инструкции EXEC SQL на нескольких примерах. Если у вас есть какие-либо вопросы, пожалуйста, не стесняйтесь задавать их в разделе комментариев ниже.

• Автор
• Последние сообщения

Ранга Бабу

Администратор базы данных SQL Server, разработчик с большим опытом администрирования SQL Server, разработки, настройки производительности, мониторинга, технологий высокой доступности и аварийного восстановления

Последние сообщения Ранги Бабу (см. все)

Как выполнить хранимую процедуру в службах SSIS и выполнить SQL Задача

Задача «Выполнение SQL» — одна из наиболее широко используемых задач в службах SSIS для взаимодействия с источником данных СУБД. Задача «Выполнение SQL» используется для самых разных целей, включая усечение таблицы промежуточных данных перед импортом, получение количества строк для определения следующего шага в рабочем процессе или вызов хранимых процедур для выполнения бизнес-логики с наборами промежуточных данных. Эта задача также используется для извлечения информации из репозитория базы данных. Задача «Выполнение SQL» также присутствует в устаревшем продукте DTS, но версия SSIS предоставляет лучший редактор конфигурации и методы сопоставления параметров хранимой процедуры для считывания результатов и выходных значений.
В этом разделе вы познакомитесь со всеми возможными способами настройки этой задачи, проработав различные способы ее использования. Вы узнаете, как выполнять параметризованные операторы SQL или выполнять пакеты операторов SQL, как получать однострочные и многострочные результаты и как выполнять хранимые процедуры.

Выполнение параметризованного оператора SQL

Задача может выполнять команду SQL двумя основными способами: выполнением встроенных операторов SQL или выполнением хранимых процедур. Результирующее действие также может привести к необходимости выполнить один из двух вариантов: принять возвращаемые значения в параметрах или результирующий набор. Вы можете получить представление о том, как можно настроить задачу для выполнения этих комбинаций, на вкладке «Общие» редактора задач «Выполнение SQL», показанной на рис. 3-21. Здесь задача «Выполнение SQL» настроена на выполнение операции обновления в таблице DimProduct с использованием встроенного оператора SQL с параметром на основе переменной. Это самое простое использование задачи «Выполнение SQL», поскольку вам не нужно настраивать свойства вкладки «Набор результатов».

Обратите внимание на рис. 3-21, что вкладка «Общие» содержит основные свойства задачи. Здесь задача настроена так, чтобы она указывала на соединение OLE DB. Другие параметры ConnectionType включают соединения ODBC, ADO, ADO.NET, SQLMOBILE и даже EXCEL. Подвох всей этой гибкости подключения заключается в том, что задача «Выполнение SQL» ведет себя по-разному в зависимости от базового поставщика данных. Например, свойство SQLStatement на рис. 3-21 показывает непосредственно введенный оператор T-SQL со знаком вопроса в операторе. Полное заявление здесь:

 ОБНОВЛЕНИЕ DimProduct Set Color = ‘Red’ Где ProductKey = ? 

Знак ?, указывающий на то, что параметр является обязательным, является классической маркировкой параметра ODBC и используется в большинстве других поставщиков, за исключением поставщика ADO.NET, который использует именованные параметры. Это важно, потому что в задаче вам нужно настроить параметры оператора SQL на вкладке «Сопоставление параметров», как показано на рис. 3-22.

Здесь коллекция сопоставления параметров сопоставляет первый параметр [порядковый номер нуля (0)] с пользовательской переменной. При сопоставлении параметров с подключениями и базовыми поставщиками используйте следующую таблицу для настройки этой вкладки в Редакторе задач:

Поскольку здесь используется поставщик OLE DB, маркер параметра — ?, а параметр использует порядковый номер, отсчитываемый от нуля. Другое сопоставление, которое вам нужно было бы сделать здесь, — это тип данных параметра. Эти типы данных также различаются в зависимости от вашего основного поставщика. Службы SSIS очень специфичны в отношении того, как вы сопоставляете типы данных, поэтому вам может потребоваться поэкспериментировать или просмотреть учебники в Интернете, чтобы найти эквиваленты сопоставления для ваших параметров и поставщика. Мы рассмотрим многие распространенные проблемы в этом отношении в этом разделе, но для этого начального примера мы сопоставили System::ContainerStartTime с типом данных OLE DB DATE. В этот момент может быть выполнена задача «Выполнение SQL» с этим простым оператором обновления, и ModifyDate будет обновлен в базе данных текущим значением DateTime.

Вариантом этого примера может быть случай, когда оператор может быть динамически сгенерирован во время выполнения и просто запущен в диспетчере соединений. Свойство SQLSourceType на вкладке «Общие» допускает три различных типа разрешения операторов SQL: либо прямой ввод (как мы сделали), либо через переменную, либо из подключения к файлу. Другой способ построить оператор SQL — использовать кнопку действия Build Query. Это вызывает инструмент Query-By-Example (QBE), который помогает вам построить запрос, щелкая таблицы и устанавливая отношения. Опция на основе переменных также проста. Обычно вы определяете переменную, которая разрешается из выражения. Установка для свойства SQLSourceType в задаче «Выполнение SQL» значения «Переменная» позволяет выбрать переменную, которая будет разрешаться в инструкцию SQL, которую вы хотите выполнить.

Другой вариант, использующий подключение к файлу, требует более подробного обсуждения.

Выполнение пакета операторов SQL

Если вы используете параметр File Connection свойства SQLSourceType задачи «Выполнение SQL», обычно вы делаете это для выполнения пакета операторов SQL. Все, что вам нужно сделать, это иметь файл, содержащий пакет инструкций SQL, доступных для пакета SSIS во время выполнения. Настройте подключение к файлу, чтобы указать на пакетный файл, который необходимо запустить. Убедитесь, что ваш пакет SQL соответствует нескольким правилам. Некоторые из этих правил являются типичными правилами SQL, например использование команды GO между операторами, но другие относятся к задаче SSIS Execute SQL. Используйте эти правила в качестве руководства для выполнения пакета операторов SQL:

• Используйте операторы GO между каждой отдельной командой. Обратите внимание, что некоторые провайдеры позволяют использовать точку с запятой (;) в качестве разделителя команд.
• Если в пакете несколько параметризованных операторов, все параметры должны совпадать по типу и порядку.
• Только одна инструкция может возвращать результат, и это должна быть первая инструкция.
• Если пакет возвращает результат, то столбцы должны совпадать с тем же номером и правильно названными столбцами результатов для задачи «Выполнение SQL». Если они не совпадают, и у вас есть последующие операторы UPDATE или DELETE в пакете, они будут выполняться, даже если результаты не связаны, и возникает ошибка. Пакет отправляется на SQL Server для выполнения и ведет себя так же.

Возврат результатов — это то, что мы не исследовали в задаче «Выполнение SQL», поэтому давайте рассмотрим несколько примеров, которые делают это в службах SSIS.

Захват одноэлементных результатов

На вкладке «Общие» задачи «Выполнение SQL» можно настроить задачу для захвата ожидаемого типа результата, настроив свойство ResultSet. Это свойство может быть настроено так, чтобы ничего не возвращать или None, одноэлементный результирующий набор, многострочный результат или строка в формате XML. Любой параметр, кроме None, требует настройки вкладки ResultSet в редакторе. На вкладке «Набор результатов» вы определяете привязку возвращаемых значений к конечному набору переменных служб SSIS. Для большинства привязок типов данных это не проблема. Вы выбираете тип данных переменной SSIS, который наиболее точно соответствует типу данных вашего поставщика. Проблемы, возникающие в результате этого действия, вызваны недопустимым приведением, которое происходит, когда данные в потоке табличных данных (TDS) от базового поставщика сталкиваются с переменными типами данных, которым они назначаются. Это преобразование происходит внутри задачи «Выполнение SQL», и вы не имеете над ней контроля, как в задаче «Скрипт». Прежде чем предположить, что это просто проблема назначения типа данных, вам нужно понять, что SSIS является наименьшим общим знаменателем, когда речь идет о возможности привязки к типам данных от всех возможных поставщиков данных. Например, в службах SSIS нет валютного или десятичного типа данных. Единственное, что близко, — это тип данных double, который должен использоваться для вещественных, числовых, текущих, десятичных, плавающих и других подобных типов данных.

Извлечение учебных пособий по службам SSIS

В следующем примере задается простая встроенная инструкция SQL, которая возвращает одну строку (или одноэлементный результат), чтобы показать как обычные, так и исключительные случаи настройки Execute. Задача SQL и решение этих проблем с привязкой. Во-первых, мы будем использовать простую инструкцию T-SQL для базы данных AdventureWorks, которая выглядит следующим образом (файл кода Ch03SQL. txt):

SELECT TOP 1
CarrierTrackingNumber,
LineTotal,
OrderQty,
UnitPrice
From Sales.SalesOrderDetail

Мы выбрали этот странный результирующий набор из-за нескольких типов данных в таблице SalesOrderDetail. Эти типы данных дают возможность выделить некоторые решения проблем с сопоставлением этих типов данных в задаче «Выполнение SQL», с которой мы помогаем людям с момента первого выпуска служб SSIS.

Чтобы захватить эти столбцы из этой таблицы, вам нужно создать некоторые переменные в пакете. Затем эти переменные будут сопоставлены один к одному со столбцами результатов. Некоторые отображения просты. CarrierTrackingNumber можно легко сопоставить с типом данных строковой переменной с типами данных varchar или varchar в задаче «Выполнение SQL». Поле OrderQty, использующее тип данных SQL Server smallint, необходимо сопоставить с типом данных SSIS int16. Неправильное сопоставление типа данных приведет к ошибке, подобной этой:

[Выполнение задачи SQL] Ошибка: Произошла ошибка при присвоении значения переменной
«OrderQty»: «Тип значения, присваиваемого переменной
«User::OrderQty»
, отличается от текущего типа переменной. Переменные не могут изменять тип во время выполнения
. Типы переменных являются строгими, за исключением переменных типа
Object».

Два других значения для столбцов SQL Server UnitPrice (деньги) и LineTotal (числовые) сложнее. Ближайшим эквивалентным типом данных переменной в SSIS является тип данных double.
Теперь параметры можно просто сопоставить на вкладке «Выполнить набор результатов задачи SQL», как показано на рис. 3-23. Свойство Имя результата сопоставляется с именем столбца в операторе SQL или его порядковым номером (начиная с 0).

Просто используйте кнопки «Добавить» и «Удалить», чтобы расположить элементы результата в том порядке, в котором они должны быть возвращены, назовите их в соответствии с требованиями поставщика и получите правильные типы данных, и все будет в порядке. Если они расположены в неправильном порядке или если задача «Выполнение SQL» не может преобразовать типы данных из TDS в соответствующий тип переменных данных, вы получите ошибку привязки. Это должно дать вам общее руководство по использованию задачи «Выполнение SQL» для сбора одноэлементных результатов.

Многострочные результаты

Как правило, вы собираете многострочные результаты из базы данных в виде набора записей или XML-файла (в частности, между источниками данных SQL Server) для использования в другой задаче сценария для целей анализа или принятия решений, чтобы предоставить перечислитель в задаче Foreach или Looping Task или для подачи в задачу Data Flow для обработки. Настройте свойства SQLSourceType и SQLStatement для вызова либо встроенной инструкции SQL, либо хранимой процедуры. В любом случае вы должны установить для свойства ResultSet на вкладке General значение Full ResultSet, а вкладка ResultSet настроена для захвата результатов. Единственное отличие от захвата одноэлементного результата заключается в том, что вам нужно захватить весь результат в переменную, а не отображать каждый столбец. Тип данных, который вы должны использовать для сбора результатов, зависит от того, что вы собираете. XML-файл может быть записан либо в виде строки, либо в виде объектного типа данных. Набор записей может быть захвачен только в переменной с типом данных объекта. Пример задачи «Выполнение SQL», настроенной для создания типа данных объекта для хранения результатов выбора строк из файла Sales. Таблица SalesOrderDetail показана на рисунке 3-24. Обратите внимание, что на вкладке ResultSet показан захват этих строк с требуемым нулевым порядковым номером.

После того, как набор записей сохранен как переменная, вы можете выполнять такие действия, как «уничтожение» набора записей. Термин измельчение означает итерацию набора записей по одной строке за раз в операции цикла по каждому элементу. Для каждой итерации вы можете захватить переменные и выполнить операцию над каждой строкой. На рис. 3-25 показано, как контейнер цикла по каждому элементу будет выглядеть при использовании набора записей на основе переменных.

Другой способ использования набора записей на основе переменных — использовать его для подачи преобразования данных. Для этого просто создайте преобразование исходного сценария в потоке данных и добавьте к нему столбцы, которые вы хотите реализовать из сохраненного набора записей, и передайте переменную набора записей. Затем добавьте код (файл кода Ch03SQL.txt), аналогичный следующему, чтобы преобразовать данные столбца из набора записей в выходной поток (для экономии времени и места в наборе записей реализуются только два столбца):

XML-версия записи результата в строку еще проще. Вам не нужно использовать компонент сценария, чтобы снова превратить строку XML в источник данных. Вместо этого используйте готовый компонент под названием «Источник XML» в потоке данных. Он может принимать переменную в качестве источника данных. (Рассмотрите пример, демонстрирующий, как это сделать, в разделе «Задача веб-службы» этой главы.)
Вы видите, что задача «Выполнение SQL» действительно весьма полезна при выполнении встроенных операторов SQL и извлечении результатов, поэтому теперь взгляните на как вы можете использовать хранимые процедуры в этой задаче.

Выполнение хранимой процедуры

Другой способ взаимодействия с СУБД — выполнение хранимых процедур, которые могут выполнять операции с источником данных для возврата значений, выходных параметров или результатов. Настройте задачу SSIS Execute SQL для выполнения хранимых процедур, указав вызов имени процедуры в свойстве SQLStatement вкладки General. Улов тот же, что и раньше. Поскольку задача «Выполнение SQL» находится поверх нескольких разных поставщиков данных, вам необходимо обратить внимание на то, как каждый поставщик обрабатывает вызов хранимой процедуры. В следующей таблице приведены сведения о том, как следует кодировать свойство SQLStatement в задаче «Выполнение SQL».0003

Возвращаясь к предыдущему примеру, в котором вы использовали встроенный оператор SQL для обновления даты изменения в деталях заказа на продажу, создайте хранимую процедуру T-SQL, которая делает то же самое (кодовый файл Ch03SQL.txt):

В онлайн-загрузках для этой главы мы создали пакет, который демонстрирует, как вызывать эту процедуру, используя как OLE DB, так и диспетчеры соединений ADO. NET. На вкладке «Общие» (показанной на рис. 3-26) свойство SQLStatement настраивается, как указано ранее в руководстве, с ключом ? маркеры параметров для одного входного параметра. Также обратите внимание, что свойство IsQueryStoredProcedure не включено. Вы не можете установить это свойство для поставщика OLE DB. Однако это свойство будет включено в версии ADO.NET задачи «Выполнение SQL» для выполнения той же процедуры. Если установить для IsQueryStoredProcedure для версии ADO.NET значение true, свойство SQLStatement также необходимо будет изменить. Удалите выполнение команды и маркеры параметров, чтобы они выглядели так:

Часто задаваемые вопросы и ответы на интервью SSIS

Usp_UpdatePersonAddressModifyDate. В этом режиме задача «Выполнение SQL» фактически создаст полный оператор выполнения, используя список параметров, который вы предоставили на вкладке «Сопоставление параметров» в редакторе задач.

Вкладка «Сопоставление параметров» в Редакторе задач зависит от базового поставщика, установленного в задаче «Выполнение SQL», как показано на рис. 3-27.

Для краткости на этом рисунке просто показано соединение OLE DB с параметрами. Однако при подключениях ADO.NET имена параметров следуют тем же правилам, которые вы использовали при применении параметров к встроенным операторам SQL ранее в этой главе, например, изменив параметр имени параметра на @MODIFIED_DATE.

Получение выходных параметров из хранимой процедуры

Сопоставление входных параметров для операторов SQL — это одно, но при обработке выходных параметров из хранимых процедур следует учитывать некоторые проблемы. Главное помнить, что все извлеченные выходные или возвращаемые параметры должны быть помещены в переменные, чтобы их можно было использовать в дальнейшем. Типы переменных определяются в службах SSIS, и у вас возникают те же проблемы, которые мы рассмотрели в разделе «Сбор результатов одноэлементных вычислений» для этой задачи. Короче говоря, вы должны иметь возможность выбирать правильные переменные при привязке результирующих выходных параметров поставщика к переменным SSIS, чтобы можно было получить успешное преобразование типа.

В качестве примера мы продублируем тот же тип SQL-запроса, который мы использовали ранее, со встроенным оператором SQL для получения одноэлементного результата, но здесь вместо этого вы будете использовать объект хранимой процедуры. Поместите следующую хранимую процедуру в базу данных AdventureWorks (кодовый файл Ch03SQL.txt):

В этом надуманном примере хранимая процедура предоставит четыре различных выходных параметра, которые вы можете использовать, чтобы научиться настраивать привязки выходных параметров. (Целые значения согласованы и легко сопоставляются почти со всеми поставщиками, поэтому нет необходимости демонстрировать это в этом примере.) Одно различие между возвратом одноэлементных выходных параметров и одноэлементной строки заключается в том, что на вкладке «Общие» задачи «Выполнение SQL» для свойства ResultSet установлено значение None, так как ни одна строка не должна возвращаться для захвата. Вместо этого для параметра «Параметры» на вкладке «Сопоставление параметров» будет установлено значение «Направление вывода», а типы данных сопоставлены в зависимости от поставщика.

Чтобы определенные параметры типа данных SQL Server соответствовали переменным SSIS, вам необходимо настроить параметры с помощью следующих сопоставлений:

Можно предположить, что у вас все еще будет Вспомните, вы пытались вернуть одиночный набор строк из встроенного оператора SQL с теми же типами данных и получили все типы ошибок связывания и приведения типов. Вам пришлось изменить свой встроенный оператор, чтобы привести эти значения, чтобы заставить их привязываться. Вам не нужно делать это при привязке к параметрам, потому что это приведение происходит вне потока табличных данных. При привязке параметров (в отличие от столбцов в потоке данных) числовой тип данных будет напрямую привязан к типу double, поэтому вы не получите ошибку, которая возникла бы, если бы те же данные были привязаны к набору строк. Мы не совсем уверены, почему это так, но, к счастью, хранимые процедуры не нужно изменять, чтобы использовать их в службах SSIS из-за проблем с привязкой выходных параметров.