0

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

  • 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 – Scott Hodgin Oct 16 at 20:55
  • Have you tried INSERT INTO Bank EXEC [SRVNEW\SQLDESA].[Bank_Database].Bank.usp_GetTDcodes 1,5 to see if that works? – HandyD Oct 17 at 5:45
  • @ScottHodgin I'll take a look at that, thanks – qhart Oct 17 at 12:11
  • @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 – qhart Oct 17 at 12:13
  • 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? – HandyD Oct 17 at 22:38
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.