Linked Server Problem

Folks i've two SQL SERVER, SERVER1 and SERVER2. I am updating a table at SERVER1 by joining a table with SERVER2. The query works fine, but update fails.

UPDATE a
set col1=b.col2
FROM mytable a JOIN SERVER2.mydb.dbo.mytable b ON a.id=b.id
WHERE a.date=getdate()

I get the following error:
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].

Plz advise!You need to start the "Distributed Transaction Coordinator" service on both servers.

Roby2222|||It's already running on both of the servers.|||Check whether any firewall rule is obstructing the DTC access.

To work around this behavior, install network DTC access on both servers:

Click Start, and then click Control Panel.
Click Add or Remove Programs, and then click Add/Remove Windows Components.
In the Components box, click Application Server, and then click Details.
Click to select the Enable network DTC access check box, and then click OK.
Click Next, and then follow the instructions that appear on the screen to complete the installation process.
Stop and then restart the Distributed Transaction Coordinator service.
Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).|||Thanx for the guidance, Satya.

I would let ya know after i restart the machines(on production).

Howdy!

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.
Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>
|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated.
>ETT
>"Sat" wrote:
>

Linked server for Oracle on SQL server - query performance

Hi folks,
I have created a linked server on SQL server 2000 for an Oracle 8i
database using Microsoft's OLE provider for ORACLE. The connection
works fine and I am able to run queries (select queries) agaist the
Oracle table from SQL query analyzer.
But the problem is, the query is miserably slower than if I run the
query directly on the Oracle table using either SQL*plus or TOAD. In
the former case (linked table) the query run time is upwards of 7
seconds whereas, in the latter case (directly using SQL*plus) the
query runs in 200 milli seconds. The table is indexed and I am hitting
the index columns.
Any ideas or suggestions? As always thanks in advance and your help
will be much appreciated.
Appu.Hi everybody,
I am experiencing the same problems as explained below. Help is appriciated.
ETT
"Sat" wrote:

> Hi folks,
> I have created a linked server on SQL server 2000 for an Oracle 8i
> database using Microsoft's OLE provider for ORACLE. The connection
> works fine and I am able to run queries (select queries) agaist the
> Oracle table from SQL query analyzer.
> But the problem is, the query is miserably slower than if I run the
> query directly on the Oracle table using either SQL*plus or TOAD. In
> the former case (linked table) the query run time is upwards of 7
> seconds whereas, in the latter case (directly using SQL*plus) the
> query runs in 200 milli seconds. The table is indexed and I am hitting
> the index columns.
> Any ideas or suggestions? As always thanks in advance and your help
> will be much appreciated.
> Appu.
>|||Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.
Steve Kass
Drew University
ETT wrote:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated
.
>ETT
>"Sat" wrote:
>
>