Running a remote stored procedure
-
Is it possible to run a stored procedure that exists on on one (remote) SQL Server against databases on another (local) SQL Server?
-
Yes but that would take extensive efforts. How about you copy the sp and adapt it to your needs on the 2nd server?
-
Not that extensive, but you are going round in circles... literally
1. Can you can run a local SP with remote tables?
CREATE PROC usp_local
AS
UPDATE server.db.schema.table SET x=1
...
2. Extending idea 1: The remote sp references local tables. Set up a linked server on your remote linked server back to the calling server
Local: EXEC remoteserver.db.schema.usp_procthere
Remote:
CREATE PROC usp_procthere
AS
UPDATE callingserver.db.schema.table SET x=1
...
And now you're going round in circles...
-
... That can't be good for performance!!!
-
Number 2, probably not but I've not implementated or tested this set up
Number 1, well it depends on things like the "collation compatible" setting of "sp_serveroption". Read "distributed queries" in BOL for more info.
Well planned code in distributed queries can run OK as long it all executes remotely...
-
I'm trying to centralize reporting against as many as six SQL Servers. My intention was to avoid duplicating code on each server. I'd have one reporting database on one server and use DTS packages to collect data from the other servers, writing into local tables in the reporting database.
No time to install and learn about SQL Reporting Services right now
-
We've centralised like this for 30 SQL Servers, but we duplicated code.
We store the data locally as well as remotely, though
One option is to use a linked server with a hypothetical name and use sp_setnetname to set the underlying NETBIOS name to each target server in turn thus centralised code always stays as linkedserver.db.schema.object, but "linkedserver" may refer to diffeent servers
-
Holly sh1t!! I need to get some networking skills like this
. -
Thanks.
I didn't say I'd actually done it though!!!
Actually, I wrote one routine that does this on our log shipping server so ensure that login changes are replicated on our standby box.
It's not pretty and probably runs like treacle, but it works anyway and it runs out of hours so what the heck.
-
Thanks folks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply