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.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>

Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
.. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida
> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
--
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.