November 8, 2006 at 2:31 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 9, 2006 at 12:38 pm
Well, it is a good question. I use pretty much same approach and since we have hundreds of SQL Servers, both W2K ( most of it is still W2K) and W2003, problem usually happen with SQL Servers on W2003 ( never happened on W2K! platform). I created a Stored proc, but didn't really have a chance to put into production. So currently it is off in our environment and if the problem happened ( very rarely though), you physically needs to look into the Server ( well you would need to look at it anyway for that problem and reset\tweak msdtc).
So, I created a table with default properties of MSDTC ( W2003K) for each SQL Server. And they I run a job every , say few hours which checks if the MSDTC properties in the registry has been changed, so it sends notifications to DBA. One caveat: W2003 OEM edition and SP1 have few different properties.
There are couple of articles on MS site, which discuses the properties whic needs to be looked into
master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSDTC\Security', N'NetworkDtcAccess'
So by reseting registry values remotely you can achive what you want; but as I say, I probably wouldn't want to do that without looking into the issue first?!
November 9, 2006 at 12:40 pm
default properties of MSDTC above should be read as default values
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply