December 7, 2009 at 9:51 am
Hi ALL,
I was tring to run transaction on remote server B from server A, but it is erroring out with below error .
OLE DB provider "SQLNCLI" for linked server "khsdevsql02" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "khsdevsql02" was unable to begin a distributed transaction
This problem exists only when I use BEGIN TRAN statement if not it is working as expected. So I have configured MSDTC on both servers to aviod this issue but no luck.
FYI:
BOth server's has sql server 2005 residing on Windows server 2008 and these machines are virtual machines resinding on single physical machine.
I have enabled all the secuirty configurations ,DTC service is running on both .. as mentionted in couple of forums but no luck..
>>> One more thing, If I run a TRAN from server C (which is physical machine with 2003 operating system) it is working fine. I am completely stuck with this issue since past 3 days.
Any help is really appreciated .
As of now I told developers not to use TRAN to unblock them ( But its really bad thing to do but I dont have any options).
December 7, 2009 at 10:11 am
Lets see..
A - SQL 2005 on Windows 2008 - virtual
B - SQL 2005 on Windows 2008 - virtual
A and B on same physical host.
C - SQL ???? on Windows 2003 - physical
A can't start distributed transaction to B?
C can talk to B?
What do you mean by:
I have enabled all the secuirty configurations ,DTC service is running on both
Have you enabled "Allow remote clients"?
Also can machine A resolve machine B's address? Do they have the same protocols active (TCP/IP)?
CEWII
December 7, 2009 at 11:05 am
Lets see..
A - SQL 2005 on Windows 2008 - virtual
B - SQL 2005 on Windows 2008 - virtual
A and B on same physical host.
C - SQL ???? on Windows 2003 - physical
A can't start distributed transaction to B?
C can talk to B?
>> Yes. Server C has SQl server 2005 on windows 2003.
-Server C can talk to both A and B .
-A and B were able to talk to C.
-Only A and B servers are not able to talk.
What do you mean by:
I have enabled all the secuirty configurations ,DTC service is running on both
I mean ,I have enabled Allow Inbound/Outbound,allow remote clients ,remote administration...
Have you enabled "Allow remote clients"?
Also can machine A resolve machine B's address? Do they have the same protocols active (TCP/IP)?
Yes, A is resolving B and vice versa.
TCP/IP 1433 has been allocated for sql server .Not sure what port it will use for MSDTC to communicate.
>>One more thing Windows firewall is completely disabled on both A and B ..,I guess port is not an issue as firewall is disabled ..I am I right?????
December 7, 2009 at 12:43 pm
kiransuram19 (12/7/2009)
>> Yes. Server C has SQl server 2005 on windows 2003.-Server C can talk to both A and B .
-A and B were able to talk to C.
-Only A and B servers are not able to talk.
What do you mean by:
I have enabled all the secuirty configurations ,DTC service is running on both
I mean ,I have enabled Allow Inbound/Outbound,allow remote clients ,remote administration...
Have you enabled "Allow remote clients"?
Also can machine A resolve machine B's address? Do they have the same protocols active (TCP/IP)?
Yes, A is resolving B and vice versa.
TCP/IP 1433 has been allocated for sql server .Not sure what port it will use for MSDTC to communicate.
>>One more thing Windows firewall is completely disabled on both A and B ..,I guess port is not an issue as firewall is disabled ..I am I right?????
I was questioning what exactly was meant by "I have enabled all the secuirty configurations" but you clarified it in your response. A can talk to B just not with a distributed transaction? And vice-versa? Both servers are listening on port 1433 and firewalls are disabled. I think you covered server protocols with the 1433 discussion.. Have you looked at client protocols on each node? Is TCP/IP enabled on both?
There is a distinct difference between server and client protocols on the server. When using a linked server one SQL Server is acting as a client to another and in that case it uses the client protocols enabled.
Is that clear?
Because everything else seems pretty good.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply