call remote application from SQL Server

[LlomaxX]

Hi. I am writing a stored procedure that will run through a cleanup algorithm, cleaning up records in the tables and whatnot. We have a network of servers that could be making calls against the SQL database at any time, and we don't want some of these processes to make database calls while the stored proc. is running. I can stop new processes from starting by setting a flag in a table in the database once the procedure starts, and setting the trigger that creates these processes to check the flag first. But I need to wait for any currently running tasks to finish before the stored procedure actually starts altering data. I can create an application on one of the remote servers, that should be able to tell me if any of the processes I am worried about are currently running. Which finally brings me to my question...

How can I call an application, COM object, script, whatever, that is running on a different server, from within a stored procedure on the SQL Server? By the way, the stored procedure will be scheduled through a SQL Agent.

Any ideas?

 

[mrdenny]

You really can't. T/SQL by it self can't access much out side of SQL. If you wrote a custom extended stored procedure, you could reach out with that to the remote machine via COM+.

I would write the cleanup script to check the sysprocesses table, and the locks in the system. Once they have all dropped off you should be good to go.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LlomaxX]

That may be an option...although I don't know if it will work. The objects that would be running on the other servers run a variety of processes, and I would only see the process I'm looking for when it was running...I wouldn't be able to tell if the main process was running and just hadn't gotten to the point where it would hit the SQL database yet.

I think I may just scrap scheduling this through SQL Server. If I create an object on the one of the other servers to kick it off, it can check for the other processes, as well as set a flag to keep new ones from starting....then when the coast is clear, call the stored proc remotely, which is apparently how SQL Server was designed to work in the first place...

But you told me what I needed to know...that there really isn't any way to do what I'm wanting to do, at least not in the direction I was wanting to do it....Thanks for the help!

 

[LlomaxX]

And, yes...I could do it through extended stored procs...but from what I can find out, that's not something that someone can just pick up in an hour or two, and safely run on a SQL Server.

Thanks again!

 

[mrdenny]

No, writting an extended stored proc would like a lot of time. I think your new path is a much better option based on what you have described.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com