BEGIN DISTRIBUTED TRANSACTION then INSERT gets "Connection not open"

[Breck Carter [TeamSybase]]

I have a SQL Server 2008 SP1 database that communicates successfully
(more or less) via SAOLEDB.11 with a SQL Anywhere 11 database.

When I use BEGIN DISTRIBUTED TRANSACTION, I get the following error
messages from a subsequent cross-database INSERT:

INSERT via Linked Server:

Msg 7391, Level 16, State 2, Server BRECK-PC\SQLEXPRESS, Procedure
copy_mss_t2,
Line 22
The operation could not be performed because OLE DB provider
"SAOLEDB.11" for
linked server "mem" was unable to begin a distributed transaction.
OLE DB provider "SAOLEDB.11" for linked server "mem" returned message
"Connection not open".

INSERT via OPENROWSET:

Msg 7391, Level 16, State 2, Server BRECK-PC\SQLEXPRESS, Line 2
The operation could not be performed because OLE DB provider
"SAOLEDB.11" for
linked server "(null)" was unable to begin a distributed transaction.
OLE DB provider "SAOLEDB.11" for linked server "(null)" returned
message
"Connection not open".

If the BEGIN DISTRIBUTED TRANSACTION is NOT used, the INSERTs proceed
without these error messages. I am trying to use the BEGIN as a
workaround to the memory leak described in an earlier posting.

If anyone has experience with INSERT via SAOLEDB.11, I'll be happy to
post all the code... but I think the problem lies with some
configuration separate from the code.

Yes, the Distributed Transaction Coordinator service is running.

Yes, 'remote proc trans' is set to 1.

Yes, the SAOLEDB.11 provider is registered and configured; e.g., the
following query works:

1> SELECT *
2> FROM OPENROWSET ( 'SAOLEDB.11',
3> 'mem_PAVILION2'; 'dba'; 'sql',
4> sys.dummy )
5> GO
dummy_col
-----------
0

Breck
--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck....@risingroad.com

[Volker Barth]

Breck,

no real answer but just a few hints:
a) Does this problem also exist when both DBMSs run on the same box?
(In my small experience, some DML statemens ran fine with both SA and MS
on the same box but failed when not.)
b) Have you cheched with the MS DTCPing utility whether the DTC is setup
correctly? (I remember I had to change some DTC setup options though I
can't remember which ones.)

Sadly I have to confess that I could not solve all problems, and there
seems to be not much NG traffic on these setups...
Therefore I usually work the other way using the *much much less*
painful OMNI facility.

Volker

[Breck Carter [TeamSybase]]

I have not specified *any* special DTC setup options.

FWIW I am exploring Linked Servers as an alternative to proxy tables,
for an article, and has been one long depressing experience. The DTC
usage is an attempt to bypass a memory leak, and I have no faith that
it will work. A different workaround (single row FETCH INSERT loop)
replaces the memory leak with archaeological slowness followed by a
entirely different error which crashes the client osql.exe AFTER the
loop finishes...

[SQL Server Native Client 10.0]Unspecified error occurred on SQL
Server.
Connection may have been terminated by the server.
Msg 3624, Level 20, State 1, Server BRECK-PC\SQLEXPRESS, Procedure
copy_mss_t2,
Line 0
A system assertion check has failed. Check the SQL Server error log
for
details. Typically, an assertion failure is caused by a software bug
or data
corruption. To check for database corruption, consider running DBCC
CHECKDB. If
you agreed to send dumps to Microsoft during setup, a mini dump will
be sent to
Microsoft. An update might be available from Microsoft in the latest
Service
Pack or in a QFE from Technical Support.
Location: memilb.cpp:1783
Expression: pilb->m_cRef == 0
SPID: 51
Process ID: 1860
Location:
e:\sql10_katmai_t\sql\ntdbms\storeng\drs\oledb\rowset.h:1138
Expression: rghRows [0] == reinterpret_cast<HROW>(&m_hRowCurrent)
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860
Location: memilb.cpp:1617
Expression: (*ppilb)->m_cRef == 0
SPID: 51
Process ID: 1860

On 9 Oct 2009 01:30:55 -0700, Volker Barth