Linked Server from Cluster Win2003 servers (sql 2000) to Win2003 server ( sql 2005)
-
My source servers is Window 2003 using sql 2000 server and my destination server is Window 2003 using sql 2005.
My source servers is cluster servers and I have granted the network dtc access from component services and I have enabled the network dtc access from window components as well for all the cluster servers ( 2 of them) and in my destination server as well.
But it is still not working… for some reason.
I am trying to insert data from my source sql db table to the desination sql db table using the linked server and I am getting the following error message..
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
Do you guys have any idea what I should try?
Thanks in advance.
-
maybe you should have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I already followed the steps suggested in above articles..
not working...
-
did you configure MSDTC ? dcomcnfg.exe
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
how do you configure MSDTC?
-
I tried with non-cluster window 2003 server to non-cluster window 2003 server. and it is working!
when it is cluster window 2003 to non-cluster window 2003 sever, it doesn't work!!!
-
You need to run comclust.exe, which configures MSDTC on a cluster server.
John
-
MSDTC configuration in the cluster:
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/default.aspx?scid=kb;en-us;889706
-
did you add/alter the registry-key ?
Be carefull when messing with the registry !!! (backup before !)
-- HOWTO: Enable DTC Between Web Servers and SQL Servers Running Windows Server 2003
-- http://support.microsoft.com/Default.aspx?kbid=555017
declare @DosCmd varchar(500)
Set @DosCmd = 'ver'
SET nocount ON
CREATE TABLE #tmpMsver (line varchar(8000))
insert into #tmpMsver
exec master..xp_cmdshell @DosCmd
if exists (select *
from #tmpMsver
where line like '%Microsoft Windows _Version 5.2%')
begin
-- print @@servername + ' W2k3'
declare @NewSQLArg varchar(50)
declare @NumericValue int
set @NumericValue = 1
create table #tmpRegValues (Value varchar(50), Data varchar(1000))
insert into #tmpRegValues
exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSDTC'
-- HKEY_LOCAL_MACHINE, then SOFTWARE, then Microsoft.
-- 3. Right-click on MSDTC, point to Add, then select DWORD Value.
-- 4. Rename the key from the default New Value #1 to TurnOffRpcSecurity.
-- 5. Double-click the new key and change the value to 1.
if not exists (select * from #tmpRegValues where Value ='TurnOffRpcSecurity' and Data = '1')
begin
print @@servername + ' Key TurnOffRpcSecurity toevoegen'
--Bepalen volgende SQLArg.
set @NewSQLArg = 'TurnOffRpcSecurity'
--print @NewSQLArg
-- exec xp_instance_regwrite -- N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSDTC', @NewSQLArg, N'REG_DWORD', '1'
exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSDTC',
@value_name='TurnOffRpcSecurity',
@type='REG_DWORD',
@value=@NumericValue
Print 'Allow Distributed transactions' -- (W2K3 rtm)
-- alternate path DcomCnfg \ Component Services \ Computers \ My computer (rechtsklikken \ Properties \ MSDTC)
exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSDTC\Security',
@value_name='NetworkDtcAccess',
@type='REG_DWORD',
@value=@NumericValue
exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSDTC\Security',
@value_name='NetworkDtcAccessAdmin',
@type='REG_DWORD',
@value=@NumericValue
exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSDTC\Security',
@value_name='NetworkDtcAccessTransactions',
@type='REG_DWORD',
@value=@NumericValue
-- XaTransactions for DB2 and Oracle communication
exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSDTC\Security',
@value_name='XaTransactions',
@type='REG_DWORD',
@value=@NumericValue
end
else
begin
print @@servername + ' key already installed'
select cast(@@servername as char(25)) as ServerNaam, *
from #tmpRegValues
where Value ='TurnOffRpcSecurity'
end
drop table #tmpRegValues
end
else
begin
print @@servername + ' No W2k3 - Key not applicable'
end
drop table #tmpMsver
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply