June 6, 2006 at 6:35 am
Good day,
I posted this in T-SQL, and got some help, but I had already tried all that - so, I am hoping for more help.
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 6, 2006 at 10:57 am
Cory,
I had THE SAME problem when connecting by Openrowset to several servers from any of my SQL 2000 servers. Connection to some servers was successful and when connecting to other servers I got this message. Everything was working fine as Select, but as soon as I was inserting the results into the Temp Table it was the distributed transaction right away and as i say, it was working with some servers but not with others if run from SQL 2000.
There were a lot of discussions on this site about this problem and the common advice was to set correct security in DTC (Distributed Transaction Coordinator) in Component Services applet. I did all that and it did not help. My queries are working well from SQL Server 2005 connecting to all servers, so I run the job from SQL 2005.
Regards,Yelena Varsha
June 6, 2006 at 11:09 am
I have tried this on nearly every computer...and what has seemed interesting is that when the machines are on the same subnet and or domain, this works...however, the cross domain / subnet is not working. I am beginning to think it has something to do with that. Especially since no one here seems to have an answer...I have never been let down before.
-- Cory
June 6, 2006 at 12:00 pm
I have a question for you, inside the SP call, is there a BEGIN TRAN statement?
Mark
June 6, 2006 at 12:10 pm
inside the actual SP, I have tried it both ways. The current version does not have that statement, I have tried adding it, the begin trans and commit, I have tried "SET xact_abort ON", "SET REMOTE_PROC_TRANSACTIONS ON", and I think every combination.
I have gone thru everything on this link: http://support.microsoft.com/?kbid=839279
Please, I will try just about everything. As I have said before, I still think this is not really an issue with the code, more the fact that the servers are on different subnets and or domains. I am trying to eliminate all possibilities.
-- Cory
February 7, 2007 at 9:51 am
I have this same problem. Any resolution to this yet? It may have something to do with triggers. Triggers are part of the transaction.
February 7, 2007 at 10:09 am
Is the Distributed Transaction Coordinator service running?
Mark
February 7, 2007 at 11:39 am
Yes, stated in the post above, network DTC is up and running.
-- Cory
February 7, 2007 at 12:39 pm
Cory,
Your posting is from 6/6/2006...did you ever resolve the issue?
-SQLBill
February 7, 2007 at 1:35 pm
I was trying to recall that just today. I cannot recall if it got fixed, or we worked around it. Either way, right now, it is no longer an issue. I assume we worked around the problem, otherwise I would have posted my solution.
Sorry
-- Cory
February 7, 2007 at 2:06 pm
No problem. I was just going to ask if you had checked to see if:
1. there was a firewall between the domains.
2. if the SP was being run by a login that also had permissions on the database on the other server.
-SQLBill
February 7, 2007 at 2:07 pm
I saw three related/similar posts in this forum/site. None of which have a finite solution.
February 7, 2007 at 2:14 pm
Thanks SQLBill,
There was no corporate firewall between these domains, I was never really able to find out if windows 2003 had some sort of firewall or not that could have been an issue.
The SP was run by a login that existed on both server with permissions.
-- Cory
February 7, 2007 at 2:15 pm
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=263197
and
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=293095
have similar situations.
The first is very tricky as the statement
Set @Var = Exec ServerName.DB.DBO.proc
works while
Insert TableName
Exec ServerName.DB.DBO.proc
doesn't.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply