|
|
|
date: Thu, 29 May 2008 11:46:01 -0700,
group: microsoft.public.hiserver.general
back
Re: SQL Server 2005 DB2OLEDB (AS/400) Linked Server
And provide how you are calling the stored procedure (the t-sql code you are
using).
--
HTH,
Charles Ezzell
Microsoft Host Integration
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
Any and all views expressed are those of the writer and not Microsoft.
There is no such thing as a free lunch.
Peach is not a color. Violets are not blue.
"Stephen Jackson [MSFT]" wrote in message
news:487D2BCC-727B-49AD-9188-ECF70A9648EB@microsoft.com...
> What is the format of the SP call that you are issuing?
>
> Also, can you provide details around the actual SP that is being called?
>
> --
> Stephen Jackson
> Microsoft® HIS Support
>
> Please do not send e-mail directly to this alias. This alias is for
> newsgroup purposes only. This posting is provided "AS IS"
> with no warranties, and confers no rights.
>
>
> "ChrisMoix" wrote in message
> news:8C220FED-86B2-44FC-A1C3-6C86EC673F9E@microsoft.com...
>> Hello,
>>
>> I am trying to execute a DB2 stored procedure from a SQL Server 2005
>> (64-bit) server. The SQL Server is SQL 2005 SP2 (9.00.3054.00) and the
>> AS400
>> is V5R4.
>>
>> I have installed the Microsoft OLE DB Provider for DB2 from HIS (provided
>> free for SQL Server 2005 Enterprise -
>> http://msdn.microsoft.com/en-us/library/ms178027.aspx). and i can get
>> basic
>> stored procedures to run (no parameters).
>>
>> When I pass in parameters, I get the following error:
>>
>> Msg 7215, Level 17, State 1, Line 1
>> Could not execute statement on remote server 'SYS38B'.
>>
>> This is the furthest I have gotten in this process (ODBC providers eror
>> out,
>> IBM Client Access for iSeries drivers cause a fatal exception in SQL).
>>
>> What can I do to get this to work?
>>
date: Fri, 30 May 2008 12:20:01 -0400
author: Charles Ezzell \(MSFT\)
Re: SQL Server 2005 DB2OLEDB (AS/400) Linked Server
I tried executing several different ones.
1. Execute a DB2 sp that required no parameters and returned no results (it
simply increments an interger column in another table):
EXEC ('CALL KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER_NO_PARM_UPDATE') AT SYS38B
and I also tried this format
EXEC SYS38B.SYS38B.KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER_NO_PARM_UPDATE
Both give me a "Command(s) completed successfully." message, but they do not
increment the value (and they work when issued directly on the AS400 thru
Navigator).
2. Execute a DB2 stored procedure that requires no input parameters and
returns an ID and a Name:
EXEC ('Call KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER_NO_PARM') AT SYS38B
and I also tried this format:
EXEC SYS38B.SYS38B.KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER_NO_PARM
both return (what is expected):
APEMID APVNAM
011754 John Doe
3. Execute a DB2 stored procedure that requires an input parameter and
returns an ID and a Name:
EXEC ('Call KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER (13000)') AT SYS38B
results in:
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'SYS38B'.
and I tried this syntax:
EXEC SYS38B.SYS38B.KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER 13000
and I get:
APEMID APVNAM
???º54001QS
when I execute that one directly on the AS400 (Navigator), I get the same
result as in #2:
APEMID APVNAM
011754 John Doe
4. execute a DB2 stored procedure with both input and output parameters:
EXEC ('Call KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER_PARMS ( 13000, ? , ? , ?
) ') AT SYS38B
result (and I didn't expect it to work this way anyway):
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'SYS38B'.
When I try this syntax:
DECLARE @input int
DECLARE @output1 varchar(255)
DECLARE @output2 varchar(255)
DECLARE @output3 varchar(255)
SET @INPUT = 13000
EXEC SYS38B.SYS38B.KRNPGM.Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER @input,
@output1 OUTPUT, @output2 OUTPUT, @output3 OUTPUT
SELECT @output1
SELECT @output2
SELECT @output3
I get the following in Sql Server management Server:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 7212, Level 17, State 1, Line 8
Could not execute procedure 'Q_EMPLOYEE_ID_FOR_VENDOR_NUMBER' on remote
server 'SYS38B'.
Hope this isn't too much information. It's kind of baffling because the
results are so inconsistent. BTW. I can run selects just fine from SQL Server
2005, using both the 4 part names and the OPENQUERY syntax.
Thanks in advance,
Chris
"Charles Ezzell (MSFT)" wrote:
> And provide how you are calling the stored procedure (the t-sql code you are
> using).
>
>
> --
> HTH,
> Charles Ezzell
> Microsoft Host Integration
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of any included samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
> Any and all views expressed are those of the writer and not Microsoft.
> There is no such thing as a free lunch.
> Peach is not a color. Violets are not blue.
>
> "Stephen Jackson [MSFT]" wrote in message
> news:487D2BCC-727B-49AD-9188-ECF70A9648EB@microsoft.com...
> > What is the format of the SP call that you are issuing?
> >
> > Also, can you provide details around the actual SP that is being called?
> >
> > --
> > Stephen Jackson
> > Microsoft® HIS Support
> >
> > Please do not send e-mail directly to this alias. This alias is for
> > newsgroup purposes only. This posting is provided "AS IS"
> > with no warranties, and confers no rights.
> >
> >
> > "ChrisMoix" wrote in message
> > news:8C220FED-86B2-44FC-A1C3-6C86EC673F9E@microsoft.com...
> >> Hello,
> >>
> >> I am trying to execute a DB2 stored procedure from a SQL Server 2005
> >> (64-bit) server. The SQL Server is SQL 2005 SP2 (9.00.3054.00) and the
> >> AS400
> >> is V5R4.
> >>
> >> I have installed the Microsoft OLE DB Provider for DB2 from HIS (provided
> >> free for SQL Server 2005 Enterprise -
> >> http://msdn.microsoft.com/en-us/library/ms178027.aspx). and i can get
> >> basic
> >> stored procedures to run (no parameters).
> >>
> >> When I pass in parameters, I get the following error:
> >>
> >> Msg 7215, Level 17, State 1, Line 1
> >> Could not execute statement on remote server 'SYS38B'.
> >>
> >> This is the furthest I have gotten in this process (ODBC providers eror
> >> out,
> >> IBM Client Access for iSeries drivers cause a fatal exception in SQL).
> >>
> >> What can I do to get this to work?
> >>
>
>
>
date: Fri, 30 May 2008 10:58:00 -0700
author: ChrisMoix
|
|
