Asynchronous stored procedure calls

Question

Asynchronous stored procedure calls

BradleyB

Say Hey Kid

Points: 674
More actions
April 3, 2003 at 8:43 am

#105945

Can an asynchronous stored procedure call be made from another stored procedure? Currently I make a set of inserts into a table. I then roll up the information that was inserted, and pass it on to a parent table (that is handled in the procedure I want to call async.) The information is not critical, but useful to the user. It also makes access time faster.

Viewing 7 posts - 1 through 6 (of 6 total)

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

DickBaker Old Hand Points: 334 More actions · Answer 1

within MSSQL no, it is effectively synchronous [excepting parallelism so several CPUs can simultaneously work on a complex query].

There is nothing to to prevent YOU from having multiple connections to the server and [say] using .NET with a thread-literate application. This would imply lotsa round-tripping which will detract from effectiveness, unless you place the app on the db server [to eliminate network activity]. Rumour is that future release of MSSQL will allow you to write sprocs in [any of the] CLR languages - not just TSQL [which isn't always one's first choice].

You will find that DTS supports such split/join features to permit multiple activities (with synchronisation constructs based on success/fail workflow). I suggest this would be a better bet than DIY apps.

Usual caveats about async activity is that you can deadlock yourself unless v.careful. Most attempts to improve performance latterly are consequence of poor application design up-front.

- Architecture, Architecture, Architecture !

Dick

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 2

Look at this thread:

http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=10992

Jeremy

BradleyB Say Hey Kid Points: 674 More actions · Answer 3

Thanks for both of your input. I found the thread Jeremy suggested early this morning. I think what I will end up using is an insert into a separate table, then have a job run across the table and process the contents once a minute.

James-466878 Valued Member Points: 53 More actions · Answer 4

that thread now appears to live at

http://qa.sqlservercentral.com/Forums/Topic10992-65-1.aspx

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Please note: 8 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gbfunk SSC Rookie Points: 45 More actions · Answer 6

You could wrap the stored proc in a SQL job, then call the job via msdb.dbo.sp_start_job 'job name' in the first proc. This is completely asynchronous. A downside to this approach is that it becomes difficult to pass parameters to the SP while its in a job. You could possibly use some type of queue table that the first request would write to, then the job would iterate through the queue.