Linked server error in SQL 2005 RTM

I have a stored procedure which inserts data into a temp table by calling
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
||||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>> Hi,
>> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
>> with with 2005 instance. If not, could you download and run DTCPing, as
>> described in
>> in 306843 How To Troubleshoot MS DTC Firewall Issues
>> http://support.microsoft.com/?id=306843?
>> Also, use this in your script so that you may get more information about
>> the error:
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET XACT_ABORT ON
>> BEGIN TRAN
>> insert into #rs
>> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
>> @.param_string
>>
>> COMMIT TRAN
>>
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> --
>> | From: "Quinn" <dellsql@.newsgroups.nospam>
>> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
>> | Subject: Re: Linked server error in SQL 2005 RTM
>> | Date: Fri, 11 Nov 2005 02:14:29 -0600
>> | Lines: 28
>> | X-Priority: 3
>> | X-MSMail-Priority: Normal
>> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> | X-RFC2646: Format=Flowed; Response
>> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.server
>> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
>> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
>> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
>> | X-Tomcat-NG: microsoft.public.sqlserver.server
>> |
>> | BTW I am running on Windows 2003 SP1.
>> |
>> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
>> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>> | >I have a stored procedure which inserts data into a temp table by
>> calling
>> | >another stored procedure via a linked server. It works fine under SQL
>> 2000.
>> | >However I'm getting the below error after upgrading to SQL 2005. Below
>> is
>> | >also an example of the code. If I take out the insert into the temp
>> table
>> | >then the procedure executes without error.
>> | >
>> | > insert into #rs
>> | >
>> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
>> | > @.param_string
>> | >
>> | >
>> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
>> | > message "No transaction is active.".
>> | >
>> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
>> | >
>> | > The operation could not be performed because OLE DB provider
>> "SQLNCLI"
>> for
>> | > linked server "linkedserver" was unable to begin a distributed
>> | > transaction.
>> | >
>> | >
>> |
>> |
>> |
>|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> =====================================================| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
||||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get the
same error.
This was working with MSDE.
--
-Corey
"Peter Yang [MSFT]" wrote:
> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linked
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> =====================================================> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the temp
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>