Sql-server – Simply inserting to temp table and failing

sql serverstored-procedurest-sqltemporary-tables

Can anyone tell me why this won't work for me?

CREATE TABLE #UndistributedCmds
(
pendingcmdcount int,
estimatedprocesstime INT
)
INSERT INTO #UndistributedCmds

EXEC sp_replmonitorsubscriptionpendingcmds ...

The error:

Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 152
An INSERT EXEC statement cannot be nested.

The error seems to imply that line 152 of the stored procedure definition is the issue, but I'm having trouble understanding this. Is this happening because the stored procedure itself has multiple stored procedures within it?

Best Answer

The error is happening because the stored procedure also has a #temp table or table variable and inserts into it using insert...exec. You can see it by looking at its OBJECT_DEFINITION():

insert into @countab (pendingcmdcount)  
  exec @retcode = sys.sp_MSget_repl_commands

So basically you asking for this (pseudo-code):

INSERT INTO #outer EXEC
  (SELECT * FROM (INSERT INTO #inner EXEC ... ));

This nesting of insert...exec calls is prohibited in SQL Server, for reasons that are unknown/undocumented. The typical workaround is:

SELECT * INTO #UndistributedCmds
  FROM OPENQUERY([LoopbackLinkedServer], N'EXEC procedure...');

You'll need a linked server that references the local server machine, and it will need the attribute "data access" set to true...

Related Solutions

Sql-server – The multi-part identifier “xxx” could not be bound

Reformatting your code shows the problem. All I have changed is whitespace and added a comment:

HAVING
    (
        SELECT COUNT(*) FROM
        (
            SELECT *
            FROM dbo.ContractDailyRoomAllocation da2 -- <-- da2 defined here
            WHERE da2.ContractId = DA.ContractId
            AND da2.RoomTypeId = DA.RoomTypeId
            AND da2.Date >= @FromDate AND da2.Date < @ToDate
            AND da2.IsSold = 0
            AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)
        ) T
    ) = @StayDates
AND
    ( 
        (
            @AllowOnRequestBookings = 'False' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) >= 0

        )
    )
AND 
    (
        (
            @AllowOnRequestBookings = 'True' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) > 0
        )
    )

As can now easily be seen, da2 is an alias defined in the subquery in the first ANDd term, and so is not available in other terms.

It may be that you want to move the second and third ANDd terms here into the subquery, or it might be more complicated than that.

Sql-server – Table Variable and Database Scope

Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches.

If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select the data from it.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

select col1 from @T1; -- returns 10

go

The exec encapsulates a T-SQL batch, so in your example it is in a completely separate batch from the table declaration and the insert statement and therefore the table variable is not in scope.

Exec does not terminate a batch though, the table variable will still be in scope after the exec is run.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

select col1 from @T1; -- returns 10

go

However, the GO command signals the end of a batch, so placing a GO between the insert and select will also mean you are executing 2 separate batches. You would then also get the error

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

GO

select col1 from @T1; -- errors

GO

Best Answer

Related Solutions

Sql-server – The multi-part identifier “xxx” could not be bound

Sql-server – Table Variable and Database Scope

Related Question