MSDTC is unavailable.

The following code:

DECLARE @.tsql varchar(1000)
CREATE TABLE #Test (...)
SET @.tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'
INSERT INTO #Test EXEC(@.tsql)

generates the following error message at the insert statement:

Server: Msg 8501, Level 16, State 3, Line 4
MSDTC on server 'MyComputer' is unavailable.

Why?

Thanks,
Tony Perovic
Compumation, Inc.Starting the DTC service caused the problem to go away but why does it need
it?

"tperovic" <tonyperovic@.yahoo.com> wrote in message
news:_bfLc.5403$iK.4645@.newsread2.news.atl.earthli nk.net...
> The following code:
> DECLARE @.tsql varchar(1000)
> CREATE TABLE #Test (...)
> SET @.tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'
> INSERT INTO #Test EXEC(@.tsql)
> generates the following error message at the insert statement:
> Server: Msg 8501, Level 16, State 3, Line 4
> MSDTC on server 'MyComputer' is unavailable.
> Why?
> Thanks,
> Tony Perovic
> Compumation, Inc.|||tperovic (tonyperovic@.yahoo.com) writes:
> Starting the DTC service caused the problem to go away but why does it
> need it?
>> DECLARE @.tsql varchar(1000)
>> CREATE TABLE #Test (...)
>> SET @.tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'
>> INSERT INTO #Test EXEC(@.tsql)

Because you call the other server in the context of a transaction,
defined by the INSERT statement.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp