June 1, 2006 at 4:26 pm
Good day,
I will do my best to explain the situation, but please feel free to ask more questions.
I have a server that used to make a call to another server for data. Now, the data resides on a NEW box on a new domain. Ever since this happened, the SP does not work, giving the following:
[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].
Msg 7391, Level 16, State 1, Procedure dtc_spTest_cory, Line 16
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
So, here is the setup:
Domain_1/Server_A - SQL Server 2000 with Windows Server 2000 (fully patched and SP'ed)
Domain_2/Server_B - same set up
Server_A has a SP that creates a temp table, then insert the data into the temp table, calling another SP on the linked server. See below:
alter
PROCEDURE [dbo].[dtc_spTest]
AS
BEGIN
Create
TABLE #tmpPractice
(
uniqueID
INT,
Name VARCHAR
(75),
StateAbbrev VARCHAR
(4),
StartDate
DATETIME,
ExpectedStudies
FLOAT,
Priority
SMALLINT,
Status
SMALLINT
)
INSERT INTO #tmpPractice
EXEC
<linkedServer>.<DB>.dbo.<SP> '8/31/06', 1,1,1,1
END
Running simply the EXEC statement from Server_A works to Server_B, but inside the SP, I get the above error...Please help!
I have verifed that Network DTC is enabled, the @@ServerName is set correct, Component Services is set as required, ect, all google searches have been tried...
TIA,
Cory
-- Cory
June 1, 2006 at 5:33 pm
So if you change the sp to just return a row of hardcoded data, it works OK?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 1, 2006 at 5:43 pm
Stax,
I am not sure I follow you...?
-- Cory
June 1, 2006 at 7:05 pm
is the msdtc service running on both boxes, is there a firewall between them or could one of the boxes be on a dmz?
June 1, 2006 at 7:12 pm
The MSDTC service is running on both machines. That reminds me, the new box (server_b) is a clustered sever.
The network guys tell me there is not a firewall between these servers...but I heard something about a PIC(?) firewall there...
I do not think there is any sort of DMZ here...I will ask the network guys again to make sure...
Good ideas, keep em coming!
-- Cory
June 1, 2006 at 7:34 pm
Easy way to do a quick check is to open query analyzer on domain you are connecting from and issue a sql statement against the domain you are trying to query.
First do normal query without distribution (use correct login with rights)
select top 10 * from servername.databasename.dbo.tablename
if this come back with data you know normal connectivity\rights are ok.
Then try:
begin distributed tran
select top 10 * from servername.databasename.dbo.tablename
commit
if this errors then something is blocking msdtc
June 1, 2006 at 7:43 pm
I found this web site (http://support.microsoft.com/?kbid=839279) that said to do the following to reproduce the issue:
SET xact_abort ON
GO
USE pubs
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM <remote_server>.pubs.dbo.authors
COMMIT TRAN
GO
and that does not work (gives the error in the 1st post), however, the following does:
SELECT * FROM <remote_server>.pubs.dbo.authors
I have also run the DTCPing utility, and that shows there are no issues.
-- Cory
June 5, 2006 at 4:58 pm
I still have not fixed this - would still love to hear some more ideas / thoughts / things to try...
Cory
-- Cory
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply