November 28, 2017 at 9:27 am
Is it possible to run a stored procedure against Server B if the stored procedure only exists on Server A?
I know it is possible if the stored procedure is setup on Server B but I am wondering is it possible if the stored procedure only exists on Server A
November 28, 2017 at 9:54 am
What do you mean by against server b? A stored procedure can reference objects on a linked server. A stored procedure can be executed on a linked server if RPC is set correctly.
November 29, 2017 at 2:23 am
ServerB is my linked server.
So, if the stored procedure exists doesn't exist on ServerB i can still executed the procedure against ServerB?
November 29, 2017 at 5:44 am
juniorDBA13 - Wednesday, November 29, 2017 2:23 AMServerB is my linked server.So, if the stored procedure exists doesn't exist on ServerB i can still executed the procedure against ServerB?
What do you mean by 'against'?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 29, 2017 at 5:51 am
against i mean execute.
I want to execute a stored procedure that is not on the linked server. The stored procedure would update tables on the linked server
November 29, 2017 at 5:55 am
juniorDBA13 - Wednesday, November 29, 2017 5:51 AMagainst i mean execute.I want to execute a stored procedure that is not on the linked server. The stored procedure would update tables on the linked server
Joe has already answered that question.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 29, 2017 at 6:01 am
What is the command to do it?
I have tested some commands and its not working so I thought it wasn't possible
November 29, 2017 at 6:06 am
juniorDBA13 - Wednesday, November 29, 2017 6:01 AMWhat is the command to do it?I have tested some commands and its not working so I thought it wasn't possible
Have you successfully created a linked server?
Have you successfully used four-part names to reference objects on the linked server?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 29, 2017 at 10:05 am
juniorDBA13 - Wednesday, November 29, 2017 6:01 AMWhat is the command to do it?I have tested some commands and its not working so I thought it wasn't possible
EXEC linkedserver.database.schema.storedprocedure;
If the procedure returns data, output parameters or return value you must enable RPC and RPC out, if not just RPC.
November 30, 2017 at 2:27 am
That is what i have been trying to run but its failing.
exec [ServerB].[master].dbo.[Test]
Could not find stored procedure 'master.dbo.Test'.
The stored procedure is located on Server A in the master db and named dbo.Test.
I have RPC and RPC out set to True on my linked server (ServerB)
Is there something I am doing wrong?
November 30, 2017 at 2:38 am
If the stored procedure is located on Sever A then specifying ServerB in your four-part name isn't going to get you anywhere. You can get this to work by (a) changing the stored procedure so that it uses four-part names to reference objects on ServerB or (b) creating the stored procedure on ServerB.
John
November 30, 2017 at 6:44 am
juniorDBA13 - Thursday, November 30, 2017 2:27 AMThat is what i have been trying to run but its failing.exec [ServerB].[master].dbo.[Test]
Could not find stored procedure 'master.dbo.Test'.The stored procedure is located on Server A in the master db and named dbo.Test.
I have RPC and RPC out set to True on my linked server (ServerB)Is there something I am doing wrong?
Have you tried running this from ServerB?
exec [ServerA].[master].dbo.[Test]
Of course, as John suggests, depending on what objects the SP references, this may be generating side effects on Server A not Server B.
November 30, 2017 at 7:40 am
The advice you have had is all correct. Does the stored procedure point to the local server or remote server? Which one are you running it from? You seem to be expecting to run it on the remote server while it exists on the local. It can be invoked from either provided you use the correct notation.
...
November 30, 2017 at 8:00 am
If the stored procedure exists on server A, the EXEC statement must refer to where it is stored.
"EXEC dbo.[Test]" when connected to the same database.
"EXEC master.dbo.[Test]" when connected to a different database on the same server.
"EXEC [ServerA].master.dbo.[Test]" when connected to a different server.
Those are your only options. It will run in the context of the database where it is stored, i.e. any two-part table names referred to in the procedure will still be interpreted as objects in the ServerA master database. A linked server reference from ServerB in the EXEC command causes it to open a connection on ServerA, and the procedure runs under that connection rather than the original connection. There is no way to have it magically infer that you wish it to use tables on another server instead.
You could define synonyms (in ServerA.master) that point to the objects you wish to use (possibly on another server), replace object references in the procedure with these synonyms, and then invoke the procedure from anywhere to have it operate on the chosen tables.
The notion of executing a procedure "against another server" kind of violates the whole notion of stored procedure. The saved execution plan refers to specific objects and can't automatically substitute other objects of the same name from another database and/or server. You need to recompile the procedure code in the target location to do this. This is a job for dynamic sql.
USE SomeOtherDatabase; EXEC (@testProcedureCommands);
Execute against another server, with parameters:
EXEC AnotherServer.Somedb.sys.sp_executesql @testProcedureCommands, N'@p1 INT, @p2 CHAR(5)', 42, 'xyzzy';
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply