It does not keep the data in the table being filled from a stored procedure
-
I would like to know why when consulting a table that is filled with a stored procedure and the last one is called with an openrowset, it does not return data.
Example
CREATE TABLE dbo.Test (Id INT, Nombre VARCHAR(10))
GO
CREATE PROCEDURE ProcedureTest1
AS
BEGIN
INSERT INTO dbo.Test VALUES(1, 'Deblin')
SELECT * FROM dbo.Test
END
GO
CREATE PROCEDURE ProcedureTest2
AS
BEGIN
EXEC ProcedureTest1 WITH RESULT SETS ((Id INT, Nombre VARCHAR(MAX)))
END
GO
The result when running the openrowset is perfect
However, I need the table that was filled with the first stored procedure to keep the data, someone knows the reason why this happens
Thank you very much in advance
- This topic was modified 3 years, 2 months ago by AlexRodriguez1990.
-
Is it possible that you're running the SELECT statement from a different database than the one where you're executing the query through OPENROWSET? I just ran the procedures and everything worked the first time. I didn't have to adjust your code in any way. So, makes me think that maybe the OPENROWSET is going somewhere other than where you're connected when you run the SELECT *.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
The query with the openrowset and in fact the result when doing the select with this sentence is perfect.
However, the table dbo.Test that fills up in stored procedure ProcedureTest1, when doing select again is empty.
Now if I execute the stored procedure ProcedureTest2, which in turn executes the stored procedure ProcedureTest1 that inserts data into the table without the openrowset statement, when consulting said table if it has data.
Which in fact is one more thing that I need the table to be full after running the stored procedures with openrowset
-
I don't know. I tested your code and it worked. I'm not sure what's going wrong on your end, but that's my best guess.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
That is, this table in your sql server appears with data
If so, can you tell me if you have any special settings.
-
I honestly don't know what to do so that the dbo.test table has the data, I really don't understand why this happens, if I'm doing something wrong or if I'm missing something, I appreciate your help.
-
Again, best guess, the openquery is running somewhere other than where you're running it locally. I've tested your code. It's working. I don't have any magic to add. It worked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply