Hi, this is a second post trying to help a friend on a slightly different subject. as shown at https://www.sqlservercentral.com/forums/topic/simple-linked-server-plumbing he is forced into splitting up some db's to multiple servers...without changing code. So on db Q (now on server x) he still wants to execute a stored proc that executes a stored proc on db A which remains on the old server R. are there ways?
Linked server I think is the only option, but personally, I'd refrain from doing something like this. My style is to keep my stored procedures contained to the system they are running on. IF the stored procedure needs DATA from a second server, I'll set up a linked server and do a SELECT or INSERT or UPDATE or DELETE across the linked server for small datasets, but I'd not have my SP on server Q call a stored procedure on server A. Instead, I'd push that logic out to the application layer. Application calls the SP on server Q, and based on those results, calls the stored procedure on server A.
The reason I don't like it is that it creates a dependency that is hard to maintain and creates potential for issues. There is no nice visibility that DB Q requires permissions to execute code across the linked server. RPC (remote procedure call) is required to be enabled on the linked server and best practice is to not use that unless it is the only option.
So, yes you CAN do it, but I would strongly encourage you to instead change the design of the application rather than kludging something together on the new server. Even if it was on the same system (both on the old server R), I'd still refrain from cross database stored procedure calls as it feels very kludgy to me and you can get into issues with execution plan generation and statistics. Heck, I'm not even a fan of cross database queries in general as they have performance considerations. Mind you the performance hit is often small, but it CAN be large. https://medium.com/@vanhecke.erwin/the-hidden-perils-of-cross-database-queries-on-sql-server-79e037d3a647
But to answer your question, yes, it is possible, but you'll need code changes and will need to set up a linked server. I would not recommend it, but you can do it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply