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
Best Answer
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: