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
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