MSDTC is unavailable.

[tperovic]

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]

Starting the DTC service caused the problem to go away but why does it need
it?

"tperovic" <to*********@yahoo.com> wrote in message
news:_b****************@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.

[Erland Sommarskog]

tperovic (to*********@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, es****@sommarskog.se

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