Инструкция 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

Картинка с сайта: t-sql.ru

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

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
Но этот вариант я не буду рассматривать в рамках этого поста.

If you use the tSQLt unit testing framework for long enough, you will eventually run into the “An INSERT EXEC statement cannot be nested” problem. This problem happens when you try to insert the output of a stored procedure into a temporary table for validation, but that stored procedure internally already does the same thing, inserting the output of another stored procedure into a temporary table. SQL Server does not allow this.

Researching the problem will lead you to a lot of articles and posts in which people say that you must change your code, and for example use:

• Table Valued Functions
• Shared temporary tables
• Table Variables as output parameters
• Custom parameters on your stored procedures that are only applicable for testing
• Workarounds using OPENROWSET, Linked Servers or even xp_cmdshell+sqlcmd

All those solutions are hacky, and it is simply not always possible to change the underlying code. Nor should you. Code should be tested exactly how it will be executed and used in the wild, else your test is not worth much and you’ll soon have users, or worse your boss, at your desk complaining about world ending errors.

In your search for a solution, you hopefully found an article by Charles Silbergleith called tSQLt and the INSERT EXEC Problem. It is a good article. You should read it. Charles’s solution is to use a CLR. If you’ve never used a CLR before, it is just a .Net application (dll) that is imported into SQL Server and executed like a stored procedure. By using a CLR, we can execute any queries or stored procedures as if the code is being run by an external application, like it would in the real world, and capture output of the query into a temporary table for validation. But Charles’s CLR implementation is far too advanced. It is going to take hours to figure out and even longer to get it working. This article is a follow on to Charles’s work, a quick and simple CLR How-to specific to tSQLt unit testing.

In this article, we’ll setup a test scenario that throws the “An INSERT EXEC statement cannot be nested” error and then step by step build a CLR to fix it. With lots of screenshots to help you along the way. I promise, you do not need to know how to code in C#. Just follow the steps below and copy paste.

Right, let’s get started.

The Test Scenario

Let’s say for example you have a stored procedure called “ComplexBusinessCalc” which you would like to unit test. Internally your stored procedure uses other stored procedures that you absolutely cannot change. And your stored procedure works perfectly as-is when you execute it.

e.g.

Картинка с сайта: www.sqlservercentral.com

If you’d like to follow along and test out the CLR for yourself, you could use the following code to create the ComplexBusinessCalc stored procedure in your test environment.

create or alter procedure SomeReallyComplexProc
as
begin
    select 'hello' as val1;
end
go

create or alter procedure ComplexBusinessCalc
as
begin
    create table #tmp (val1 varchar(50));

    insert into #tmp (val1)
    exec SomeReallyComplexProc;

    update #tmp set val1 += ' world';

    select val1 from #tmp;

    drop table if exists #tmp;
end
go

For your unit test, all you want to do is run the ComplexBusinessCalc stored procedure and capture its output into a temporary table for validation.

Hint: if you have not used tSQLt before, here is a good place to start.

Your tSQLt unit test could look something like this. In the test below, we are simply checking that our ComplexBusinessCalc stored procedure returns at least one row of data.

exec tSQLt.NewTestClass 'demo';
go

create or alter procedure demo.[test that ComplexBusinessCalc returns data]
as
begin
    create table #test (val1 varchar(50));

    insert into #test (val1)
    exec ComplexBusinessCalc;

    if not exists (select * from #test)
    begin
        exec tSQLt.Fail 'No data returned';
    end
    
    drop table if exists #test;
end
go

But when you run your unit tests, you get stuck on the annoying “An INSERT EXEC statement cannot be nested” problem.

exec tSQLt.RunAll;

Result

[demo].[test that ComplexBusinessCalc returns data] failed: (Error) Message: An INSERT EXEC statement cannot be nested. | Procedure: ComplexBusinessCalc (6) | Severity, State: 16, 1 | Number: 8164

The solution is simple. All you need to do is change one line in your unit test. Instead of executing the ComplexBusinessCalc stored procedure within the current transaction’s context, you instead pass it as a query to a CLR to execute on it a new connection not enlisted in the same transaction context as the tSQLt test. See the «exec Query» line which replaced «exec ComplexBusinessCalc». The «Query» object looks like a stored procedure, and it is, but underneath it calls a CLR.

create or alter procedure demo.[test that ComplexBusinessCalc returns data]
as
begin
    create table #test (val1 varchar(50))

    insert into #test (val1)
    exec Query @server = @@servername, @query = 'exec Demo..ComplexBusinessCalc';

    if not exists (select * from #test)
    begin
        exec tSQLt.Fail 'No data returned';
    end

    drop table if exists #test;
end
go

Hint: If you need to pre-prepare some specific data for the unit test, add those commands into the @query parameter. The SQL connection made by the CLR would not be able to read any data setup via the tSQLt unit test’s transaction unless you allow uncommitted reads. Remember to rollback afterwards.

But, we first need to go create that “Query” CLR that accepts T-SQL commands and executes those against your SQL instance as an external application on a different transaction.

Here we go. Get ready to copy paste.

Create your first CLR

Step 1: Run Visual Studio and ensure that you have the “Data storage and processing” feature set installed. You can download the Visual Studio Community Edition for FREE from https://visualstudio.microsoft.com/ . All the screenshots in this article are from Visual Studio 2022.

In Visual Studio, via the Tools menu, open the “Get Tools and Features” dialog and ensure the Data storage and processing features are installed.

Картинка с сайта: www.sqlservercentral.com

Картинка с сайта: www.sqlservercentral.com

Step 2: Create a new SQL Server Database Project. You can access the Create Project dialog via the Files > New Project menu.

Картинка с сайта: www.sqlservercentral.com

Step 3: Add a “SQL CLR C# Stored Procedure” item to your database project, by right clicking on your project inside the Solution Explorer view and selecting Add > New Item from the context menu. This will open the Add New Item dialog. Select the “SQL CLR C# Stored Procedure” template.

Картинка с сайта: www.sqlservercentral.com

Картинка с сайта: www.sqlservercentral.com

Name the new item Query.cs

The default template code inside Query.cs will look like this:

Картинка с сайта: www.sqlservercentral.com

Replace all the default template code inside Query.cs with the following. Note, if you do change the SqlConnection, you must remember to keep the setting Enlist=false else the connection pooler will automatically enlist the connection in the creation thread’s current transaction context, and you’ll get the same “An INSERT EXEC statement cannot be nested” error, even when running your stored procedure via the CLR.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class Query
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void Exec(SqlString server, SqlString query)
    {
        SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder
        {
            DataSource = server.ToString(),
            IntegratedSecurity = true,
            Enlist = false
        };

        using (SqlConnection conn = new SqlConnection(sb.ToString()))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(query.ToString(), conn);
            SqlDataReader reader = cmd.ExecuteReader();
            SqlContext.Pipe.Send(reader);
        }
    }
}

Step 4: Select the “Release” solution configuration from the drop down in the top toolbar in Visual Studio.

Step 5: Make the “Output” window visible in Visual Studio via the View menu.

The Output window will pop up and look like this. After you have successfully built your database project, the name and path to your new .dll will be displayed in this window.

Картинка с сайта: www.sqlservercentral.com

Step 6: Build your database project by right clicking on it in the Solution Explorer view and selecting “Build” from the context menu.

Картинка с сайта: www.sqlservercentral.com

The Output window will show that your project was successfully built and the full name and path to your newly created CLR.

Картинка с сайта: www.sqlservercentral.com

Step 7: Import your new CLR dll into SQL

exec sp_configure 'clr enabled', 1;  
reconfigure;  
go

alter database Demo set trustworthy on; --change to your database name
go

use Demo --change to your database name
go
 --change path to dll
create assembly ExternalUtils from 'C:\Users\brink\source\repos\Database2\Database2\bin\Release\Database2.dll'
with permission_set = external_access;
go

create procedure Query
@server nvarchar(max)
, @query nvarchar(max)
as
external name ExternalUtils.Query.[Exec];
go

Step 8: Test your newly created CLR. By the way, if you haven’t noticed, the CLR uses Windows Authentication (a.k.a IntegratedSecurity). You’ll have to change the SqlConnection code in the CLR if you only have SQL Authentication in your environment.

Картинка с сайта: www.sqlservercentral.com

Done: That’s all folks. Now simply update your unit test to use the CLR to execute your stored procedure. After which your tSQLt unit test will run perfectly.

Картинка с сайта: www.sqlservercentral.com

Bonus Tip

You can use the “Generate Scripts…” task in SQL Server Management Studio (SSMS) to export your CLR as a T-SQL script. This makes deployment of the CLR to other servers very easy.

Картинка с сайта: www.sqlservercentral.com

The output script will look something like this. Simply include it as-is in your deployment patches.

Картинка с сайта: www.sqlservercentral.com

Have fun unit testing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

ALTER proc fs_read @pathtoread nvarchar(1024)
AS
SET nocount ON
DECLARE
     @Path nvarchar(1024)
    ,@Fullpath nvarchar(2048)
    ,@Id INT;
IF object_id('tempdb..#tmpfs') IS NOT NULL
    DROP TABLE #tmpfs;
CREATE TABLE #tmpfs(
     id INT IDENTITY PRIMARY KEY clustered
    ,fullpath nvarchar(2048)
    ,subdirectory nvarchar(1024)
    ,depth INT
    ,isfile bit);
INSERT #tmpfs (subdirectory, depth, isfile)
EXEC master.sys.xp_dirtree @pathtoread, 1, 1;
UPDATE #tmpfs 
SET fullpath = @pathtoread;
while EXISTS (SELECT id FROM #tmpfs WHERE isfile = 0)
BEGIN
    SELECT top (1)
         @Id = id
        ,@Fullpath = fullpath
        ,@Path = @pathtoread + '\' + subdirectory
    from #tmpfs 
    where isfile = 0;
    if @Fullpath = @Path
        begin
        insert #tmpfs (subdirectory, depth, isfile)
            exec master.sys.xp_dirtree @Path, 1, 1;
        update #tmpfs
            set fullpath = @Path
            where fullpath IS NULL;
        delete from #tmpfs where id = @Id;
        end
    else
        begin
        set @pathtoread = @Fullpath;
        select top (1)
             @Id = id
            ,@Fullpath = fullpath
            ,@Path = @pathtoread + '\' + subdirectory
            from #tmpfs 
            where isfile = 0;
        insert #tmpfs (subdirectory, depth, isfile)
            exec master.sys.xp_dirtree @Path, 1, 1;
        update #tmpfs
            set fullpath = @Path
            where fullpath IS NULL;
        delete from #tmpfs where id = @Id;
        end
end
--insert #t
select id, replace(fullpath, '\\', '\') as fullpath, subdirectory as filename
from #tmpfs
order by fullpath, subdirectory;
if object_id('tempdb..#tmpfs') is not null
    drop table #tmpfs;
return

1
2
3
4
5
6
7
8

CREATE TABLE #t(id INT, fullpath nvarchar(2048), subdirectory nvarchar(1024));
 
INSERT #t -- --
EXEC fs_read 'd:\folder';
 
SELECT * FROM #t
 
DROP TABLE #t

1
2

INSERT #tmpfs (subdirectory, depth, isfile)
EXEC master.sys.xp_dirtree @pathtoread, 1, 1;

1
2

INSERT #t -- --
EXEC fs_read 'd:\folder';

Наверх

An INSERT EXEC statement cannot be nested is an error that comes up due to an SQL Server restriction that talks about the same concept. At this moment, understanding why this problem is happening is equally important as finding its solution to not only stumble upon the best procedure but also be able to avoid this annoying restriction in the future.

Картинка с сайта: www.positioniseverything.net

Thus, this post talks about the cause of the stated error in a bit more detail and presents you with premium solutions to fix the error immediately. So, don’t waste an extra second, and continue reading to see what this article has for you.

An Insert Exec Statement Cannot Be Nested: Why Does It Occur?

An INSERT EXEC statement cannot be nested error can occur due to an SQL Server restriction that stops the usage of the nested INSERT EXEC statement. It says that you can’t use a stored procedure with an INSERT EXEC statement inside it in an INSERT EXEC statement.

– An SQL Server Restriction Not Allowing Nested INSERT-EXEC Statements

The SQL Server poses a restriction over the usage of the nested INSERT-EXEC statements. You can’t use a stored procedure in the INSERT-EXEC statement if the stored procedure uses an INSERT-EXEC statement inside it. If you do, you’ll get an insert exec statement cannot be nested server error message.

Картинка с сайта: www.positioniseverything.net

Moreover, it is also possible that your targeted stored procedure doesn’t contain the INSERT-EXEC statement, but a stored procedure called inside it contains the same statement. In such a case, the sp_replmonitorsubscriptionpendingcmds an insert exec statement cannot be nested will pop up.

In short, no matter how deeply embedded the given statement is inside a stored procedure, the stored procedure will be restricted from being used inside an INSERT-EXEC statement.

An Insert Exec Statement Cannot Be Nested: How To Resolve It?

You can resolve an INSERT EXEC statement cannot be nested error by avoiding the extra usage of the said statement, using the output parameter or parameters, leveraging the table-valued functions, or using the CLR as the last option. In short, using an INSERT EXEC alternative will save you.

– Use the OUTPUT Parameter/Parameters

You can use the OUTPUT parameter or parameters as a nested insert exec alternative to fix the given error on your system. But there is a limitation to apply this solution. You can go with it only if the resulting data is a single row instead of a big table.

Imagine that you want a stored procedure that inserts a single row in the table, and returns the ID for the same row. Moreover, you want to call the same stored procedure from another stored procedure. In this scenario, the OUTPUT parameter will prove to be of great benefit. Plus, you won’t need to work with the nested INSERT EXEC SQL Server statement and stumble upon the mentioned error.

The code that aligns perfectly with the given situation has been provided below:

Now, you can call the insert_teacher stored procedure from another stored procedure without much by following the line of code depicted below:

EXEC insert_teacher @name, @subject, @teacher_id OUTPUT

Note that it’s not necessary to restrict the number of output parameters to one. You can make your stored procedure return as many output parameters as you want. But remember that the number of resulting rows should not exceed one.

– Don’t Use Insert-exec Statement Unnecessarily

It is recommended to avoid the unnecessary usage of the INSERT-EXEC statement in your stored procedures to resolve the error provided above. It is because this way, the chances of the occurrence of the nested INSERT-EXEC will be reduced, keeping the SQL Server satisfied.

For example, it is possible for you to perform your desired task without using the INSERT-EXEC statement. In that case, it would be better to skip INSERT-EXEC because you never know when you’ll need to use your stored procedure in the INSERT-EXEC statement. As nesting the said statement isn’t allowed, you’ll have to go back to your code, and change it to replace the INSERT-EXEC statement with one of its alternatives, or remove it entirely to proceed further.

– Make Use of the Table-valued Functions

Although not all of your stored procedures can be rewritten as table-valued functions, the ones that can be rewritten will help you escape from the error discussed in this post. Consequently, you’ll be able to reuse the result set from a stored procedure successfully.

Картинка с сайта: www.positioniseverything.net

Check out the following inline function to understand the concept better:

Here is how you can use it:

SELECT * FROM SalesByCustomer(‘6380’)

Please feel free to filter the data with the WHERE clause or use it in a larger query that includes other tables. It means that you can use the given function just the way you would use a table or a view.

Moreover, it would be safe to say that an inline function is a parameterized view. It is because the query optimizer expands the function just like a macro, and provides the plan like you have provided an expanded query. Therefore, you’ll find zero performance cost for wrapping a SELECT * from EXEC statement into an inline function.

Based on the above facts, if you want to reuse a stored procedure executing a single SELECT statement, then rewriting it into a user-defined function should be your first choice. Another thing that you can try is to move the SELECT statement into an inline function and rewrite the existing stored procedure as a wrapper on the function.

– Choose Working With the CLR As the Last Option

Although working with the CLR can be complicated as compared to the INSERT-EXEC statement, you should go for it as the last option to make the error go away. It means that if none of the other options seem to work, then using the CLR will be a great workaround.

Furthermore, you can go through the following examples to learn about the situations where CLR serves as a life-saver. Say that you are calling a stored procedure that returns various differently-structured result sets. Here, the INSERT-EXEC statement will fail to serve you, and you might like to choose CLR. Note that many system procedures in SQL Server come under the stated category.

Next, imagine that you want to call a stored procedure that can’t be called within an active transaction. In this situation, the CLR will be a better option than INSERT-EXEC, helping you perform your required operation. Besides this, the CLR will help you avoid the trouble caused by calling a stored procedure that already uses an INSERT-EXEC.

FAQ

– What Does the Insert Exec Failed Error Mean?

The insert exec failed because the stored procedure altered the schema of the target table. error means that the name of your targeted temporary table and the one created inside your stored procedure is the same. You can modify the temp table name to fix the error.

– What Does the Invalid Use of ‘Insert Exec’ Within a Function Mean?

The invalid use of a side-effecting operator ‘insert exec’ within a function. means that you can’t use a function for inserting data into a base table. However, the data insertion operation can be performed within the stored procedures by using the insert exec statement.

Conclusion

Finally, you have clearly understood the cause of an insert exec statement cannot be nested SQL Server 2014 error and got your how to use results from one stored procedure in another query resolved. Now, you can deal with the said error more confidently. Please spend a few more minutes for the below listicle to end this post.

• You should avoid the unnecessary usage of the INSERT EXEC statement in your stored procedures and choose its alternatives more often.
• The OUTPUT parameters technique will serve the best when the result set consists of a single row.
• Implementing the table-valued functions wherever possible can make your code highly efficient.
• Working with CLR is not always a bad option, and you can go with it if the INSERT EXEC is constantly failing at fulfilling your task.

Now, if you are still thinking about disabling the SQL restriction, then know that it’s not possible.

Всем известно ограничение Transact-SQL, состоящее в следующем. Есть хранимая процедура, которая возвращает таблицу. Если требуется, чтобы эта таблица участвовала в каком-либо запросе, то приходится сохранять результаты работы процедуры во временной таблице с помощью инструкции «insert into <Table Name> exec <ProcName>» Это неудобно, требуется больше кода.
К тому же инструкция «insert into <Table Name> exec <ProcName>» имеет ограничение. Многие разработчики сталкивались с такой проблемой: в коде процедуры <ProcName> уже имеется инструкция «insert into … exec …». Тогда попытка сохранить результаты <ProcName> приводит к ошибке. В сообщении говорится, что конструкция «insert into … exec …» не может быть вложенной. Можно пытаться обойти эту проблему, используя функцию openquery, которая использует в качестве связанного сервера линк, который смотрит на исходный сервер. Однако такой метод не всегда сработает: функция может завершиться с ошибкой, если в процедуре используются временные таблицы, к тому же openquery не поддерживает параметры.

Один из подходов к решению проблемы состоит в следующем. Напишем табличную функцию, которая получает в качестве параметра текстовое sql-выражение и возвращает набор записей, который возщвращает sql-код. Как известно функции имеют массу ограничений по сравнению с хранимыми процедурами. Однако в них можно применять расширенные хранимые процедуры sp_OA…

План функции таков. Через sp_OACreate мы создадим ADO-соединение и ADO-команду. Для команды инициализируем свойство CommandText нашим sql-кодом. Далее применим метод Execute, инициализировав ADO-набор записей. После этого пройдемся в цикле по набору записей, наполнив выходную таблицу.

Предварительно создадим табличную функцию, которая будет логировать возможные ошибки OLE:
if sessionproperty ( N’quoted_identifier’ ) = 0

    set quoted_identifier on
go

if sessionproperty ( N’ansi_nulls’ ) = 0

    set ansi_nulls on
go

if object_id ( N’dbo.ProcessOleError’, N’TF’ ) is null

    exec ( N’create function dbo.ProcessOleError () returns @tab table ( i int ) as begin return end’ )
go

alter function dbo.ProcessOleError ( @step varchar ( 100 ), @obj int )

    returns @tab table ( src varchar ( 300 ), descr varchar ( 300 ) )
as

begin
    declare @src varchar ( 300 ), @desc varchar ( 300 )

    exec sp_OAGetErrorInfo @obj, @src out, @desc out


    insert into @tab ( src, descr )

        values ( N’статус: ‘ + @step + N’; источник: ‘ + isnull ( @src, ‘неизвестно’ ), N’описание: ‘ + isnull ( @desc, ‘неизвестно’ ) )

    return
end

go
Ниже код табличной функции dbo.GetQueryResult:
if sessionproperty ( N’quoted_identifier’ ) = 0

    set quoted_identifier on
go

if sessionproperty ( N’ansi_nulls’ ) = 0

    set ansi_nulls on
go

if object_id ( N’dbo.GetQueryResult’, N’TF’ ) is null

    exec ( N’create function dbo.GetQueryResult () returns @tab table ( i int ) as begin return end’ )
go

alter function dbo.GetQueryResult ( @txt nvarchar ( max ) )

    returns @tab table

    (

        col0 varchar ( 300 ),

        col1 varchar ( 300 )

    )
as

begin    declare @res int, @conn int, @cmd int, @prop int, @propInst int, @propSearch int, @propAsync int, @ADOrs int

    exec @res = sp_OACreate ‘ADODB.Connection’, @conn out

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’создание соединения’, @conn )

        goto GarbageCollector

    end


    exec @res = sp_OASETProperty @conn, ‘ConnectionString’, ‘Provider=SQLNCLI11; Server=EDYNAK\SHADOW; Database=master; Trusted_Connection=yes;’

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’строка подключения’, @conn )

        goto GarbageCollector

    end



    exec @res = sp_OAMethod @conn, ‘Open’

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’открытие соединения’, @conn )

        goto GarbageCollector

    end



    exec @res = sp_OACreate ‘ADODB.Command’, @cmd out

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’создание команды’, @cmd )

        goto GarbageCollector

    end



    exec @res = sp_OASETProperty @cmd, ‘ActiveConnection’, @conn

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’соединение для команды’, @cmd )

        goto GarbageCollector

    end



    exec @res = sp_OASETProperty @cmd, ‘CommandText’, @txt

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’текст команды’, @cmd )

        goto GarbageCollector

    end



    exec @res = sp_OACreate ‘ADODB.Recordset’, @ADOrs out

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’набор записей’, @ADOrs )

        goto GarbageCollector

    end



    exec @res = sp_OAMethod @cmd, ‘Execute’, @ADOrs out

    if @res <> 0

    begin

        insert into @tab ( col0, col1 )

            select src, descr

            from dbo.ProcessOleError ( N’выполнение запроса’, @cmd )

        goto GarbageCollector

    end

    declare @h int = 1, @data varchar ( 300 ), @obj varchar ( 300 )

    declare @src varchar ( 300 ), @desc varchar ( 300 )

    declare @eof bit = 0


    while @eof <> 1

    begin

        exec @res = sp_OAGetProperty @ADOrs, ‘Fields(0).Value’, @data out

        if @res <> 0

        begin

            insert into @tab ( col0, col1 )

                select src, descr

                from dbo.ProcessOleError ( N’первое поле’, @ADOrs )

            goto GarbageCollector

        end



        exec @res = sp_OAGetProperty @ADOrs, ‘Fields(1).Value’, @obj out

        if @res <> 0

        begin

            insert into @tab ( col0, col1 )

                select src, descr

                from dbo.ProcessOleError ( N’второе поле’, @ADOrs )

            goto GarbageCollector

        end


        insert into @tab ( col0, col1 )

            values ( @data, @obj )

        exec @res = sp_OAMethod @ADOrs, ‘MoveNext’, null

        if @res <> 0

        begin

            insert into @tab ( col0, col1 )

                select src, descr

                from dbo.ProcessOleError ( N’переход на строку’, @ADOrs )

            goto GarbageCollector

        end


        exec @res = sp_OAGetProperty @ADOrs, ‘EOF’, @eof out

        if @res <> 0

        begin

            insert into @tab ( col0, col1 )

                select src, descr

                from dbo.ProcessOleError ( N’признак окончания набора’, @ADOrs )

            goto GarbageCollector

        end

    end
GarbageCollector:

    exec sp_OADestroy @conn

    exec sp_OADestroy @cmd

    exec sp_OADestroy @prop

    exec sp_OADestroy @propInst

    exec sp_OADestroy @propSearch

    exec sp_OADestroy @propAsync

    exec sp_OADestroy @ADOrs


    return
end

go
Испытаем функцию. Например, для того чтобы получить список всех объектов всех баз данных одним запросом, можно использовать такой код:
select *

from dbo.GetQueryResult
(    ‘
    declare @sql nvarchar ( max ) = N»», @name nvarchar ( max )

    declare cur cursor local static forward_only for

        select name

        from sys.databases

        where database_id > 4

    open cur

    fetch next from cur into @name

    while @@fetch_status = 0

    begin

        set @sql = @sql + case when @sql = N»» then N»» else N» union all » end + N»

        select »»» + @name + »»», name collate Cyrillic_General_CI_AI

        from » + quotename ( @name ) + ».sys.tables

        »

        fetch next from cur into @name

    end

    close cur

    deallocate cur

    exec sp_executesql @sql

‘)
Теперь можно использовать этот запрос для соединения с другими таблицами, вставками в таблицы. Можно передать в паметр функции и sql-выражение, содержащее вызов любой процедуры с параметрами, возвращающей набор записей. В коде функции я для примера сделал возврат только двух столбцов. При необходимости можно добавить большее число столбцов. Для работы функции на серверном компьютере должна быть установлена библиотека Microsoft ActiveX Data Objects.