MSDTC Unavailable Windows 2003

Hi
I have a VB6 windows app which calls a VB6 COM+ application (both running on
machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
machine CANSUR001 but it keeps failing with error "MSDTC on server
'CANSUR001' is unavailable". The COM+ application is configured with
Transaction Support=Required and isolation level set to the default of
serialised (the COM+
app changes it to Read Committed)
* CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
is running SQL Server 2000.
* MSDTC is started on both machines
* I have Installed/enabled windows components "Enable network COM+ access"
and "Enable network DTC access" on both machines
* I have configured MSDTC in Component services to use "Network DTC access",
allow outbound and inbound on "Transaction Manager Communication" and set
to "No authentication required"
* Both machines are in the same domain
* No local firewalls are installed
* I've rebooted both machines but same result
* CANSUR001 is NOT part of a cluster
* I've tried running
BEGIN distributed transaction
select * from cansur001.leisure.dbo.member
from SQL Analyser in CHOPCOM001 but get the same result
Any help would be gratefully received
God Bless
RonanMSDTC is turned off by default in Windows 2003. Have you enabled it in
Windows?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:F77ECB86-A5E1-42CC-9D02-51CEBFDED779@.microsoft.com...
> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
> on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC
> access",
> allow outbound and inbound on "Transaction Manager Communication" and
> set
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||Distributed Transaction Coordinator service is enabled and started on both
machines
--
Ronan
"Ronan" wrote:

> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC access
",
> allow outbound and inbound on "Transaction Manager Communication" and s
et
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||One thing I forgot to mention is that the remote server CANSUR001 is a
Domain Contoller, anyone come across problems running distributed
transactions against
servers which are also doiman controllers?
Ronan
"Ronan" wrote:
> Distributed Transaction Coordinator service is enabled and started on both
> machines
> --
> Ronan
>
> "Ronan" wrote:
>

MSDTC unavailable / JoinTransaction returned 0x8004d01c

Hi! We have 2 database server. 1 at our company, while the other with a web
hosting company.
We wrote stored procedures to update data from tables in our company's
server to the web hosting company's server. Out of 13 of these stored
procedures, 5 failed, giving me the error message that looks like the one
below,
Server: Msg 8501, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
MSDTC on server 'ORION' is unavailable.
Server: Msg 7391, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
Can anyone help me explain what the error mean? I can post stored procedure
if required. Thanks.Hi this is one of the stored procedures that doesn't work. tsp is my user
name, server name.
CREATE PROCEDURE sp_ExportSysConf AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS (SELECT * FROM [SysConf])
BEGIN
--Step1: Remove records that do not exist in CIM
DELETE FROM [tsp].[tsp].[tsp].[SysConf]
FROM [tsp].[tsp].[tsp].[SysConf] as C2
WHERE NOT EXISTS (SELECT [C1].[ConfName], [C1].[ConfValue],
[C2].[LastUpdate] FROM [SMSBASE].[DBO].[SysConf] as [C1] where
[C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] = [C2].[ConfValue])
IF (@.@.error = 0)
BEGIN
-- no error --> Step 2: Insert new records from CIM
INSERT [tsp].[tsp].[tsp].[SysConf]([ConfName], [ConfValue])
SELECT [ConfName],[ConfValue]
FROM [SMSBASE].[DBO].[SysConf] as C2
WHERE NOT EXISTS (SELECT * FROM [tsp].[tsp].[tsp].[SysConf] as [C1]
where [C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] =
[C2].[ConfValue])
IF (@.@.error = 0)
-- no error -> commit
COMMIT TRANSACTION
ELSE
-- error occurred -> rollback
ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
GO

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC o
n
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on bot
h
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I'v
e
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
RonanI forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:

> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC
on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on b
oth
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works o
k
> in both directions. DTCtester fails with the same message as above. On th
e
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component service
s
> to "allow inbound" and "allow outound" and "no authentication required". I
've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the
sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
[vbcol=seagreen]
> I forgot to mention there is no firewall on either machine and all the por
ts
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:
>

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I've
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
Ronan
I forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:

> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> in both directions. DTCtester fails with the same message as above. On the
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> to "allow inbound" and "allow outound" and "no authentication required". I've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan
|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
[vbcol=seagreen]
> I forgot to mention there is no firewall on either machine and all the ports
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:

MSDTC Unavailable

Hi
I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
calls a COM+ component which in turn calls another COM+ component ( data
access module) which in turn calls a stored procedure on another machine
CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
in both directions. DTCtester fails with the same message as above. On the
Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
to "allow inbound" and "allow outound" and "no authentication required". I've
run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
account) to CANPAD001 but again it gives the same error.
Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
Any help would be much appreciated
--
RonanI forgot to mention there is no firewall on either machine and all the ports
are open.
I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
Ronan
"Ronan" wrote:
> Hi
> I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> calls a COM+ component which in turn calls another COM+ component ( data
> access module) which in turn calls a stored procedure on another machine
> CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> in both directions. DTCtester fails with the same message as above. On the
> Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> to "allow inbound" and "allow outound" and "no authentication required". I've
> run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> account) to CANPAD001 but again it gives the same error.
> Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> Any help would be much appreciated
> --
> Ronan|||Also in the SQL Server logs for CANPAD001 I have the following message.
CANPAD001 is not part of a a cluster.
"Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"
Ronan
"Ronan" wrote:
> I forgot to mention there is no firewall on either machine and all the ports
> are open.
> I can ping CANPAD001 from PROD_COM1 so that means WINS/DNS works ok.
>
> --
> Ronan
>
> "Ronan" wrote:
> >
> > Hi
> >
> > I have a server running Windows 2000 SP4 (PROD_COM1) running an EXE which
> > calls a COM+ component which in turn calls another COM+ component ( data
> > access module) which in turn calls a stored procedure on another machine
> > CANPAD001 (Win2K3 SP1) but the query fails each time with a message 'MSDTC on
> > server 'CANPAD001' is unavailable'. I've checked and MSDTC is started on both
> > PROD_COM1 and CANPAD001. I've used the testing tool DTCPing and it works ok
> > in both directions. DTCtester fails with the same message as above. On the
> > Win2K3 machine (CANPAD001) I've set the MSDTC setting in component services
> > to "allow inbound" and "allow outound" and "no authentication required". I've
> > run a BEGIN DISTRIBUTED Query in Query analyser from PROD_COM1 (using the sa
> > account) to CANPAD001 but again it gives the same error.
> > Is this perhaps a Win2K to Win2K3 MSDTC configuration error?
> >
> > Any help would be much appreciated
> >
> > --
> > Ronan

MSDTC on server xx is unavailable

Hi:
We have 2 sql server(both sql2000) and using update/insert
triggers to replicate between the 2 servers. the
replication has been working and now We got this error
when doing an update:
ole/db provider returned message, new transaction cannot
enlist in the specified transaction coordinator. the
operation could not be performed because the oledb
provider 'sqloledb' was unable to begin a distributed
transaction.
So I restarted msdtc service on both sql servers, now when
running an update, I got msdtc on server
destinationservernm is unavailable, but both dtc services
are started. Any thoughts? thanks.Which OS version?
--
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"dean" <dean@.hotmail.com> wrote in message
news:016b01c377c6$693f1e40$a401280a@.phx.gbl...
> Hi:
> We have 2 sql server(both sql2000) and using update/insert
> triggers to replicate between the 2 servers. the
> replication has been working and now We got this error
> when doing an update:
> ole/db provider returned message, new transaction cannot
> enlist in the specified transaction coordinator. the
> operation could not be performed because the oledb
> provider 'sqloledb' was unable to begin a distributed
> transaction.
> So I restarted msdtc service on both sql servers, now when
> running an update, I got msdtc on server
> destinationservernm is unavailable, but both dtc services
> are started. Any thoughts? thanks.

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I woul
d
>be interested in an explanation.
>"TomPearson" wrote:
>

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
Tom
Have just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom
|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:
> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:
>> Hi
>> I have two stored procedures that use a linked server to access an Access
>> database from SQL Server 2000.
>> The first takes no input:
>> CREATE PROCEDURE GetModules
>> AS
>> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
>> OPENQUERY(ProjAForcBCase0,'select * from datCase')
>> GO
>> This procedure runs fine and does exactly as expected.
>> The second parameterises the linked server name (the idea being that these
>> will be created dynamically in code) so that it can be passed in to the
>> stored procedure.
>> CREATE PROCEDURE GetModulesDynamic
>> @.LinkedServer nvarchar(4000)
>> AS
>> DECLARE @.TSQL nvarchar(4000)
>> DECLARE @.OPENQUERY nvarchar(4000)
>> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
>> SET @.TSQL = 'select * from datCase'')'
>> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
>> EXEC(@.OPENQUERY+@.TSQL)
>> GO
>> However if I run this second stored procedure I am met with the error
>> message "MSDTC on server 'SERVER_NAME' is unavailable".
>> Both these stored procedures were executed on my PC (win 2K) using a local
>> SQL Server 2000 instance and a localo access database. As such I do not
>> understand why I should require MSDTC for the second procedure and not for
>> the first.
>> Any explanation would be extremely useful.
>> Also can MSDTC be installed on Win 2K and if so where can I get it from?
>> Thanks
>> Tom

MSDTC on server is unavailable

I have a server called SCSS01 with SQL Server 2000 installed.
I have another server called NT02 with a named instance of SQL Server 2000 installed - the instance name is NT02_2000.
I have set up SCSS01 as a linked server on NT02\NT02_2000.
When I attempt to execute a transaction on NT02\NT02_2000 that updates a table on my linked server (SCSS01) I receive the following error
MSDTC on server 'NT02\NT02_2000' is unavailable.
I think this is because the MSDTC service is called NT02 and not NT02\NT02_2000.
Is this a bug or am I doing something wrong?
Any help would be appreciated.
If these are Windows 2003 servers have you installed Network MSDTC?
Check the following:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?scid=kb;[LN];817064
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Rand,
The server named NT02 is w2003 and I have already enables network DTC access - it made no difference.
The server named SCSS01 is windows NT4 server.
It is the names that look wrong to me - my NT02 server has MSDTC server running on it but when I run the transaction the error message indicates it is looking for an MSDTC server on 'NT02\NT02_2000'.
Using SQL Server Service Manager also seems to give strange results when looking at the 'Ditributed Transaction Coordinator' service - no matter which server I look at the status bar shows 'Running \\NT02 - MSDTC'
Can you give me some more help - I can provide more details if necessary.
|||Hi John,
From your descriptions, I understood that you would like to use Linked
Server with an Instance in SQL Server on Windows 2003. Have I understood
you? If there is anything I misunderstood, please feel free to let me know
First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
MSDTC' as it's an machine-level service instead of instance-level service.
Secondly, Randy's suggestion is very helpful. Have you restart the machine
after make that change? Do you use cluster server?
Thirdly, when you are attempting executing an distributed query, what's the
error message? What's your query? Could you run the query standalone in
SCSS01 and get the right result?
At last, the following KB may hit your scenrio, would you please have a
check on them according to your error message? (I keep in mind that you are
using Windows 2003 and I just want you have a look at them if the error msg
meets)
You may receive error message 8525 when you try to run a distributed
transaction on an instance of SQL Server 2000 SP3
http://support.microsoft.com/?id=834849
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2 (839279)
http://support.microsoft.com/?id=839279
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||You have understood my problem correctly.
I undertand that MSDTC is at machine level and my machine name in NT02 so the service status bar showing 'Running \\NT02 - MSDTC' is ok.
I have restarted my machine after enabling network for msdtc - it did not make any difference.
I am not using cluster server.
I can reproduce my problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error message I get when running my transaction is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on NT02\NT02_2000?
Shouldn't it be looking for MSDTC on NT02?
The same query works perfectly if I run it on SCSS01.
I have looked at the kb articles you mentioned but they do not seem to be of any help to my situation.
Do you have any more ideas?
If you need any more details then please let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>
|||Hi John,
Thanks for your prompt updates!
First of all, as we are using distributed query, we should no longer using
BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
to see whether it will resolve your issue?
Secondly, if above could not resolved you issue, would you please show me
your SQL Log files? I have to use them finding the root cause of this
issue. You could only post relative log in the newsgroup
Thank you for your patience and cooperation. If you have any questions or
concerns, dont hesitate to let me know. We are
here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don’t hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:

> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don’t hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

MSDTC on server is unavailable

I have a server called SCSS01 with SQL Server 2000 installed
I have another server called NT02 with a named instance of SQL Server 2000 installed - the instance name is NT02_2000
I have set up SCSS01 as a linked server on NT02\NT02_2000
When I attempt to execute a transaction on NT02\NT02_2000 that updates a table on my linked server (SCSS01) I receive the following error
MSDTC on server 'NT02\NT02_2000' is unavailable
I think this is because the MSDTC service is called NT02 and not NT02\NT02_2000
Is this a bug or am I doing something wrong
Any help would be appreciatedIf these are Windows 2003 servers have you installed Network MSDTC?
Check the following:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?scid=kb;[LN];817064
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Rand
The server named NT02 is w2003 and I have already enables network DTC access - it made no difference
The server named SCSS01 is windows NT4 server
It is the names that look wrong to me - my NT02 server has MSDTC server running on it but when I run the transaction the error message indicates it is looking for an MSDTC server on 'NT02\NT02_2000'
Using SQL Server Service Manager also seems to give strange results when looking at the 'Ditributed Transaction Coordinator' service - no matter which server I look at the status bar shows 'Running \\NT02 - MSDTC
Can you give me some more help - I can provide more details if necessary.|||Hi John,
From your descriptions, I understood that you would like to use Linked
Server with an Instance in SQL Server on Windows 2003. Have I understood
you? If there is anything I misunderstood, please feel free to let me know:)
First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
MSDTC' as it's an machine-level service instead of instance-level service.
Secondly, Randy's suggestion is very helpful. Have you restart the machine
after make that change? Do you use cluster server?
Thirdly, when you are attempting executing an distributed query, what's the
error message? What's your query? Could you run the query standalone in
SCSS01 and get the right result?
At last, the following KB may hit your scenrio, would you please have a
check on them according to your error message? (I keep in mind that you are
using Windows 2003 and I just want you have a look at them if the error msg
meets)
You may receive error message 8525 when you try to run a distributed
transaction on an instance of SQL Server 2000 SP3
http://support.microsoft.com/?id=834849
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2 (839279)
http://support.microsoft.com/?id=839279
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||You have understood my problem correctly.
I undertand that MSDTC is at machine level and my machine name in NT02 so the service status bar showing 'Running \\NT02 - MSDTC' is ok.
I have restarted my machine after enabling network for msdtc - it did not make any difference.
I am not using cluster server.
I can reproduce my problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error message I get when running my transaction is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on NT02\NT02_2000?
Shouldn't it be looking for MSDTC on NT02?
The same query works perfectly if I run it on SCSS01.
I have looked at the kb articles you mentioned but they do not seem to be of any help to my situation.
Do you have any more ideas?
If you need any more details then please let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||You have understood correctly.
I have restarted my machine after enabling network access for msdtc.
I can reproduce the problem with the following simple query:
begin tran
select * from scss01.web_openroad.dbo.wt_job
The error I am getting is:
Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'NT02\NT02_2000' is unavailable.
Why is it looking for MSDTC on server NT02\NT02_2000?
Shouldn't it be looking for MSDTC on server NT02?
The same query works perfectly on SCSS01.
Do you have any more ideas?
If you need any more details from me then let me know.
Thanks
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> From your descriptions, I understood that you would like to use Linked
> Server with an Instance in SQL Server on Windows 2003. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know:)
> First of all, It's OK when you seeing status bar shows 'Running \\NT02 -
> MSDTC' as it's an machine-level service instead of instance-level service.
> Secondly, Randy's suggestion is very helpful. Have you restart the machine
> after make that change? Do you use cluster server?
> Thirdly, when you are attempting executing an distributed query, what's the
> error message? What's your query? Could you run the query standalone in
> SCSS01 and get the right result?
> At last, the following KB may hit your scenrio, would you please have a
> check on them according to your error message? (I keep in mind that you are
> using Windows 2003 and I just want you have a look at them if the error msg
> meets)
> You may receive error message 8525 when you try to run a distributed
> transaction on an instance of SQL Server 2000 SP3
> http://support.microsoft.com/?id=834849
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2 (839279)
> http://support.microsoft.com/?id=839279
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> ---
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||Hi John,
Thanks for your prompt updates!
First of all, as we are using distributed query, we should no longer using
BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
to see whether it will resolve your issue?
Secondly, if above could not resolved you issue, would you please show me
your SQL Log files? I have to use them finding the root cause of this
issue. You could only post relative log in the newsgroup :)
Thank you for your patience and cooperation. If you have any questions or
concerns, don?t hesitate to let me know. We are
here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup :)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, donâ't hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I have tried using BEGIN DISTRIBUTED TRANSACTION but it makes no difference.
Below is a SQL Log file - the error at the end occurs when I attempt to execute the transaction.
2004-06-21 12:06:05.89 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-06-21 12:06:05.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-06-21 12:06:05.89 server All rights reserved.
2004-06-21 12:06:05.89 server Server Process ID is 2692.
2004-06-21 12:06:05.89 server Logging SQL Server messages in file 'f:\mssql2000\data\MSSQL$NT02_2000\log\ERRORLOG'.
2004-06-21 12:06:05.89 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2004-06-21 12:06:05.92 server SQL Server configured for thread mode processing.
2004-06-21 12:06:05.93 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-06-21 12:06:05.96 server Attempting to initialize Distributed Transaction Coordinator.
2004-06-21 12:06:05.98 server Resource Manager Creation Failed: Result Code = 0x8004d01c
2004-06-21 12:06:06.00 spid3 Starting up database 'master'.
2004-06-21 12:06:06.17 spid5 Starting up database 'model'.
2004-06-21 12:06:06.17 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-06-21 12:06:06.18 server SQL server listening on 192.4.92.47: 1045.
2004-06-21 12:06:06.18 server SQL server listening on 127.0.0.1: 1045.
2004-06-21 12:06:06.21 server SQL server listening on TCP, Named Pipes.
2004-06-21 12:06:06.21 server SQL Server is ready for client connections
2004-06-21 12:06:06.23 spid3 Server name is 'NT02\NT02_2000'.
2004-06-21 12:06:06.23 spid3 Skipping startup of clean database id 7
2004-06-21 12:06:06.23 spid8 Starting up database 'msdb'.
2004-06-21 12:06:06.23 spid9 Starting up database 'pubs'.
2004-06-21 12:06:06.23 spid10 Starting up database 'Northwind'.
2004-06-21 12:06:06.62 spid5 Clearing tempdb database.
2004-06-21 12:06:07.06 spid5 Starting up database 'tempdb'.
2004-06-21 12:06:07.15 spid3 Recovery complete.
2004-06-21 12:06:07.15 spid3 SQL global counter collection task is created.
2004-06-21 12:06:18.23 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2004-06-21 12:06:32.14 spid52 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-06-21 12:06:36.82 spid52 Starting up database 'OPENROAD_DEVELOPMENT'.
2004-06-21 12:08:25.46 spid54 Resource Manager Creation Failed: Result Code = 0x8004d01c
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> Thanks for your prompt updates!
> First of all, as we are using distributed query, we should no longer using
> BEGIN TRANSACTION. Would you please try using BEGIN DISTRIBUTED TRANSACTION
> to see whether it will resolve your issue?
> Secondly, if above could not resolved you issue, would you please show me
> your SQL Log files? I have to use them finding the root cause of this
> issue. You could only post relative log in the newsgroup :)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, donâ't hesitate to let me know. We are
> here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi John,
To solve this issue, would you please try the following steps?
1. If you are using SQL cluster, make sure MSDTC has been installed on the
cluster
successfully, and make sure you can ping the MSDTC resource's Network name?
The following documents may be helpful to rebuild MSDTC on your cluster
server
HOWTO: Rebuild or Move MSDTC Used with a SQL Failover Cluster
http://support.microsoft.com/?id=294209
2. Please make sure you have configured the MSDTC on the Windows NT/2003 by
trying the steps mentioned below successfully, then try to reboot the
machine.
After you reboot the machine, start MSDTC and then start MSSQLServer.
--Confiuration for MSDTC on Windows 2003
a. Click "Start", point to "All Programs", point to "Administrative Tools",
and then click "Component Services".
b. In the Component Services Wizard, expand "Component Services", and then
double-click "Computers".
c. Right-click "My Computer", and then click "Properties".
d. Click the "MSDTC" tab, and then click "Security Configuration".
e. In the "Security Configuration" dialog box, click to select the "Network
DTC Access" check box.
f. Under "Network DTC Access", click "Network Transactions".
g. Make sure that "DTC Logon Account" is set to "NT
Authority\NetworkService".
--End of Configuration
3. If it still does not work, try to restart the MSDTC on the Windows NT
machine, then restart MSSQLServer on that machine.
4. If it still does not, on both machines, open regedt32.exe, go to
HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC, add a DWORD named
TurnOffRpcSecurity
with the value 1 under this key, then restart MSDTC to see if it works.
If all above still could not resolved issue, what's the detailed SQL Server
and Windows Error message for the failure of running distributed
transaction error?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Changing the DTC Logon Account has solved my problem.
The log on account for my DTC service was set to 'Local System Account'
I assume the problem is that the local system account does not have network access rights.
It seems strange that the default log on account for DTC is the local system account because surely the whole purpose of the DTC service means that it will always need network access rights.
I am very grateful for your speedy response to this problem.
Thank you
John
""Mingqing Cheng [MSFT]"" wrote:
> Hi John,
> To solve this issue, would you please try the following steps?
> 1. If you are using SQL cluster, make sure MSDTC has been installed on the
> cluster
> successfully, and make sure you can ping the MSDTC resource's Network name?
> The following documents may be helpful to rebuild MSDTC on your cluster
> server
> HOWTO: Rebuild or Move MSDTC Used with a SQL Failover Cluster
> http://support.microsoft.com/?id=294209
> 2. Please make sure you have configured the MSDTC on the Windows NT/2003 by
> trying the steps mentioned below successfully, then try to reboot the
> machine.
> After you reboot the machine, start MSDTC and then start MSSQLServer.
> --Confiuration for MSDTC on Windows 2003
> a. Click "Start", point to "All Programs", point to "Administrative Tools",
> and then click "Component Services".
> b. In the Component Services Wizard, expand "Component Services", and then
> double-click "Computers".
> c. Right-click "My Computer", and then click "Properties".
> d. Click the "MSDTC" tab, and then click "Security Configuration".
> e. In the "Security Configuration" dialog box, click to select the "Network
> DTC Access" check box.
> f. Under "Network DTC Access", click "Network Transactions".
> g. Make sure that "DTC Logon Account" is set to "NT
> Authority\NetworkService".
> --End of Configuration
>
> 3. If it still does not work, try to restart the MSDTC on the Windows NT
> machine, then restart MSSQLServer on that machine.
> 4. If it still does not, on both machines, open regedt32.exe, go to
> HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC, add a DWORD named
> TurnOffRpcSecurity
> with the value 1 under this key, then restart MSDTC to see if it works.
> If all above still could not resolved issue, what's the detailed SQL Server
> and Windows Error message for the failure of running distributed
> transaction error?
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

MSDTC on server <Server-Name> is unavailable.

I got this error after I put an update trigger on a table which resides
in Database A and queries a table contained in Database B via a
linkserver. I have looked at the server and have bounced both the SQL
Server service and the DTC service and still it says that it can't find
it. Since this is a production server, resetting it is kind of out of
the question (but I'll do the old Microsoft fix if I have to). I was
just curious if anyone else has had this problem?Topper (Kinky.Taylor@.gmail.com) writes:
> I got this error after I put an update trigger on a table which resides
> in Database A and queries a table contained in Database B via a
> linkserver. I have looked at the server and have bounced both the SQL
> Server service and the DTC service and still it says that it can't find
> it. Since this is a production server, resetting it is kind of out of
> the question (but I'll do the old Microsoft fix if I have to). I was
> just curious if anyone else has had this problem?
Firewalls?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Try the following from the command prompt:
msdtc -uninstall
then...
msdtc -install
This will reset the msdtc instance on the server.
Probably will require a reboot, but this has worked for me in the past.
Good Luck...John|||This is how I got into this predicament, but I'll try a reboot later
on.
Thanks

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