Executing a stored procedure inside another stored procedure from a linked server

Question

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 < 50
 EXECUTE @@OK = Bank.usp_WriteEvent @qty, @code, @@today, 500
 IF @@OK <> 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

• Scott Hodgin - Retired: I'm wondering if you could take advantage of sys.dm_exec_describe_first_result_set (Transact-SQL) as shown in this answer to allow you to create a temp table that represents the schema being returned from SP1
• HandyD: Have you tried INSERT INTO Bank EXEC [SRVNEW\SQLDESA].[Bank_Database].Bank.usp_GetTDcodes 1,5 to see if that works?
• qhart: @HandyD I thought that INSERT INTO required a table already created, am I correct? If that's the case, I need to create the table with the results of the 1st SP
• HandyD: Given the stored procedure code doesn't appear to be dynamically building a result set, wouldn't the schema of the table required be known ahead of time so you could simply create the table ahead of time?

Answer 1

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.