As title says, I have a stored procedure that needs to delete data out of tables on server x, the action of which needs to update tables on another server y.
Can I reference what server to use in a stored procedure for given sets of code?
No, however you can execute a stored procedure on a linked server, run a script using xp_cmdshell that can connect to another server and run some code or write a CLR procedure that makes a connection and does something on another server.
Thanks..jotorre..it looks as if creating a linked server is only applicable to me, correct? So, I create the linked server per Management Studio and so it appears that only when I run the stored procedure from my management studio instance can I reference that linked server? I say this because i wish others to be able to run the the stored procedure I have created, accessing the other server; but it looks like if the above is true, then they would also have to create a linked server instance via their Management Studio as well?
You can have your linked server connect using a login and password with permissions on the remote server. In that way anyone can run it. You can map only certain logins to a privileged login on the remote server so a limited list of logins can run a remote procedure.