July 25, 2005 at 1:04 pm
Given a DTS package that runs against ten SQL Servers, if one of those servers was unresponsive, what would you expect to happen?
I want a stored procedure to run against all servers that populates a table on one of the servers. If if fails to get data from one unresponsive server, I don't want it to keep the entire job from failing. It should collect data from the servers it could get to.
I haven't set up workflow in the package because I wanted the procedures to run at the same time ... there's no need for one to finish before the other starts. But do I need to have some workflow defined in order to prevent the job from producing no data when it can't get to a server? Unfortunately, this isn't an easy one for me to reproduce.
July 25, 2005 at 1:50 pm
Use Openrowset batches. The following connects first to the first server, then tries to connect to non-existing server then connects to the second server. The result is a first result set, then error message, then the third recordset. The query itself continues processing even after one connection did not respond. I am sure you don't have to say Use Pubs 3 times, but I just copied and pasted.
USE pubs
GO
SELECT *
FROM OPENROWSET('SQLOLEDB','myserver1';'sa';'sapw',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
USE pubs
GO
SELECT *
FROM OPENROWSET('SQLOLEDB','mynonexistingserver';'sa';'sapw',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
USE pubs
GO
SELECT *
FROM OPENROWSET('SQLOLEDB','myserver2';'sa';'sapw',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
Regards,Yelena Varsha
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply