Help with remote stored procedure
-
I've got a DTS package on ServerA that is intended to run a stored procedure on both ServerA and ServerB to populate a table on ServerA. The procedure is local to ServerA only. When it is executed from ServerB (using a linked server), the results of the query tell me that it is pulling data from ServerA, rather than ServerB. I think I'm missing something really basic. How can I avoid creating the stored procedure on each Server that I add to the DTS package? I'd like to have it exist in one place and be used by all the servers. Any advice would be appreciated. Thanks!
-
You might want to try using SELECT @@SERVERNAME with flow control to make sure your processes are working only on the server you desire.
I wasn't born stupid - I had to study.
-
Hey, thanks for the response. When I take DTS out of the picture and just execute the stored procedure from SQL Query Analyzer on ServerB, I can see that it's returning data from ServerA. (I'm doing something similar to select @@servername within the procedure.)
Here's the command being run on ServerB:
EXEC serverA.dbname.dbo.packagename 'parameter'
This is returning results from ServerA, but I want results from ServerB ... using a stored procedure that resides on ServerA. How can I make that happen?
-
Boy, folks on this site hate hearing this, (and for valid reason) but you may want to try Dynamic SQL. Since you obviously know how to jump servers, you could write something that points to another server (maybe a CASE statement choosing which Server you want to use - that would have to be an additional parameter passed into your SP) and gives you the name of the server of the server you desire to use.
Then, you would have a @Variable with the name of the server you want. Use that to jump servers by concatenating it into a longer variable @sql and executing that string.
If this is not making sense, I can try and throw together an example using the names ServerA and ServerB.
I wasn't born stupid - I had to study.
-
I wouldn't say no to examples if you have the time
Thanks very much for your response. -
This is way over simplified, but it 'might' get you moving in a direction that will help - hope so.... I am assuming you have a table named States for the example. Obviously, you will insert your desired code or call to another stored procedure....
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'WhatServer')
AND OBJECTPROPERTY(id, N'IsProcedure')=1)
DROP PROC WhatServer
GO
CREATE PROCEDURE WhatServer( @ServerToUse varchar(100))
AS
SET NOCOUNT ON
DECLARE @sql varchar(8000),
@CurrentServer varchar(100)
SELECT @CurrentServer = (SELECT @@SERVERNAME)
IF @CurrentServer <> @ServerToUse -- ServerA
BEGIN
SELECT @sql = 'SELECT * FROM ' +
RTRIM( LTRIM( @ServerToUse)) + '.macs.dbo.States'
EXEC( @sql)
END
ELSE
BEGIN
EXEC WhatServer 'ServerA'
END
EXEC WhatServer 'ServerB'
I wasn't born stupid - I had to study.
-
thanks very much! i'll give it a try.
in your opinion, would the solution proposed here be better or worse, in terms of maintenance, compared to creating the procedure locally on every SQL Server that it needs to run on? there won't be more than five or six SQL Servers.
-
Its always easier to change things in one place. But, if trying to get this approach to work becomes too cumbersome, you can always copy it to other servers. Great way not to answer that question, huh?
I have no idea how complicated your stored procedure is - my guess is you are well past Newbie if you know jumping databases and Dynamic SQL...
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply