Executing a stored procedure on another server

[TomR100]

Hello,
Is there a way to execute a stored procedure within another stored proedure that is located on different server?

The outside vendor gets a number of files at the end of the month and they would like two new files added to the month end process.

The trouble: We do not capture the information they request in the original database. The information is in another database on another server.

I would like to execute the stored procedures that create the new files when the user starts the month end process stored procedure.

Thank you for your help.
TomR100

 

[ClaireHCM]

You can try to use openquery.

The syntax is
openquery( linked_server_name , 'query' )

Refer Books on line "openquery"
and the following link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ad_1qzm.asp

 

[vadimg]

use a linked server query:

exec SERVER_NAME.db_name.dbo.stored_proc_name

look up in BOL:
sp_addlinkedserver
and
sp_addlinkedsrvlogin
That should explain how to register a linked server.

 

[TomR100]

Hello,
Thanks for getting back to me so quickly.

I ran your ideas by the DBA and he DOES NOT want to rely on external links so he suggested using an ALERT to trigger a job on the database where the data is stored.

Can anyone tell me what I need to do to achieve this?

Thanks again