December 13, 2007 at 4:30 pm
Based on the article Customizable Error Log Scanning[/url], I'm trying to monitor logs on linked servers to avoid creating a new database on each server. My problem is with executing the master..xp_readerror log on the linked server and reading that into a temp table on my monitoring server using this code.
CREATE TABLE #Errors2000 (vchMessage varchar(255), ID int)
-- use xp_readerrorlog in order to gather all details in the current log
INSERT #Errors2000 Exec "Linked Server".master..xp_readerrorlog
insert #Errors select Substring(vchMessage,1,22), Substring(vchMessage, 24,7),Substring(vchMessage, 34,200)
from #Errors2000 where PerformanceMonitoring.dbo.udf_ValidErrorlogDate(Substring(vchMessage,1,22),getdate()) ='Good Date'
When I try this, I get the following message
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions on how to get the results of a linked server stored procedure into a temp table?
Thanks,
Tim
December 14, 2007 at 3:15 pm
So I was able to get the linked server procedure results to load into a temp table by following the instructions from this kb article, http://support.microsoft.com/kb/329332
Unfortunately I can only get it to work when the linked server is Win 2000. I am still getting the message for any linked servers running Win 2003. I have tried the 3 different security settings in the Transaction Manager Communication box of the Security Configuration, but it doesn't seem to make a difference. The linked Win 2000 will work when it is set to No Authentication Required.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply