About SQL2000 SP4 and DTC e Linked Server

Hi,
the scenario is :
- cluster windows 2003 SP1 withc SQL2000 SP4 A/P
- DTC on both server was configured with the modified after the service
pack1
In SQL 2000 i have 2 database:
1- DB1
2- DB2
For the DB2 i have created a linked server and it seems work .
Now i would start a distribuited transaction across DB1 & DB2 , the
stantment SQL is like this :
begin tran
insert into table-1
select top 100 convert(varchar(100),idMov) as Chiav , num from
LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
commit tran
if i run it a error was show :
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].
I check for MSDTC configuration but it seems right.
I check for this error and i found some KB that indicated a isuee with
MSDTC, so i test it with the DTCTester but the tool tell work all fine !
ARGH!!!!
It's important see that the transaction start on the node1 use a "local"
MSDTC contat a "local" linked server that poit to "local" sql server
instance for use a "local" DB2.
The MSDTC is cluster configuration.
There is any problem to use a ditribuited transaction with a "local" linked
server ?
Anybody have any idea ?
Thanks in advance.Hi,
You don't mention which KB's you've looked at, but try to see if the 2
links below helps you any further?
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/kb/817064/
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
alterx@.noemail.noemail wrote:
> Hi,
> the scenario is :
> - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
> - DTC on both server was configured with the modified after the service
> pack1
>
> In SQL 2000 i have 2 database:
> 1- DB1
> 2- DB2
> For the DB2 i have created a linked server and it seems work .
> Now i would start a distribuited transaction across DB1 & DB2 , the
> stantment SQL is like this :
>
> begin tran
> insert into table-1
> select top 100 convert(varchar(100),idMov) as Chiav , num from
> LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
> commit tran
>
> if i run it a error was show :
> 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].
>
> I check for MSDTC configuration but it seems right.
> I check for this error and i found some KB that indicated a isuee with
> MSDTC, so i test it with the DTCTester but the tool tell work all fine !
> ARGH!!!!
> It's important see that the transaction start on the node1 use a "local"
> MSDTC contat a "local" linked server that poit to "local" sql server
> instance for use a "local" DB2.
> The MSDTC is cluster configuration.
> There is any problem to use a ditribuited transaction with a "local"
> linked server ?
>
> Anybody have any idea ?
> Thanks in advance.
>|||Hello,
Thank you for posting here.
From the problem description of the post you submitted, my understanding
is: when you attempt to start a Distributed Transaction across DB 1 and DB
2, the following error message is received:
########################
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].
########################
In addition, you have created a linked server for DB2, which seems work
properly. If I have misunderstood about your concern, feel free to let me
know.
Based on my experience, the following error message can normally be caused
by one of the following
1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
network transactions.
2. Windows Firewall is enabled on the computer. By default, Windows
Firewall blocks the MSDTC program.
3. Communication issue between DB1 and DB2
Note This problem may occur even when Windows Firewall is turned off.
In your post, you have indicated that the MSDTC has been verified by the
DTCTester tool but this tool tell work fine.
First of all, I would like to check whether the object on the destination
server (DB2) refers back to the first server (DB1). This is what is known
as a loopback situation. This is not supported, as documented in SQL Server
Books Online. For more information, visit the following Microsoft Web site:
Loopback Linked Servers
http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
Next, let¡¯s manually verify your network name resolution works. Verify
that the servers can communicate with one another by name, not just by IP
address. Check in both directions (for example, from DB1 to DB2 and from
DB2 to DB1). You must resolve all name resolution problems on the network
before you run your distributed query. This may involve updating WINS, DNS,
or LMHost files.
If Windows Firewall or other third party firewall is used, please also make
sure that your Remote Procedure Call (RPC) ports are opened correctly.
Below are the steps to configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this, follow these
steps:
==============================a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl , and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the Exceptions
tab.
e. In the Add a Program dialog box, click the Browse button, and then
locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the msdtc option in
the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number text box, and
then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception in the Name
text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you used for
the exception in step j in the Programs and Services list, and then click
OK.
In addition, although we have used the DTCTester tool to manually verify
the MSDTC service, it is also recommended that we manually make sure that
the Log On As account for the MSDTC service is the Network Service account
and it allow the network transaction.
For the detail steps, please refer to the STEP ONE and STEP TWO in the
following KB article
You receive error 7391 when you run a distributed transaction against a
linked server in SQL Server 2000 on a computer that is running Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: <alterx@.noemail.noemail>
| Subject: About SQL2000 SP4 and DTC e Linked Server
| Date: Wed, 13 Jun 2007 20:50:41 +0200
| Lines: 60
| Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| the scenario is :
|
| - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| - DTC on both server was configured with the modified after the service
| pack1
|
|
| In SQL 2000 i have 2 database:
|
| 1- DB1
| 2- DB2
|
| For the DB2 i have created a linked server and it seems work .
|
| Now i would start a distribuited transaction across DB1 & DB2 , the
| stantment SQL is like this :
|
|
| begin tran
| insert into table-1
| select top 100 convert(varchar(100),idMov) as Chiav , num from
| LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| commit tran
|
|
|
| if i run it a error was show :
|
| 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].
|
|
|
| I check for MSDTC configuration but it seems right.
|
| I check for this error and i found some KB that indicated a isuee with
| MSDTC, so i test it with the DTCTester but the tool tell work all fine !
| ARGH!!!!
|
| It's important see that the transaction start on the node1 use a "local"
| MSDTC contat a "local" linked server that poit to "local" sql server
| instance for use a "local" DB2.
|
| The MSDTC is cluster configuration.
|
| There is any problem to use a ditribuited transaction with a "local"
linked
| server ?
|
|
| Anybody have any idea ?
|
| Thanks in advance.
|
||||Hello,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 190683365
| References: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 14 Jun 2007 10:16:25 GMT
| Subject: RE: About SQL2000 SP4 and DTC e Linked Server
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <QonFV0mrHHA.644@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 187
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17954
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| From the problem description of the post you submitted, my understanding
| is: when you attempt to start a Distributed Transaction across DB 1 and
DB
| 2, the following error message is received:
|
| ########################
| 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].
| ########################
|
| In addition, you have created a linked server for DB2, which seems work
| properly. If I have misunderstood about your concern, feel free to let me
| know.
|
| Based on my experience, the following error message can normally be
caused
| by one of the following
|
| 1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
| network transactions.
| 2. Windows Firewall is enabled on the computer. By default, Windows
| Firewall blocks the MSDTC program.
| 3. Communication issue between DB1 and DB2
|
| Note This problem may occur even when Windows Firewall is turned off.
|
| In your post, you have indicated that the MSDTC has been verified by the
| DTCTester tool but this tool tell work fine.
|
| First of all, I would like to check whether the object on the destination
| server (DB2) refers back to the first server (DB1). This is what is known
| as a loopback situation. This is not supported, as documented in SQL
Server
| Books Online. For more information, visit the following Microsoft Web
site:
|
| Loopback Linked Servers
| http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
|
| Next, let¡¯s manually verify your network name resolution works. Verify
| that the servers can communicate with one another by name, not just by IP
| address. Check in both directions (for example, from DB1 to DB2 and from
| DB2 to DB1). You must resolve all name resolution problems on the network
| before you run your distributed query. This may involve updating WINS,
DNS,
| or LMHost files.
|
| If Windows Firewall or other third party firewall is used, please also
make
| sure that your Remote Procedure Call (RPC) ports are opened correctly.
|
| Below are the steps to configure Windows Firewall to include the MSDTC
| program and to include port 135 as an exception. To do this, follow these
| steps:
| ==============================| a. Click Start, and then click Run.
| b. In the Run dialog box, type Firewall.cpl , and then click OK
| c. In Control Panel, double-click Windows Firewall.
| d. In the Windows Firewall dialog box, click Add Program on the
Exceptions
| tab.
| e. In the Add a Program dialog box, click the Browse button, and then
| locate the Msdtc.exe file. By default, the file is stored in the
| <Installation drive>:\Windows\System32 folder.
| f. In the Add a Program dialog box, click OK.
| g. In the Windows Firewall dialog box, click to select the msdtc option
in
| the Programs and Services list.
| h. Click Add Port on the Exceptions tab.
| i. In the Add a Port dialog box, type 135 in the Port number text box,
and
| then click to select the TCP option.
| j. In the Add a Port dialog box, type a name for the exception in the
Name
| text box, and then click OK.
| k. In the Windows Firewall dialog box, select the name that you used for
| the exception in step j in the Programs and Services list, and then click
| OK.
|
| In addition, although we have used the DTCTester tool to manually verify
| the MSDTC service, it is also recommended that we manually make sure that
| the Log On As account for the MSDTC service is the Network Service
account
| and it allow the network transaction.
|
| For the detail steps, please refer to the STEP ONE and STEP TWO in the
| following KB article
|
| You receive error 7391 when you run a distributed transaction against a
| linked server in SQL Server 2000 on a computer that is running Windows
| Server 2003
| http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE 2000, MCDBA
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: <alterx@.noemail.noemail>
| | Subject: About SQL2000 SP4 and DTC e Linked Server
| | Date: Wed, 13 Jun 2007 20:50:41 +0200
| | Lines: 60
| | Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=original
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | the scenario is :
| |
| | - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| | - DTC on both server was configured with the modified after the service
| | pack1
| |
| |
| | In SQL 2000 i have 2 database:
| |
| | 1- DB1
| | 2- DB2
| |
| | For the DB2 i have created a linked server and it seems work .
| |
| | Now i would start a distribuited transaction across DB1 & DB2 , the
| | stantment SQL is like this :
| |
| |
| | begin tran
| | insert into table-1
| | select top 100 convert(varchar(100),idMov) as Chiav , num from
| | LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| | commit tran
| |
| |
| |
| | if i run it a error was show :
| |
| | 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].
| |
| |
| |
| | I check for MSDTC configuration but it seems right.
| |
| | I check for this error and i found some KB that indicated a isuee with
| | MSDTC, so i test it with the DTCTester but the tool tell work all fine
!
| | ARGH!!!!
| |
| | It's important see that the transaction start on the node1 use a
"local"
| | MSDTC contat a "local" linked server that poit to "local" sql server
| | instance for use a "local" DB2.
| |
| | The MSDTC is cluster configuration.
| |
| | There is any problem to use a ditribuited transaction with a "local"
| linked
| | server ?
| |
| |
| | Anybody have any idea ?
| |
| | Thanks in advance.
| |
| |
|
|