Executing a stored procedure inside another stored procedure from a linked server I'm having a problem and I don't know how to solve it, I have searched the web and found good advice but I can't work it out.

This is the problem: I have a SQL Server instance running on my PC, and I linked one of the main servers SRVOLD\SQLDESA to it. I want to execute main server's stored procedures from my PC's SQL Server instance and insert the results into a new table. I found the perfect way to do it using the following:

SELECT *   
INTO Bank   
FROM OPENQUERY([SRVOLD\SQLDESA],  
               'EXEC Bank_Database.Bank.usp_GetTDcodes 1, 5')   
GO  


There is important information about this server, it's SQL Server version is 2008. Keep this in mind for later.

Ok so I managed to executed this Stored Procedure but I found out something, turns out that inside this Stored Procedure there's an execution of another stored procedure, check this out:

1st stored procedure:

CREATE PROCEDURE Bank.usp_GetTDcodes 
    (@code TINYINT = NULL, @qty TINYINT = NULL)
WITH ENCRYPTION 
AS
    DECLARE @@msg VARCHAR(100)
    DECLARE @@OK INT
    DECLARE @@today CHAR(30)

    SELECT @@today = CONVERT(VARCHAR(30), GETDATE(), 112) + ' ' +
                     CONVERT(VARCHAR(30), GETDATE(), 8)

    SELECT bnk_code, bnk_descr
    FROM CODBNK
    WHERE bnk_code &lt; 50

    EXECUTE @@OK = Bank.usp_WriteEvent  @qty, @code, @@today, 500

    IF @@OK &lt;&gt; 0
        RETURN @@OK

RETURN 0
GO


Now let's look inside the 2nd stored procedure:

CREATE PROCEDURE Bank.usp_WriteEvent
    (@code TINYINT = NULL, 
     @qty TINYINT = NULL,   
     @date DATETIME = NULL,     
     @number SMALLINT = NULL, 
     @ideve INT = 0 OUTPUT)
WITH ENCRYPTION
AS
    DECLARE @@sdate VARCHAR(30)
    DECLARE @@ret SMALLINT

    INSERT INTO Event (eve_code, eve_qty, eve_date, eve_number)
    VALUES (@code, @qty, @date, @number)

    SET @@ret = @@error

    IF @@ret = 0
    BEGIN
        SELECT @ideve = @@IDENTITY

        SELECT @@sdate = CONVERT(VARCHAR(30), @date, 112) +  ' ' +
                         VARCHAR(30), @date, 8) 
    END
    ELSE 
        RETURN @@ret
GO


When I executed the 1st stored procedure, I was able to insert it's result into a new table, but I was hoping to find a new row inserted in the table Event, because that is the expected result when executing 2nd stored procedure.

So I started to search online and managed to achieve this by doing the following:

SELECT *   
INTO Bank   
FROM OPENQUERY([SRVTEST\SQLDESA],  
               'SET FMTONLY OFF;SET NOCOUNT ON;EXEC Bank_Database.Bank.usp_GetTDcodes 1, 5')   
GO  


So, the SET FMTONLY OFF;SET NOCOUNT ON worked and I was happy. But something happened...

I needed to execute the same stored procedure, but this time adding a new linked server SRVNEW\SQLDESA. This server's version is 2012, so the new solution didn't work. I kept trying and trying different ways, there's just one way to make it work and is the following:

EXEC [SRVNEW\SQLDESA].[Bank_Database].Bank.usp_GetTDcodes 1,5


But it doesn't work for me because I need the 1st stored procedure result into a new table. And I don't know its schema that's why SELECT INTO works best for me. I don't know what else I can do, maybe is the OPENQUERY that doesn't work? Do I need to change something else?

PD: I also tried using OPENROWSET didn't work either.

Thanks in advance, and have a nice day! Peace!

Some references: http://www.sommarskog.se/share_data.html#OPENQUERY
I used the function that @Scott Hodgin suggested and created the table with that information. Thanks man!

Note: if your Stored Procedure is using a Temporary table this won't work as specified in the page:


  The result could not be determined because the batch contains a
  temporary table and is not supported by sp_describe_first_result_set.