July 7, 2005 at 5:53 am
Hi
I am running distributed transactions via linked servers. I have one central XP machine linking to several WINDOWS 2000 machines. SQL Server 7.0 is running on all machines. The DTC service is running on all machines and the service on the central XP server is operating under the account of Network Service.
I am able to run the distributed query against some of the WINDOWS 2000 machines but a few return the error :
---------------------------------------------------------------------------------------------------
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.
[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].
---------------------------------------------------------------------------------------------------
Does anyone know why this would be? Any help much appreciated.
July 7, 2005 at 7:23 am
You didn't specify the service pack on the XP box, but if it's XP2, you'll likely need to do the following:
I know it says with Commerce Server, but it's something we found we had to do on Windows 2003 servers as well (and there is equivalent support docs in the KB for 2003. Also, we had to apply the COM+ 1.5 Rollup 4 for certain distributed transaction functionality to work.
K. Brian Kelley
@kbriankelley
July 7, 2005 at 8:05 am
Thanks for you help Brian. Unfortunately I am still have problems.
Yes I am running SP2 on the central XP box. However on the other server(s) I am not running windows 2003 or 2002 commerce server. I am running with windows 2000 SP4.
I have tried this and also followed the guides of similar KB articles which suggest the problem can be resolved by changing MSDTC security configurations. Unfortunately they have not worked. I have set the config options to No Authentication required and running as Network Service
I am actually able to run distributed queries against some of the SQL servers in my network, there are just a few which return this error. Unfortunately it is proving difficult to find what the configuration difference is.
…
Can you tell me more about what the COM+ 1.5 Rollup 4 is and how I can go about applying this.
July 7, 2005 at 8:10 am
Availability of Windows XP COM+ Hotfix Rollup Package 10
This looks to be the latest, although you might have to check with MS to find out. We though Package 3 was the latest on 2003 Server, only to find Package 4 was.
BTW, do you have the issue when going from one Windows 2000 system to another or does it just exist when using the XP box in the loop?
K. Brian Kelley
@kbriankelley
July 7, 2005 at 8:22 am
I believe we do have trouble going from one 2000 to another although I am restricted to only creating linked servers on an XP box and so not able to test this.
If the problem was going from one 2000 box to another, would this related to another problem?
I will look through the COM+ stuff and respond back with whether I was able to get this working or not.
Thanks for you help so far.
July 7, 2005 at 8:32 am
The issues with DTC on XP/2003 was because MS changed how DTC worked. The network DTC access/COM+ Rollup package addresses those issues. If it's a DTC issue between two Windows 2000 boxes, then something else is the culprit.
The DTC issues with 2003 caused us to drop back to 2000 for our SQL clusters as we never saw the issue you described on 2000 but could reproduce that error in our sleep on 2003. Since the COM+ Rollup Package 4, though, the errors on 2003 are gone.
K. Brian Kelley
@kbriankelley
July 8, 2005 at 8:25 am
You also need to watch for authentication failures accroos the boxes - what authentication are you using - is it Windows Authentication? and how are the Linked servers set to authenticate - it may be worth trying to use SQL authentication to see if that works as it is simpler to diagnose - if that does work then you need to start investigating Windows Auth issues
James Horsley
Workflow Consulting Limited
July 11, 2005 at 4:25 am
I am not using windows authentication.
I have now tested the problem from a Windows 2000 to a Windows 2000 machine and am getting the same problem.
After having investigated further I have found that not all distributed queries fail.
There query
------------------------------------------
insert into #temp(
test1 , test2)
select test1, test2
from "remote_server_a".DBTEST.dbo.TableTest
------------------------------------------
works on all my remote sql servers
however the query
================================
insert into #temp(
test1, test2)
exec ('
exec "remote_server_a".DBTEST.dbo.exec ''
select test1, test2
from DBTEST.dbo.TableTest''
')
================================
All the exec sql proc does on the remote server is execute the SQL parsed to it in it’s only paramater (allows me to run stuff like select @@version on a remote server).
Effectively all i am doing different in the second query is execing the select on the remote server.
Doesn’t’ work on some returning the above error. I can’t find the configuration difference.
July 11, 2005 at 11:45 am
I'll have to look in some of the internals books I have (Delaney's Inside SQL Server books and Henderson's The Guru's Guide series) to see if there's a reason for the doubled EXECs to be considered a no-no. Off-hand I don't see a reason why you'd want to do that, but I don't know of a reason off-hand why it should be failing.
K. Brian Kelley
@kbriankelley
July 19, 2005 at 3:02 am
I have now found out the error is only returned when i call a remote procedure in a distributed query.
A distributed query directly linking to the remote tables work fine.
I am only receiving the error when trying to run a remote stored procedure.
Does this shed anymore light?
July 19, 2005 at 6:19 am
Is your stored procedure run as a transaction?
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
July 19, 2005 at 7:17 am
Yes. The stored procedure forms part of the distributed transaction.
An example piece of code that fails is:
-----------------------------------------------------------
drop table #dbs
go
create table #dbs(
DATABASE_NAME varchar(30) ,
DATABASE_SIZE int ,
REMARKS varchar(500)
)
go
insert into #dbs(
DATABASE_NAME ,
DATABASE_SIZE ,
REMARKS)
exec ('
exec "<Remote Server>".master.dbo.sp_databases
')
-----------------------------------------------------------
The problem is not related to the stored procedure, it fails which ever stored procedure i use.
September 16, 2005 at 4:46 am
Just a thought: did you try the OPENQUERY or OPENROWSET SQL statement instead of the EXEC one.
October 10, 2005 at 5:17 am
Contacted MS Support. After much troubleshooting eventually concurred that this was some sort of DCOM problem.
Installing hotfix KB896720 fixed the problem.
Hope this helps anyone else who has this problem.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply