Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2008-08-25 : 10:44:18
|
| Hi,is it possible to fire a stored procedure from anther stored procedure asynchronously somehow? Let's say I have this proc that does a few minor things and then I have this other proc that takes anywhere from 1 to 10 minutes to finish. Is it possible to execute the first one, fire the second and then end scope without waiting for the last one to finish? The only way I can think of is to create a job that is scheduled to run once but I find this kind of tedious...- Lumbago |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-25 : 10:49:53
|
| Didnt understand why you want first sp to finish before second is finished? |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-25 : 10:51:37
|
| sounds like a job for service broker._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-08-25 : 11:01:50
|
| visakh: because first sp takes nanoseconds and the second takes minutes and the user (this is a website) shouldn't have to wait until the second one is finished also to regain his fucusspirit: hm, never really looked in to what the service bruker does/can do...I'll have a look, thanks :)- Lumbago |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-08-25 : 11:12:44
|
| awesome! I'll read it right away... :)- Lumbago |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-25 : 11:15:15
|
Also sound like starting a few jobs  EXEC msdb..sp_start_job @job_name = 'MyJobNameHere' -- Execute job asynchronously
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-08-25 : 11:20:05
|
| Well...as far as I know Peso a job can't take parameters which means I have to create the job on the fly also, and having done this in the past I'd really like to avoid it to be honest. But from what I have read so far the service broker seems like a good option. I have to read more though and test it...- Lumbago |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-08-25 : 11:38:32
|
| Maybe I should have included more info in the OP. What I basically want to acomplish are two things:1. Fire a long-running-query (procedure) with parameters from within another procedure. This process is initiated from a website backend and focus should be regained to the website once the first procedure is finished. 2. Replicate small chunks of data between two db-servers in different countries across a VPN link. If the link is down the user shouldn't have to wait for a timeout period to regain focus since the data transfer is non critical. I have considered transactional replication also for this task.Sorry for not providing this in the OP...- Lumbago |
|
|
|
|
|
|
Asynchronous Operations