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: