Execute a Stored Proc as a background process

Question

Execute a Stored Proc as a background process

robertcaustin

Mr or Mrs. 500

Points: 551
More actions
May 12, 2008 at 4:04 am

#190489

I want to initiate a Procedure and then return-continue execution. The reasoning behind this is to use SQL Express but because Express does not have an Agent I need to initiate a recurring task somehow.
I have an external app feeding data in SQL so I am hoping there is a way, as part of the call by that external app, that I can initiate a 'task'.
I am sure this is a fairly common need with SQL Express - ideas will be great.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

Can't you simply open another connection to the database and fire the procedure asynchronously? That way it won't be part of the main application's execution path.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

philliphiggins SSC Veteran Points: 255 More actions · Answer 2

If you are programming using a multithreaded environment like java or .net you can just run the long running query from a new thread. If you're in a single thread environment you have a lot less options.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 3

robertcaustin (5/12/2008)
I want to initiate a Procedure and then return-continue execution. The reasoning behind this is to use SQL Express but because Express does not have an Agent I need to initiate a recurring task somehow.
I have an external app feeding data in SQL so I am hoping there is a way, as part of the call by that external app, that I can initiate a 'task'.
I am sure this is a fairly common need with SQL Express - ideas will be great.

Hey folks... does SQL Express have Osql.exe? If so, couldn't Robert create a batch file and use Windows Task Scheduler to execute it on a regularly scheduled basis?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Yeah, OSQL will work fine. It's still back to what I said. Open a new connection, either by spawning a thread or using another executable, one you built or one you bought, and run the query seperate from the other processes. It shouldn't be a big deal.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

philliphiggins SSC Veteran Points: 255 More actions · Answer 5

If its just a matter of running a batch process regularly an executable or batch file started from scheduled tasks would work just fine.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Another thing you can do is create a startup proc, and use the WaitFor command and a While loop to make it execute something over and over again.

While 1 = 1

begin

exec dbo.RecurringCommandProc

waitfor delay '00:05'

end

That will make it run every 5 minutes. (Well, after it finishes, there will be a 5 minute delay till it runs again. Not the same as starting it every 5 minutes.)

sp_procoption will set it to run automatically at startup. Has to be created in master database and be owned by sa account.

I just tested this in an instance of Express and it seems to have worked.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

robertcaustin Mr or Mrs. 500 Points: 551 More actions · Answer 7

Thanks to all the replies but I think my logic and reasoning is the opposite to normal programming. I live inside SQL and only write or use outside programs when I can't do it in SQL. My logic is that locks and DB activity is easiest managed from within the SQL structures. Having an outside program pushing instructions in means a lot of overhead needed to make sure that external process does not mess with the core activity.

So the idea is to run the normal insert process (from an external app) and to return to the external app. But if the insert required some further processing then I want to be able to leave this running as an internally-originated thread/process without holding up the external app.

Hence the thought that it may be possible to call a Stored Procedure from another proc with a flag to run in the background.

I have an existing C Service running on the server that calls for any data that needs to be pushed out to the client. I wanted to use this as a 'timer' to initiate any pending processing BUT I don't want to stall this service while it waits for an unrelated process to complete.

On my Normal (as opposed to SQL Express) servers I have a SQL Agent Task that starts when SQL Starts and is set to restart every minute but because it never ends, it only restarts if the {code} fails

While 1=1

begin

{code}

WAITFOR()

end

Express does not have Agent so there is nothing that can initiate this process that does not 'hang' whatever was used to initiate it.

GSquared SSC Guru Points: 260824 More actions · Answer 8

Did you look at the startup proc idea I wrote up? That doesn't require any outside process, nor SQL Server Agent.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 9

robertcaustin (5/15/2008)
Thanks to all the replies but I think my logic and reasoning is the opposite to normal programming. I live inside SQL and only write or use outside programs when I can't do it in SQL. My logic is that locks and DB activity is easiest managed from within the SQL structures. Having an outside program pushing instructions in means a lot of overhead needed to make sure that external process does not mess with the core activity.

You're a man after my own heart, Robert. Sorry I can't help on the express side of the house.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robertcaustin Mr or Mrs. 500 Points: 551 More actions · Answer 10

Thanks GSsquared - the StartUp script is a fine idea but has the short-coming. Without Agent to keep restarting it if anything fails there is nothing in Express that can recursively check it is running.

I use exactly that concept on my normal SQL servers. I set Agent to run the Procedure every 5 minutes. Agent Starts the Procedure and because it keeps running Agent does not fire a new instance. If it fails for whatever reason then Agent restarts it on the 5-minute timer.

With Express I can get it started with a Windows Job. Problem is if I use a Windows Task to 'restart' it and it is still running then a new instance is created (which is definately not the desired thing to happen).

The only way to get that to work is if there is a call that can check if a Procedure is currently running?

So that's then the question? Is there a SQL call to check if a SQL Procedure is already runnimg?

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 11

So have the job update a single row in a log table and make the code check it at the beginning... for example, if the last entry was more than 10 minutes ago, you know the job stopped and it needs to be restarted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 12

"run the normal insert process (from an external app) and to return to the external app. But if the insert required some further processing then I want to be able to leave this running as an internally-originated thread/process without holding up the external app."

Have you looked as Service Broker?

SQL = Scarcely Qualifies as a Language

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Service Broker only works on SQL Express when it is connected to another SQL Server with a full license to Service Broker.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 14

How about using something like:

DECLARE @sessionid smallint;

SELECT @sessionid = session_id

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a

WHERE object_name(a.objectid) = 'MyTestProcedure';

IF (@sessionid IS NOT NULL)

BEGIN;

RAISERROR('Process is already running - exiting...', -1, -1) WITH NOWAIT;

RETURN;

END;

Put the above in your procedure. If a session id is returned, exit the procedure - else, start running your process.

There are other ways to implement it - but that should get you started.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs