September 23, 2020 at 7:58 pm
I have a linked server and need to change only the @svrproduct and the @datasrc. I cannot figure out how to do this. I do not want to drop the object and recreate.
@server = N'EAUTO', @srvproduct=N'1.2.3.4', @provider=N'SQLNCLI', @datasrc=N'1.2.3.4'
Thanks,
Terry
A great day starts with a great attitude
September 24, 2020 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 24, 2020 at 10:22 pm
There is no supported procedure to change those - dropping and recreating with the updated information is the supported method. I would guess you could hack the system table to update the information sys.servers but you'd have to search for how to do that as it's not something I would post. And it's really, really, really not recommended. But the only reason I can think of for not wanting to drop and recreate would be if you didn't have the password for a login being used and creating the a new login and password or resetting the existing password on the other data source may not be an option. If that's the case, there are a few powershell scripts on the internet to get linked server passwords or script them out with passwords. I haven't tried it on 2016 but it did work on 2014 and earlier versions. I would test that method before trying to hack system tables. All the scripts I've seen to get the linked server passwords use the same method from this post:
Decrypting MSSQL Database Link Server Passwords
If you do a search on something like "linked server password recovery", you can find some of the ways people have used that Powershell script.
Sue
September 29, 2020 at 4:24 pm
Hello,
You could try some of the ideas mentioned here:
https://stackoverflow.com/questions/11866079/changing-properties-of-a-linked-server-in-sql-server
HTH.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply