April 6, 2007 at 5:59 pm
I have recently run into a situation with Linked Servers. Here's some environment info:
ServerA - SQL Server 2000 SP3a (Windows 2000 SP4)
ServerB - SQL Server 2000 SP3a (Windows 2000 SP4)
ServerB is setup as a Linked Server on ServerA.
ServerA has a stored procedure (SProc1) on it that calls a remote stored procedure (SProc2) on ServerB. It attempts to insert the results from SProc2 into a local temp table created in SProc1 in the form:
INSERT INTO #Tmp_RemoteData
EXEC ServerB.DB1.dbo.prc_myremoteproc
When the SProc1 runs it gets the following error:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
I also get that error if I run just the Insert Into...Execute... part directly from Query Analyzer on ServerA. However, if I run just the execute statement, it works fine, returning all the expected results. I can also run other direct queries against ServerB from ServerA so I know the Linked Server is setup correctly and working.
I can ping both Servers by IPAddress and Name from each other. I have tried the DTCPing tool provided by Microsoft and both sides are successful for all the DTC Tests.
These Servers have recently been moved to a new domain and fully patched. I cannot get the users to confirm whether they have run the Report (which uses SProc1 on ServerA) successfully since the move to the new domain (about a month ago). This issue was just reported last week. Before the move, SProc1 worked just fine.
Anyone seen this behavior before? Anyone have any suggestions on what to try next?
Thanks for any info you can provide.
Mike
April 7, 2007 at 2:26 am
I have seen this when running on windows server 2003 as microsoft chnaged the default security settings for dtc. I ma not sure if they also applied this chnage in one of server 2000 service packs.
But it may be worth having a look at the security settings for dtc under component services.
David
April 7, 2007 at 9:44 am
David,
Thanks for the reply. I have seen a number of articles on the issues with DTC Security changes with Windows XP/2003 and how to change the security settings to work with Windows 2000. But none of them apply to Windows 2000 to Windows 2000 communication. None of the settings discussed in those articles are exposed in Windows 2000.
Mike
April 9, 2007 at 6:26 am
I do not know what your stored procedure prc_myremoteproc is doing other than returning results, but if it is not updating or inserting, you can probably get around this problem by using XACT_ABORT or another method to keep the procedure out of your transaction. If it is just doing a complicated select, you do not need a distributed transaction at all and in my opinion, DTC should be avoided like an angry boss.
Otherwise, the previous post about DTC permissions is likely to be on the correct trail. Even between two SQL 2000 servers, linked servers and distributed transactions can easily be disabled by properly securing your servers.
April 9, 2007 at 11:50 am
Michael,
Thanks for your reply. I have the SET XACT_ABORT statement in the stored procs. Does not help. The remote stored procedure returns a resultset after some complex processing. I can call the remote procedure locally (thru QA or SSMS) without any problems. But when I try to insert the results from that remote procedure into a local temp table, that is when I get the error, presumably because that is when the distributed transaction begins. Since this all worked before on the same servers using the same Service Pack versions, I think some patch or additional software (IT related) has interrupted DTC communication. Although the DTCPing tool from Microsoft says everything is working correctly.
In Windows 2000, what DTC Settings can I look at (and/or change) to properly configure DTC Access between the two servers?
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply