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?
Thanks for any help!
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.
1 Like
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?
Thanks!
Thanks Btrim, I understood some of that article, will have to keep researching..
So people are using ssms to interact with the data? Why? What is the use case here?
Once you create a linked server on a remote server it is accessible to all that have permission to it
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.