November 15, 2006 at 7:53 am
Hi,
I have a simple cursor, which loops through a list of SQL servers. It then
fires of a stored procedure which remotely grabs the server drive space
(xp_fixeddrives) and inserts the values into a table (on the monitoring box)
for analysis. I run this as a job on a daily basis, however, I am
experiencing inconsistent problems with some servers in question. Every so
often I get the error "The operation could not be performed because the OLE
DB provider 'SQLOLEDB' was unable to begin a distributed transaction".
Unfortunately this fails the whole job and Im left with no data for the rest
of the servers in the list. Ideally Id like to ignore the error, continue
looping through the entire servers, flag all successful grabs then retry the
failed ones. I have tried many error handling methods with no luck, the job
fails every time. Has anyone overcome a similar problem?
P.S I have also tried osql
November 16, 2006 at 8:47 am
There are some error messages you just can't grab. How about this idea, using a stored procedure write a batch file based on the list of servers that you need to query "today" that uses osql, one server per osql call. the batch file won't fail on a sql error so it will move to the next server and on down the line.
As the last step, have the batch file run the procedure that wrote the batch file in the first place to get those servers that failed and try again. You'll need to generate a different file name for each cycle, but using a datetime stamp as part of the file name will take care of that.
The last step of the stored procedure should be to XP_CMDSHELL to run the batch file just created.
November 20, 2006 at 3:05 am
Brilliant, that worked a treat. Has been great over the weekend. Thanks!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply