Try re-creating your linked server. Not a scientific approach,
but we've had problems accessing a linked Oracle server, were
the only solution seemed to be this.

It's as though sql has one problem contacting the linked server
and then refuses to ever do it again.

HTH



"Taras" <taras.kolakovic@manto.net> wrote in message
news:bf8n2r06uo@enews4.newsguy.com...
>
>
> MS SQL server:
> Windows 2000 SP 3
> SQL 2000 standard SP 3
> Oracle client 8.1.7
> On SQL there was a linked server, which was linked to the
> Oracle server
> Oracle server
> Oracle 9i Enterprise Edition 9.2.0.1.0
>
>
> With this configuration distributed transactions worked perfectly for
about
> 6 months until the Friday 11th 2003.
>
> On Saturday the customer shut down all the servers, because they were out
> of electricity.
>
> Since then all distributed transactions stoped working.
>
>
> SYMPTOMS:
>
> if we run next sentence:
> SET XACT_ABORT ON
> SET NOCOUNT ON
> BEGIN DISTRIBUTED TRANSACTION
> update MOM_ORACLE..MAOP.TEST_STR_NOS
> Set NAZ_NOS = 'Test update 3'
> From MOM_ORACLE..MAOP.TEST_STR_NOS MAOP_SN
> inner join PP on PP.Sifra = MAOP_SN.SIF_NOS
> where MAOP_SN.SIF_NOS='9400'
> COMMIT TRANSACTION
>
>
>
> we get the next message:
>
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8000d048].
>
>
>
>
>
> some time after that even the simplest select sentence from linked server
> dosn't work:
>
> select * from MOM_ORACLE..MAOP.TEST_STR_NOS
> order by SIF_NOS DESC
>
>
>
> instead we get next message:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Oracle error occurred, but error
message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBSchemaRowset::GetRowset
> returned 0x80004005: ].
>
> After 5 - 10 minutes linked server starts to work again. Except of the
> distributed transactions of course
>
> what we have figured out so far:
>
> Nobody is awared of any changes on the SQL server before restart.
> Microsoft's test program MsDTCora.exe on all the steps returns "sucedeed".
> Normal Selects, Inserts, Updates between servers (which are not
distributed
> transactions) work normally.
>
>
>
>
>
>
>
>
>
>

Have you made sure that the Distributed Transaction Co-ordinator service is running?

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


"Stressed" <k@c.co.uk> wrote in message news:eCys3s5TDHA.1724@TK2MSFTNGP10.phx.gbl...
Try re-creating your linked server. Not a scientific approach,
but we've had problems accessing a linked Oracle server, were
the only solution seemed to be this.

It's as though sql has one problem contacting the linked server
and then refuses to ever do it again.

HTH



"Taras" <taras.kolakovic@manto.net> wrote in message
news:bf8n2r06uo@enews4.newsguy.com...
>
>
> MS SQL server:
> Windows 2000 SP 3
> SQL 2000 standard SP 3
> Oracle client 8.1.7
> On SQL there was a linked server, which was linked to the
> Oracle server
> Oracle server
> Oracle 9i Enterprise Edition 9.2.0.1.0
>
>
> With this configuration distributed transactions worked perfectly for
about
> 6 months until the Friday 11th 2003.
>
> On Saturday the customer shut down all the servers, because they were out
> of electricity.
>
> Since then all distributed transactions stoped working.
>
>
> SYMPTOMS:
>
> if we run next sentence:
> SET XACT_ABORT ON
> SET NOCOUNT ON
> BEGIN DISTRIBUTED TRANSACTION
> update MOM_ORACLE..MAOP.TEST_STR_NOS
> Set NAZ_NOS = 'Test update 3'
> From MOM_ORACLE..MAOP.TEST_STR_NOS MAOP_SN
> inner join PP on PP.Sifra = MAOP_SN.SIF_NOS
> where MAOP_SN.SIF_NOS='9400'
> COMMIT TRANSACTION
>
>
>
> we get the next message:
>
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8000d048].
>
>
>
>
>
> some time after that even the simplest select sentence from linked server
> dosn't work:
>
> select * from MOM_ORACLE..MAOP.TEST_STR_NOS
> order by SIF_NOS DESC
>
>
>
> instead we get next message:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Oracle error occurred, but error
message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBSchemaRowset::GetRowset
> returned 0x80004005: ].
>
> After 5 - 10 minutes linked server starts to work again. Except of the
> distributed transactions of course
>
> what we have figured out so far:
>
> Nobody is awared of any changes on the SQL server before restart.
> Microsoft's test program MsDTCora.exe on all the steps returns "sucedeed".
> Normal Selects, Inserts, Updates between servers (which are not
distributed
> transactions) work normally.
>
>
>
>
>
>
>
>
>
>