Can You Change A Server Connection From Within A Stored Procedure?

Transact-SQL
#1

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!

#2

Sounds like you might want to check into Linked Servers....

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017

1 Like
#3

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
#4

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!

#5

Thanks Btrim, I understood some of that article, will have to keep researching..

#6

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

#7

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.