April 9, 2013 at 6:54 pm
I know that this might cause a lot of suggestions about query optimization and such, but I have a somewhat unique situation. I work for a fishing company that manages 6 vessels. Each of these vessels produce several seafood products on board. When enough customers request a new product (i.e., new case size or packaging size), our office sends out these new products as specifications to the linked server SQL databases on each vessel.
If the desired product already exists on a vessel, a call to the linked server remote procedure updates the product with any particulars. If it doesn't exist, the same procedure inserts the new product. We have a product master table in the home database such that when the remote procedures are successful on every vessel, a process flag gets flipped from 0 to 1.
The problem is, depending upon weather conditions or whether or not the vessel is making a turn, the satellite connection to the linked server and database might drop or become extremely slow. I want to execute a stored procedure that calls the remote procedure on each linked server, but continues to execute even if one vessel's linked server experiences a timeout (dropped connection).
Example:
CREATE PROC dbo.MyProcedure
AS
declare @token1 int
,@token2 int
,@token3 int
,@token4 int
,@token5 int
,@token6 int
BEGIN
EXEC Linkedserver1.DB.dbo.SPROC @product = 'NewProduct', @Result = @token1
EXEC Linkedserver2.DB.dbo.SPROC @product = 'NewProduct', @Result = @token2
EXEC Linkedserver3.DB.dbo.SPROC @product = 'NewProduct', @Result = @token3
EXEC Linkedserver4.DB.dbo.SPROC @product = 'NewProduct', @Result = @token4
EXEC Linkedserver5.DB.dbo.SPROC @product = 'NewProduct', @Result = @token5
EXEC Linkedserver6.DB.dbo.SPROC @product = 'NewProduct', @Result = @token6
-- ... additional processing and checking of @token variables....
END
If any of the linked servers drop their connection, is there any way I can prevent it from stopping execution?
Thanks in advance,
Kurt
April 10, 2013 at 3:36 am
Although not coded in an efficent way the following might help...
CREATE PROC dbo.MyProcedure
AS
declare @token1 int
,@token2 int
,@token3 int
,@token4 int
,@token5 int
,@token6 int
BEGIN
Declare @NotDone_Linkedserver1 tinyint = 1
Declare @NotDone_Linkedserver2 tinyint = 1
....
....
--- If you want to keep looping until all the Linked Servers get called.
--- If not then remove the following line
While 0 < @Done_Linkedserver1 + @Done_Linkedserver2 + .....
begin
if @Done_Linkedserver1 = 1
Begin try
EXEC Linkedserver1.DB.dbo.SPROC @product = 'NewProduct', @Result = @token1
set @NotDone_Linkedserver1 = 0
End try
Begin
--- If the error is due to a legit issue then "set @NotDone_Linkedserver1 = 0"
End catch
if @Done_Linkedserver2 = 1
Begin try
EXEC Linkedserver2.DB.dbo.SPROC @product = 'NewProduct', @Result = @token2
set @NotDone_Linkedserver2 = 0
End try
Begin
--- If the error is due to a legit issue then "set @NotDone_Linkedserver2 = 0"
End catch
.... etc etc
end
END
April 10, 2013 at 12:52 pm
Thanks for the idea. I did use try...catch blocks with transactions and tested for the transaction state, but I crashed into DTC with so many linked servers. I will work with your idea to see if the behavior is any better. Thanks again.
April 11, 2013 at 1:17 am
Kurtman (4/10/2013)
Thanks for the idea. I did use try...catch blocks with transactions and tested for the transaction state, but I crashed into DTC with so many linked servers. I will work with your idea to see if the behavior is any better. Thanks again.
Back in the ol' VFP days it wasn't unusual to asynchronously run something on SQL Server - you open a new connection, set it to asynch and off you go. I'm not so sure it would have coped with a dropped connection, which is what you're looking for. It's not a unique requirement though and Googling (try 'running "stored procedures" asynchronous') raised two different ways of achieving this; using Service Broker or agent jobs. SB looks tricky and involves a fair amount of initial setup. Using a job looks easy - load the new values to a staging table on the target server then fire off the job (or let it poll - you only connect for long enough to enter a new set of values into your staging table).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply