November 16, 2008 at 11:05 pm
I need to run an update statement from a sql server in a primary location that will update tables in 20 remote locations. I have set this up as a cursor with linked servers and it works fine as long as all servers are on line with no VPN problems. However, if a server is down, the cursor stops with the failed server and doesn't update the remaining servers. I could make this into 20 different steps of a job on the primary server, but I thought the cursor would give more flexibility. What's the best way to keep the cursor going?
November 17, 2008 at 7:56 am
There is no way to get the SQL session running your cursor code to survive a connection failure; connection failure is of too high a severity to be trapped by TRY...CATCH, and will always cause the session to end.
To get around this, you need to check the status of the remote servers in another SQL session so that the failures don't pull down the server - there are several ways to do this, but they're all variations on a theme.
If your local security policy allows xp_cmdshell, you can test the connection using it and sqlcmd.
Inside the cursor loop, before you run your looped code, execute something like the following:
declare @result int
declare @cmd varchar(1000)
set @cmd = replace('sqlcmd -E -S ','YOUR SERVER NAME HERE')
exec @result = xp_cmdshell @cmd, no_output
if @result = 0...
@result will be 0 if the connection test suceeded and 1 if it failed.
On our site, xp_cmdshell is not permitted, so we carry out similar tests using a .CMD file generated from a SQL Agent job.
November 17, 2008 at 9:06 am
As this is posted in a SQL Server 2005 forum, I am going to make a partial assumption, the primary server is running SQL Server 2005. What about the remote servers? If all the servers are running SQL Server 2005, you may want to look at creating a Service Broker application that will update the remote servers. This would allow you to ensure that the updates are processed in order on the remote servers even if they are down at the time the updates are sent.
Take the time to read more about SSSB in Books Online and see if that may be a direction to explorer.
November 17, 2008 at 10:33 am
Thank you for the replies - both are great tips. I think this will resolve my issue. I'll look into the service broker as well. I was planning on a log table, but SSSB may work even better.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply