AS400 stored procedure

Has anyone been able to call an AS400 stored procedure from Reporting
Services?On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
> Has anyone been able to call an AS400 stored procedure from Reporting
> Services?
Does AS400 support ODBC? If so, you could create an ODBC connection
via a datasource and access it that way.
Regards,
Enrique Martinez
Sr. Software Consultant|||Of course the AS400 supports ODBC. (more to the point, DB2 does)
It also supports OLE-DB, and there are some interesting differences between
the drivers. The "base" driver set will be IBM's Client Access.
One answer for Reporting Services in particular *might* be web service calls
into the AS400. However, I've always done it using linked servers, and I
link twice (once with each driver), using whichever one appears to be better
for the task.
However there are definitely some problems with calling an RPG stored proc
using the drivers -- see
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y
The solution appears to be as follows (quoting from that thread):
>>on iSeries, always worked for me to have RPG program execute the SP, have
>>MSSQL statement execute the RPG pgm with a 'CALL rpgPgmName' always used
>>client access oledb driver
Note: he doesn't execute the stored proc directly *AND* he uses OLE-DB,
*NOT* ODBC. The ODBC driver -- even if patched -- tends to be less
up-to-date and capable. OTOH the ODBC sometimes does a better job of
"transparent" translation between charsets <shrug>. That is why I said
"interesting".
Note also that other people make drivers besides IBM and some of them (while
expensive) may be better able to do both these things.
I would expect the .NET-specific driver to be even more up-to-date, and
possibly better behaved. To find out about accessing AS400 data from
different environments, see:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafydynamicsqlclient.htm
HTH,
>L<
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1176899717.222877.18910@.n59g2000hsh.googlegroups.com...
> On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>
> Does AS400 support ODBC? If so, you could create an ODBC connection
> via a datasource and access it that way.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||This is how I called the AS400 stored procedure in the Query string :
CALL GSSSQLLIB.SPTEST2 ('01', 'SUBCAT', '1070401', '1070410')
I get the following error:
An error occurred while retrieving the parameters in the query.
SQL0104: Token 01 was not valid. Valid tokens: FOR WITH FETCH ORDER UNION
EXCEPT OPTIMIZE.....
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:%23UDEWjcgHHA.3412@.TK2MSFTNGP02.phx.gbl...
> Of course the AS400 supports ODBC. (more to the point, DB2 does)
> It also supports OLE-DB, and there are some interesting differences
> between the drivers. The "base" driver set will be IBM's Client Access.
> One answer for Reporting Services in particular *might* be web service
> calls into the AS400. However, I've always done it using linked servers,
> and I link twice (once with each driver), using whichever one appears to
> be better for the task.
> However there are definitely some problems with calling an RPG stored proc
> using the drivers -- see
> http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y
> The solution appears to be as follows (quoting from that thread):
>>on iSeries, always worked for me to have RPG program execute the SP, have
>>MSSQL statement execute the RPG pgm with a 'CALL rpgPgmName' always used
>>client access oledb driver
> Note: he doesn't execute the stored proc directly *AND* he uses OLE-DB,
> *NOT* ODBC. The ODBC driver -- even if patched -- tends to be less
> up-to-date and capable. OTOH the ODBC sometimes does a better job of
> "transparent" translation between charsets <shrug>. That is why I said
> "interesting".
> Note also that other people make drivers besides IBM and some of them
> (while expensive) may be better able to do both these things.
> I would expect the .NET-specific driver to be even more up-to-date, and
> possibly better behaved. To find out about accessing AS400 data from
> different environments, see:
> http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafydynamicsqlclient.htm
>
> HTH,
>
>>L<
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1176899717.222877.18910@.n59g2000hsh.googlegroups.com...
>> On Apr 17, 11:15 pm, "John Doe" <j...@.msn.com> wrote:
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>> Does AS400 support ODBC? If so, you could create an ODBC connection
>> via a datasource and access it that way.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||I finally figured it:
1) Use ODBC Connection string:
DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
figure how to work with OLEDB.
2) Use Command type "Text" and not "Stored Procedure" with the following
Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401', '1070407')
"John Doe" <jdoe@.msn.com> wrote in message
news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Has anyone been able to call an AS400 stored procedure from Reporting
> Services?
>|||Yup, when you do a CALL it's not a stored proc any more. That makes sense.
And, like I said, you pays your money and you takes your choice between the
OLEDB and ODBC drivers! I can never tell which one is going to be the better
"buy" for a given situation until I try both <g>.
Unless you're saying that you couldn't get the OLEDB connection to work at
all? In which case, I can tell you how I do it, if that will help. I have
to look at the settings every time, I can never remember them <g>.
First I should say that I do everything with linked servers, so I'm coming
across from TSQL code, not .NET code. I wrap the procedure I want in TSQL
(in 2005 you can do this with either a view or a sproc, if I remember
correctly in 2000 I could only use linked servers in sprocs but I might be
wrong).
The performance is often far better if you do it this way. It's just a
little more work to use OPENQUERY() -- you have to double the argument
delimiters, etc -- but along with better performance I think it's a bit more
maintainable.
>L<
"John Doe" <jdoe@.msn.com> wrote in message
news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>I finally figured it:
> 1) Use ODBC Connection string:
> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
> figure how to work with OLEDB.
> 2) Use Command type "Text" and not "Stored Procedure" with the following
> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
> '1070407')
>
> "John Doe" <jdoe@.msn.com> wrote in message
> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>|||Just a heads up for lurkers about linked databases. The four part naming
under SQL Server 2000 is very dangerous and pretty much useless. It takes
almost nothing for it to decide to bring the whole table local for
processing. Openquery works well and as it should. Four part naming has
improved substantially in SQL 2005, still be careful though. Looking at the
query plan will tell you what percentage is taking place remotely.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
> Yup, when you do a CALL it's not a stored proc any more. That makes
> sense.
> And, like I said, you pays your money and you takes your choice between
> the OLEDB and ODBC drivers! I can never tell which one is going to be the
> better "buy" for a given situation until I try both <g>.
> Unless you're saying that you couldn't get the OLEDB connection to work
> at all? In which case, I can tell you how I do it, if that will help. I
> have to look at the settings every time, I can never remember them <g>.
> First I should say that I do everything with linked servers, so I'm coming
> across from TSQL code, not .NET code. I wrap the procedure I want in TSQL
> (in 2005 you can do this with either a view or a sproc, if I remember
> correctly in 2000 I could only use linked servers in sprocs but I might be
> wrong).
> The performance is often far better if you do it this way. It's just a
> little more work to use OPENQUERY() -- you have to double the argument
> delimiters, etc -- but along with better performance I think it's a bit
> more maintainable.
>>L<
> "John Doe" <jdoe@.msn.com> wrote in message
> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the following
>> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
>> '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>|||Thanks for the tip! I have almost never used 4-part naming, and I can't
remember if that was because of performance testing or because it just
didn't buy me anything.
(FWIW I don't use OPENDATASOURCE either, except for to solve one specific
problem involving linking directly to an Excel spreadsheet -- the current
loc of the spreadsheet is stored in a table, etc.)
I don't use aliases much (yet) in 2005, either. Any feelings either way on
perf there?
>L<
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Just a heads up for lurkers about linked databases. The four part naming
> under SQL Server 2000 is very dangerous and pretty much useless. It takes
> almost nothing for it to decide to bring the whole table local for
> processing. Openquery works well and as it should. Four part naming has
> improved substantially in SQL 2005, still be careful though. Looking at
> the query plan will tell you what percentage is taking place remotely.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be the
>> better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to work
>> at all? In which case, I can tell you how I do it, if that will help. I
>> have to look at the settings every time, I can never remember them <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I want
>> in TSQL (in 2005 you can do this with either a view or a sproc, if I
>> remember correctly in 2000 I could only use linked servers in sprocs but
>> I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the following
>> Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT', '1070401',
>> '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>|||I haven't used aliases either.
If you are in SQL 2005 you might want to take a look at 4 part naming. It is
soooo much easier than handling all those quotes. It is actually useable in
SQL 2005 (as I stated earlier, stay away from 4 part with SQL 2000).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
news:uABKI%231gHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Thanks for the tip! I have almost never used 4-part naming, and I can't
> remember if that was because of performance testing or because it just
> didn't buy me anything.
> (FWIW I don't use OPENDATASOURCE either, except for to solve one specific
> problem involving linking directly to an Excel spreadsheet -- the current
> loc of the spreadsheet is stored in a table, etc.)
> I don't use aliases much (yet) in 2005, either. Any feelings either way
> on perf there?
>>L<
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
>> Just a heads up for lurkers about linked databases. The four part naming
>> under SQL Server 2000 is very dangerous and pretty much useless. It takes
>> almost nothing for it to decide to bring the whole table local for
>> processing. Openquery works well and as it should. Four part naming has
>> improved substantially in SQL 2005, still be careful though. Looking at
>> the query plan will tell you what percentage is taking place remotely.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
>> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be
>> the better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to work
>> at all? In which case, I can tell you how I do it, if that will help.
>> I have to look at the settings every time, I can never remember them
>> <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I
>> want in TSQL (in 2005 you can do this with either a view or a sproc, if
>> I remember correctly in 2000 I could only use linked servers in sprocs
>> but I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the
>> following Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT',
>> '1070401', '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>>
>|||>>is
> soooo much easier than handling all those quotes.
Yeah, but. As I said, anybody do any perf comparisons?
As far as how hard it is to handle the quotes... I'd still have to do it
with aliases, just one layer less. Because I have to decide at runtime
which alias (or linked server) to use, and build an "outer" statement from
there...
>L<
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23GpRMK2gHHA.4064@.TK2MSFTNGP02.phx.gbl...
>I haven't used aliases either.
> If you are in SQL 2005 you might want to take a look at 4 part naming. It
> is soooo much easier than handling all those quotes. It is actually
> useable in SQL 2005 (as I stated earlier, stay away from 4 part with SQL
> 2000).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
> news:uABKI%231gHHA.1312@.TK2MSFTNGP03.phx.gbl...
>> Thanks for the tip! I have almost never used 4-part naming, and I can't
>> remember if that was because of performance testing or because it just
>> didn't buy me anything.
>> (FWIW I don't use OPENDATASOURCE either, except for to solve one specific
>> problem involving linking directly to an Excel spreadsheet -- the current
>> loc of the spreadsheet is stored in a table, etc.)
>> I don't use aliases much (yet) in 2005, either. Any feelings either way
>> on perf there?
>>L<
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:epB7e11gHHA.3852@.TK2MSFTNGP04.phx.gbl...
>> Just a heads up for lurkers about linked databases. The four part naming
>> under SQL Server 2000 is very dangerous and pretty much useless. It
>> takes almost nothing for it to decide to bring the whole table local for
>> processing. Openquery works well and as it should. Four part naming has
>> improved substantially in SQL 2005, still be careful though. Looking at
>> the query plan will tell you what percentage is taking place remotely.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa Slater Nicholls" <lisa@.spacefold.com> wrote in message
>> news:eZcVsw1gHHA.4992@.TK2MSFTNGP06.phx.gbl...
>> Yup, when you do a CALL it's not a stored proc any more. That makes
>> sense.
>> And, like I said, you pays your money and you takes your choice between
>> the OLEDB and ODBC drivers! I can never tell which one is going to be
>> the better "buy" for a given situation until I try both <g>.
>> Unless you're saying that you couldn't get the OLEDB connection to
>> work at all? In which case, I can tell you how I do it, if that will
>> help. I have to look at the settings every time, I can never remember
>> them <g>.
>> First I should say that I do everything with linked servers, so I'm
>> coming across from TSQL code, not .NET code. I wrap the procedure I
>> want in TSQL (in 2005 you can do this with either a view or a sproc, if
>> I remember correctly in 2000 I could only use linked servers in sprocs
>> but I might be wrong).
>> The performance is often far better if you do it this way. It's just a
>> little more work to use OPENQUERY() -- you have to double the argument
>> delimiters, etc -- but along with better performance I think it's a bit
>> more maintainable.
>>L<
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:eUm%23bqegHHA.3852@.TK2MSFTNGP04.phx.gbl...
>>I finally figured it:
>> 1) Use ODBC Connection string:
>> DSN=iSeries;database=S100E37D;DBQ=GSSSQLLIB;SYSTEM=10.0.0.7. Couldn't
>> figure how to work with OLEDB.
>> 2) Use Command type "Text" and not "Stored Procedure" with the
>> following Query string: CALL GSSSQLLIB.SPTEST2('01', 'SUBCAT',
>> '1070401', '1070407')
>>
>> "John Doe" <jdoe@.msn.com> wrote in message
>> news:OZ$89AXgHHA.4916@.TK2MSFTNGP06.phx.gbl...
>> Has anyone been able to call an AS400 stored procedure from Reporting
>> Services?
>>
>>
>>
>