March 14, 2008 at 10:51 am
Hello,
I’ve got the following queries to test one linked server, the point is that in Q1 the output appears in a moment and in Q2 no results are returned instead of it it appears to me the following output.
Q1:
select * from Linked_server.database_name.owner.table_name where 1 = 2
Q2:
:doze:
begin distributed tran
select * from Linked_server.database_name.owner.table_name where 1=2
commit tran
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[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].
:crazy:
The poin tis that i've got a lot of linked servers on another SQL Server Hosts and i really don't remember if i'm missing something 🙁
Hope you can help me again
Thanks and regards 😉
JMSM
March 14, 2008 at 11:01 am
This is a DTC error, not really a SQL error. Since you are only doing a select query in Q2, it should work if you remove the BEGIN TRANSACTION and COMMIT.
We have had this issue with our servers, but I would actually have to walk through the DTC configuration to tell you what we had to do it make it work. PeopleSoft automatically used transactions between servers, so we had to fix it, couldn't just remove the BEGIN TRANS and COMMIT statements.
If you need more help on this, let me know and I will see what I can do.
😎
March 14, 2008 at 11:10 am
- is msdtc running at both instances ?
- did you configure msdtc security ?
Windows 2003 vs other windows ?
- did you add the registry key ?
-- zie HOWTO: Enable DTC Between Web Servers and SQL Servers Running Windows Server 2003
--http://support.microsoft.com/Default.aspx?kbid=555017
set nocount on
-- is xp_cmdshell enabled ? if not, enable it
declare @SQLcmdshell varchar(100)
Create table #tmpConfigCmdShell (configName varchar(128), MinValue varchar(15), MaxValue varchar(15), ConfigValue varchar(15), RunValue varchar(15))
Set @SQLcmdshell = 'sp_configure @configname = ''xp_cmdshell'''
insert into #tmpConfigCmdShell
exec (@SQLcmdshell)
if exists (select * from #tmpConfigCmdShell where configName = 'xp_cmdshell' and RunValue = '0' )
begin
exec sp_configure @configname = 'xp_cmdshell', @configvalue = '1' ;
RECONFIGURE WITH OVERRIDE;
End
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)
-- kan ook via 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 voor DB2 en Oracle communicatie
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 reeds geïnstalleerd'
select cast(@@servername as char(25)) as ServerNaam, *
from #tmpRegValues
where Value ='TurnOffRpcSecurity'
end
drop table #tmpRegValues
end
else
begin
print @@servername + ' Geen W2k3 - Key niet van toepassing'
end
- did you fix the issue(s) with sql2000 sp3 and sp4 ?
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
--
--
-- You may receive an error message when you try to run distributed queries
-- from a 64-bit SQL Server 2005 client
-- to a linked 32-bit SQL Server 2000 server
-- Uit te voeren Query :
C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql
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
March 14, 2008 at 11:14 am
Thanks a lot for your answer.
The point is that the error was one test to can simulate the error that people return to me when executing the DTC syntax.
In this moment i can't tell you how is configured the Data Transaction Cordinator because someone is connected to this host remotely and i think that we have no licenses since i cant connect at this point on it :sick:
:allien: So can you help me on what should i check on Data Transaction Cordinator since it wasn't me that have installed it and this is installed in a cluster and i haven't expirience working on it.
Thanks again and regards,
JMSM 😉
March 14, 2008 at 11:29 am
To get you started (I have to go to a meeting now), search the Microsoft Knowledge Base with yoor error message. There are a couple of KB articles that should get you pointed in the right direction.
😎
March 14, 2008 at 12:25 pm
Hello again,
The registry key is configured as follows:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC]
"TurnOffRpcSecurity"=dword:00000001
I dont know what sould i do now, i did not change any value of this key.
Thanks and regards,
JMSM 😉
March 14, 2008 at 1:23 pm
This is scream day, meetings just keep getting in the way.....
Any way, on the way to another one, but in fixing this issue for our PeopleSoft systems, I made no changes directly to the registry. All changes were done through MMC to the DTC process.
Hopefully I will get a chance later to give you more details, but what KB articles did you read so far?
😎
March 14, 2008 at 1:38 pm
Meeting has been delayed. Question, what version of Windows Server are you running? We are using Server 2003 R2 Enterprise Edtion.
😎
March 14, 2008 at 1:47 pm
Basing this off our config.
We are not in a cluster environment, so this may be different for you.
Open Component Services, expand Component Services, expand Computers, right click My Computers and select properties.
On the My Computer Properties, click the MSDTC tab, click the Securiity Configuration button.
In this dialog box, Security Settings:
Network DTC Access should be checked.
Client and Adminitration, Allow Remote Clients should be checked.
Transaction Manager Communication, Allow Inbound and Allow Outbound checkboxes should be checked, the No Authentication Required Radio Button should be selected.
And, to be sure, DTC Logon Account should be NT Authority\NetworkService
click OK, click OK, right click My Computer, Stop MS DTC, right click My Computer Start MS DTC (the stop and start may not be needed, as MS DTC may get restarted automatically when you accept your changes).
This will most likely need to be done on all systems involved in distributed transactions.
😎
March 17, 2008 at 5:00 am
Thanks a lot.
Regards 😉
JMSM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply