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 |
|
jtwork
Yak Posting Veteran
82 Posts |
 Posted - 2008-04-11 : 05:40:11
|
| i want to be able to trigger a sp on another server from within an sp. I dont want to do this via a linked server as this will hold up the job while it waits for a response to say the sp has completed. Is there another way i can do this? i have a number of them and i did think of setting up a dts on the other server for each one of them and then executing the dts packages instead.any ideas anoyone? |
|
|
tomy74
Starting Member
32 Posts |
Posted - 2008-04-11 : 09:51:51
|
| Hi jtwork,In case you have sql server 2005, You can create an end point http on the second server and host your sp(s) on it, and this is similar to web method in web services development.Regards,Tarek GhazaliSQL Server MVPhttp://www.sqlmvp.com |
 |
|
|
jtwork
Yak Posting Veteran
82 Posts |
Posted - 2008-04-11 : 09:56:24
|
| no just 2000 im afraid with no immediate plane to upgrade |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-12 : 01:24:45
|
| How about create sql job for the sp on remote server, then start that job via linked server? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-12 : 01:29:16
|
| Or you can try with OPENROWSET using EXEC SPName as the query |
|
|
|
jtwork
Yak Posting Veteran
82 Posts |
Posted - 2008-04-14 : 04:33:01
|
| rmiao, that will still hold original job open while it waits for a finish response.visakh16,ive given this a try and got it working if the sp is a simple select query but im looking to exec against the msdb database to auto schedule a job. this didnt work and at first i thought it was because i was passing parameters so i hard coded in the dates and times and re ran it but i get The OLE DB provider 'SQLOLEDB' indicates that the object has no columns. so im assuming i cant do this? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-14 : 04:41:22
|
| I've never tried it myself in msdb but just came across this article with an example:-http://jon.netdork.net/2007/02/23/sql-job-monitoringAlso look at this for configuration options:-http://duartes.org/gustavo/showSource796b.html?projectName=Common&filePath=src/sql/LockDownSQLServer2005.sql&syntaxId=stp |
|
|
|
|
|
|
trigger so on another server