May 18, 2014 at 2:01 pm
For example... If I create the following stored procedure:
create procedure test
as
select * from sys.databases waitfor delay '00:00:30'
and then run this:
declare @rc int
exec @rc=sp_exec_init 8
if (@rc=0) begin
exec sp_exec 'exec test'
exec sp_exec 'exec test'
exec sp_exec 'exec test'
end
exec sp_exec_end
It just runs and doesn't do anything. No error, no activity. Can you not call another stored procedure as the sql statement?
May 20, 2014 at 2:29 pm
The select won't return the results to your current SSMS window. The system will run the sql statements in their seperate SPIDs. So you'll see nothing.
The typical scenarios is like this -- You can create a table (or a global temp table ##tmp), and make the stored procedure to insert their partial results to the table. After all partial works have been completed, use select * from the table to get the combined results.
jxhopper (5/18/2014)
For example... If I create the following stored procedure:create procedure test
as
select * from sys.databases waitfor delay '00:00:30'
and then run this:
declare @rc int
exec @rc=sp_exec_init 8
if (@rc=0) begin
exec sp_exec 'exec test'
exec sp_exec 'exec test'
exec sp_exec 'exec test'
end
exec sp_exec_end
It just runs and doesn't do anything. No error, no activity. Can you not call another stored procedure as the sql statement?
February 14, 2017 at 10:30 pm
Granted this is a few years old now but I'm experiencing the same issue as the former commenter dbatech99. I don't suppose you worked it out?
I can run the example delay based tests without issue. However if I'm calling a stored proc the threads never come back and often do not log anything. These SP's are all in my own DB which I don't believe presents an issue as you appear to be substituting the current DB in your code. I note that in some cases I do get a log record and the database in use is [pmaster] and not my own. I've tried forcing it, I even hard coded it into the p_exec function and rebuilt it, but to no avail.
The SP's are part of an ETL series. The one's I'm testing with should only take a few seconds and result in data appearing in one of my tables. They work manually but don't do anything when combined.
Given the age of the procedure now perhaps there are some incompatibilities with SQL Server 2016 - but I doubt it.
daz
March 23, 2017 at 12:28 pm
Is there any way to execute this within a transaction? For example, I have the following (pseudo) scenario.
BEGIN TRANSACTION
DO SOMETHING
DO SOMETHING ELSE
WHILE(CONDITION)
BEGIN
SET @sql = <New Parallel Query>
exec sp_exec @sql
END
DO ANOTHER THING
IF (ANYERROR) ROLLBACK TRANSACTION
COMMIT TRANSACTION
The problem is that the queue doesn't seem to actually start the worker threads while inside of a transaction. Instead, they get sent to the queue and just sit there indefinitely. From my research it appears this is because the message queue doesn't actually commit the messages until the outermost transaction is committed? Any ideas of how I could get this to work?
November 2, 2023 at 11:14 pm
This was removed by the editor as SPAM
November 2, 2023 at 11:16 pm
This was removed by the editor as SPAM
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply