ASP, AS400 (iSeries), and stored procedures.

Does anyone have any info on how to call a stored procedure with asp classic
? Is it possible? I'm running client access v5r2.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Yes it's possible. It's pretty much like calling any stored procedure. I've
done this using both the IBM Client Access ODBC driver as well as the IBM
Client access OLE DB Provider. The Data Access components need to be
installed on the system running the ASP scripts.
Mike O.
"ryadex" <ryadex@.hotmail.com> wrote in message
news:eHQZfJt1DHA.4032@.tk2msftngp13.phx.gbl...

quote:

---

> Does anyone have any info on how to call a stored procedure with asp

---

classic? Is it possible? I'm running client access v5r2.

quote:

---

>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &

---

ASP.NET resources...sql

AS400/DB2 different column names in different configurations

Hello!

I am creating a package that will be executed against different AS400/DB2 databases. (I use a Foreach Loop). The AS400/DB2 is configured a little bit differently. This results in that the same column can have different name in different AS400/DB2. For example the column nameA4￡ADG, A4$ADG and A4?ADG is the same column.

In the Data Flow I use DataReader. Does anyone know how to get past this issue without creating one package for each column name?

Best regard

Tina

I'm not an AS400 person (or DB2), so I have no idea if this will work. But, it seems like you could use an expression to set the SQL Statement on the DataReader source, and alias the differently named columns to the same name. To set an expression on the data reader, you have to select the data flow task in the control flow, go to properties, and go to expressions.|||

Hello!

Thank you for your help!

But know I have solved it. I use different Data Flow for each set of column names. Depending on the company that is executed I defined the Precedence Constraint to "Expression and Constraint" and use the Expression:

@.[User::varCompanyName]=="CDHU"

In the Data Reader I have to set ValidateExternalMetadata to False

Best regards,

Tina

AS400 to SQL with different MEMBER name

I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.

The name of the file is:

Library = MTGLIBP2

File Name = CHSAVQPL

Member Name = INS

this is the query I have so far but I still need to reference the Member Name

SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')

I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.

The name of the file is:

Library = MTGLIBP2

File Name = CHSAVQPL

Member Name = INS

this is the query I have so far but I still need to reference the Member Name

SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')

I found the solution to my problem. I needed to use SQL Enterprise Manager to set up a DTS Package.

The first step is to link AS400 server and SQL, then set up the first query using AS400 and create an alias for the file I needed to use.

example: CREATE ALIAS qgpl.mydata FOR mtglibp2.chsavqpl (INS) the INS is the Member I need to use.

The second step is to transfer the data to SQL by using the following query :

SELECT * INTO MYTABLE

FROM OPENQUERY (AS400PL,'SELECT * FROM QGPL.MYDATA') -- (This will transfer only the data with the Member Name specifiedd)

I hope this helps. : )

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

AS400 Query

I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this?? see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Of course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:

>I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this?? see example
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

AS400 Query

I have a simple query - listing all line #'s an all info that goes with eac
h -(pretty much all of the columns are itentical - just the total is diff -
I need to get the sum of the invoice - which I have - BUT now I want just th
e summary of the invoice -
all of the columns from the detail with the total - how can I print this?
'? see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118... />
server.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comOf course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:

>I have a simple query - listing all line #'s an all info that goes with each -(pre
tty much all of the columns are itentical - just the total is diff - I need to get t
he sum of the invoice - which I have - BUT now I want just the summary of the invoic
e -
all of the columns from the detail with the total - how can I print this'' see exampl
e
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118.../>
-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

AS400 Query

I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this'' see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comOf course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:
>I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this'' see example
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

AS400 password request

Hi all,

I'm loading a working SSIS package in VB.NET and executing it programmatically. As the 'Persist Security Info' is set, it executes fine, presumably because I executed it previously with valid login info. But, when I update the connection info (programmatically) it prompts me for a password at runtime. The connection string will be provided by the user (DB Admin) and this is running as a Windows service, so what I want to do is, in the case of invalid login info, just receive a rejection, that I can respond to in the code. I'm using the IBMDA400 data provider to connect to an iSeries. Ideally, I need a generic solution that will apply to all connection strings and providers, if possible, but provider specific would be fine for now. Hope you can help....

Mazmo

i ran into this using 3 different AS400 connectors. Try making the Connection componments connection string a variable, and setting the connection string there. Fixed it for me, though security freaks would say this is bad. You could always encrypt your config file .|||

I'm pretty sure this won't solve my problem.

Regardless of the 'Persist Security Info' flag and whether it has connected previously, my customers will be creating the SSIS packages for my program to deal with. Wherever the connection string is stored, there will always be cases where it cannot authenticate. This is acceptable, as long as I can respond to the rejection by telling the user to edit their SSIS package and try again, instead of it automatically throwing up the login box.

So, basically, I want to supress the login box.

Any ideas?

|||We used a program called Macro Express that could track dialog boxes (amongst other things). This was our "Automation Process" before we got an open standards DMS. You could read the form title of the CLAE prompt, do a macro to email the person resonsible. Crummy workaround, but you are dealing with a 3rd party driver pop-up. So however else you do this in .net i don't know. Basically you will be screen scraping the environment, which menas you will also have to have the ssis running in a logged in user context, whcih goes over real well with sys admins (I hate it). Otherwise, maybe a windows programmer can shed some light on this using a pre-execute event or something.|||

maybe you could run a pre-emptive sql query against the 400 (such as SELECT 1+1) as a fake first step of you SSIS program. If it fails, send an email to somebody. Of course, it might time out and never trigger your catch clause.

(tried to edit but forums kept throwing errors...hopefully this gets through)

AS400 ODBC connection

I was able to successfully create a linked server to AS400 box without
any problem. But now, due to some reason the following query does not
return any data (0 rows)
Select * from openQuery(CWBIS800, 'select * from lib.tb where cmln =
10001001')
whereas the following query does return the correct record.
Select * from openQuery(CWBIS800, 'select * from lib.tb') where cmln =
10001001
I dont like the later query as this basically fetches all the records
from as400 (network traffic) and then applies the where clause on SQL
server side. Any idea how to fix the previous query ? please help
Sandeep Khandelwal
KeyBank
What is the data type for cmln? Did you try the first query
- exactly what is in between the single quotes - using
Client Access or on the AS400 itself?
-Sue
On 17 Sep 2004 12:47:53 -0700, Sandeep.khandelwal@.gmail.com
(Sandeep) wrote:

>I was able to successfully create a linked server to AS400 box without
>any problem. But now, due to some reason the following query does not
>return any data (0 rows)
>Select * from openQuery(CWBIS800, 'select * from lib.tb where cmln =
>10001001')
>whereas the following query does return the correct record.
>Select * from openQuery(CWBIS800, 'select * from lib.tb') where cmln =
>10001001
>I dont like the later query as this basically fetches all the records
>from as400 (network traffic) and then applies the where clause on SQL
>server side. Any idea how to fix the previous query ? please help
>
>Sandeep Khandelwal
>KeyBank

AS400 member

I need to query data through SSIS from what I was told is an AS400 DB2 member table. I am assuming this is a sub-table of the main table. I was going to write a correlated sub-query in SQL to get this data, however our AS400 contracted programmer says that there is an easier way and she pointed me to the main table's member. I do not know how to go about accessing this.

Has anybody had experience with this? The AS400 programmer is familiar with SQL syntax, however she does not know how to have SQL grab the data from a member table.

If all else fails, I will just construct my correlated sub-query.

Thanks for the information.

I assume you are using a multi-member physical file PF(the AS400 refers to files rather than tables). The default member is *FIRST and generally speaking they are only created with a single member (you could treat it like a normal table). Multi-member is a throw back to the days of card files and tape files where you had header/detail/trailer records (typically banks/insurance) with different record layouts (long before SQL was conceived. the 70s). You can get a list with the command DSPFD Library/File *MBRLIST

If you want to retrieve data from the above Library/File.member in SQL you can try the following (i have never needed to run sql on multi-member files)

Select * from Library/File.member OR Select * from Library/File(member) OR

Select * from Library.File(member)

Let me know if you need more help

|||

These types doesnot work.

I have a situation. Its like we to migrate from IBM DB2 to SQl Server. But how will we move mulri membered files? Can anyone help?

|||

Just make sure you have the latest Client Access (OLEDB) drivers for DB400. there is a user group for As400 (now called iSeries) where i am sure u will get resolution (comp.sys.ibm.as400.misc)

As far as migration is concerned

You will have 2 kinds of multi-member files.

Each member has the same columns

AS400 imports

Hi,

I am using the IBM OLE DB Provider (IBMDASQL) provided with Client Access (IBM) to access a DB2 database on an AS/400 (iSeries). Using an OLE DB Source in the data flow and Access Mode set to "OpenRowset" this works fine (even though I think it is quite slow).

However, when I change the Access Mode to "SQL Command" my data flow fails at the OLE DB Source component - not giving me any meaningful error description (only the mysterious "... the error is specific to the component..."). Using and SQL Command to access the data is important to me, since I need to put a WHERE-statement on the rows returned from the source.

/Michael

PS: I am not able to use the MS OLE DB Provider for DB2 (as far as I know it is only shipped with HIS?).

I believe you also got generic OLEDB error before seeing failures from OLEDBSrc - Did you see something like "An OLE DB error has occurred. Error code: 0x80040E00."? If yes, we talked about the same issue before in this forum. It's likely a provider issue, here is more info quoted from our dev Ted's explanation on this
"
The error returned by the provider is 0x80040E00, which is DB_E_BADACCESSORHANDLE, and means that there was a problem involving the accessor used to describe how to retrieve data from the data source. This problem is not necessarily an SSIS problem. For example, if the provider has a bug that invalidated the accessor before it is released by the client when the accessor is still needed. On our side, there isn't much we can do to diagnose this without a repro, and even with a repro, we may still need access to the provider source code to know for sure why it is returning the error.
"
Another possible solution for you, besides using HIS provider, is to create an ADO.Net:OLEDB connection for DB2 and use DataReaderSrc to pull out the data. Do you want to try that out? Let me know if any questions on using DataReaderSrc.

Thanks
Wenyang

|||

Wenyang, thanks for your reply... It does not sound too uplifting. I am going to give the ADO.NET connection a go and see if I have any luck setting up a Data Reader Source.

In the meantime: Is there any way to get a hold of the MS OLE DB Provider for DB2 without getting HIS? And if you install the trial version of HIS, is the provider also limited by the 120 day period? Also, does MS consider shipping this provider as a part of SQL Server 2005?

Thanks
Michael

|||You may want to switch to Client Access ODBC. Per IBM, the OLE DB Provider does not provide as much functionality as their ODBC driver.
It has been about 8 months since I've connected to an ISeries, but if I remember correctly the OLE DB provider does not allow you to specify a cursor mode which is why the OpenRowset is slow (the OLE provider does not support scrollable cursors).
I would try connecting to your ISeries via the .Net ODBC Driver and use the DataReader Source. That may resolve some of your issues. I only ever had one problem with the ODBC Driver and that was with PHP/ODBC where I needed to specify the cursor mode. As far as MS development packages were concerned, I never had any issues.
Larry

|||>does MS consider shipping this provider as a part of SQL Server 2005?
Yes. I was told it will be on the web as part of the feature pack of SQL Server 2005 RTM

Thanks
Wenyang|||

I am trying to connect to a DB2 database to retrieve data but always receive errors. I am using an ODBC connection using a datasource (iSeries Access ODBC Driver using SQL naming conventions) with a DataReader Source. I am attempting a simple SLQ statement - 'Select * from tablename'

The error is usually in the following format '[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - tablename in QGPL type *FILE not found.' Am I missing some setting in my DSN? Any help is appreciated.

Dan

|||It has been some time since I've used an iSeries, but the error could because your account does not have a default library setup. I can't remember how to setup default library though.
Try changing your SQL to "select * from library.table" and see if that works.
Larry Pope
|||

Thanks alot Larry! It was the simple fix you specified. I had gotten other wierd 'QGPL' errors when I played around with the SQL and I am glad this was it!

One problem was my development environment was totally SQL Server while the target environment as AS400. But after your tip and changing the tables schema name in SQL 2005 (owner in 2000), I was able to develop/deploy.

Dan

|||

Hi,

Has anyone come up with a sensible answer on this issue?

Receiving exactly the same error using the IBM ASQL OLE DB R5V3 provider.

If Data Access Mode is:

1. Table or view - works fine

2. SQL Command - The preview button works and displays the information correctly. but fails with error below when executing.

The command is a simple "Select * from ..." a single table.

If it is a provider issue why does it work fully for the Table or View mode, and in Preview for the SQL command but not when run as an SQL Command?

[ASQL SQL Command] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "ASQL SQL Command" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

|||

Has anyone found a workaround for this problem? Table/View is not an option if you want to do a lookup or merge join, since you have to specify an order by clause, which is not possible with Table/View.

I am also having strange problems using the DTS Import/Export tool whether using Table/View or SQL Command. Table/View doesn't translate decimal data types properly and SQLCommand can't seem to map the source and destination columns (although this may be a related issue).

I've asked this on another thread, but can anyone from Microsoft speak to fixes for these issues?

Thanks

|||

The only workaround I have is to use do a two step process.

1. OLE DB Source using Client Access driver and use the Table/View option to dump the table(s) into SQL Server

2. Use an SQL OLE DB Source to run an SQL Command against the dumped table(s) - which works.....

Obviously not ideal.

|||

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

|||

I am getting a very similiar error ...

[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - CYYMMDD in *LIBL TYPE *N not found.'

I can pull data just fine when I use a static search argument, but receive this error while trying to use a date function.

SELECT column1, column2
FROM library.file
WHERE column = CYYMMDD(CURRENT DATE - 1 DAYS)

I know this function exists, because I use it in queries created by a 3rd party app (ASC Sequel Viewpoint).

Any ideas?

Thank you much.

|||

KrustyDeKlown wrote:

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

Here, here! This worked extremely well for me.|||

HI,

I'm Facing the same Problem. Getting same error as you did mention.

what i'm trying to achieve:

Want to take Incremental data ( based on datetime stamp ) from AS400 and dump it to DB2 database using SQL Server 2005.

Have source connection to ISeries AS400 and destinaton connection to DB2 Database.

For fetching data i have two approaches:

1> Using OLEDB source connection : Not able to see the table names associated with any Library in "Table or View mode". can write a SQL query in the "SQL command" mode and see.preview of the table; but get error on execution of task: see Error # 1 below:

2> Using DataReader Source: I can use datareader source with an ADO.Net connection: I get data easily with this mode, but how do i specify the incremental data part in the query within that task.

Even if i stick to the approach 2 ( Datareader source ), i'm not able to insert data into DB2.

1> Here i'm using OLEDB destination to insert data into a DB2 database. not been able to do the same. Task fails with following error (see ERROR # 2 )

## Now my question is that how do i insert data into DB2 database ?

## and what are the alternatives available to insert data into DB2 database.

What solution did you get to your problem, mine is similar to yours....

HELP plz !!!!!!

Error # 1:

Error: 0xC0202009 at DB2 PRODUCTION, OLE DB Source [1]: An OLE DB error has occurred. Error code: 0x80040E00.
Error: 0xC0047038 at DB2 PRODUCTION, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Error # 2:

Error: 0xC0202009 at PRODUCTION SERVER with data reader source TO DB2, OLE DB Destination [587]: An OLE DB error has occurred. Error code: 0x80040E53.
Error: 0xC0047022 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (587) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.

Shah

AS400 imports

Hi,

I am using the IBM OLE DB Provider (IBMDASQL) provided with Client Access (IBM) to access a DB2 database on an AS/400 (iSeries). Using an OLE DB Source in the data flow and Access Mode set to "OpenRowset" this works fine (even though I think it is quite slow).

However, when I change the Access Mode to "SQL Command" my data flow fails at the OLE DB Source component - not giving me any meaningful error description (only the mysterious "... the error is specific to the component..."). Using and SQL Command to access the data is important to me, since I need to put a WHERE-statement on the rows returned from the source.

/Michael

PS: I am not able to use the MS OLE DB Provider for DB2 (as far as I know it is only shipped with HIS?).

I believe you also got generic OLEDB error before seeing failures from OLEDBSrc - Did you see something like "An OLE DB error has occurred. Error code: 0x80040E00."? If yes, we talked about the same issue before in this forum. It's likely a provider issue, here is more info quoted from our dev Ted's explanation on this
"
The error returned by the provider is 0x80040E00, which is DB_E_BADACCESSORHANDLE, and means that there was a problem involving the accessor used to describe how to retrieve data from the data source. This problem is not necessarily an SSIS problem. For example, if the provider has a bug that invalidated the accessor before it is released by the client when the accessor is still needed. On our side, there isn't much we can do to diagnose this without a repro, and even with a repro, we may still need access to the provider source code to know for sure why it is returning the error.
"
Another possible solution for you, besides using HIS provider, is to create an ADO.Net:OLEDB connection for DB2 and use DataReaderSrc to pull out the data. Do you want to try that out? Let me know if any questions on using DataReaderSrc.

Thanks
Wenyang

|||

Wenyang, thanks for your reply... It does not sound too uplifting. I am going to give the ADO.NET connection a go and see if I have any luck setting up a Data Reader Source.

In the meantime: Is there any way to get a hold of the MS OLE DB Provider for DB2 without getting HIS? And if you install the trial version of HIS, is the provider also limited by the 120 day period? Also, does MS consider shipping this provider as a part of SQL Server 2005?

Thanks
Michael

|||You may want to switch to Client Access ODBC. Per IBM, the OLE DB Provider does not provide as much functionality as their ODBC driver.
It has been about 8 months since I've connected to an ISeries, but if I remember correctly the OLE DB provider does not allow you to specify a cursor mode which is why the OpenRowset is slow (the OLE provider does not support scrollable cursors).
I would try connecting to your ISeries via the .Net ODBC Driver and use the DataReader Source. That may resolve some of your issues. I only ever had one problem with the ODBC Driver and that was with PHP/ODBC where I needed to specify the cursor mode. As far as MS development packages were concerned, I never had any issues.
Larry

|||>does MS consider shipping this provider as a part of SQL Server 2005?
Yes. I was told it will be on the web as part of the feature pack of SQL Server 2005 RTM

Thanks
Wenyang|||

I am trying to connect to a DB2 database to retrieve data but always receive errors. I am using an ODBC connection using a datasource (iSeries Access ODBC Driver using SQL naming conventions) with a DataReader Source. I am attempting a simple SLQ statement - 'Select * from tablename'

The error is usually in the following format '[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - tablename in QGPL type *FILE not found.' Am I missing some setting in my DSN? Any help is appreciated.

Dan

|||It has been some time since I've used an iSeries, but the error could because your account does not have a default library setup. I can't remember how to setup default library though.
Try changing your SQL to "select * from library.table" and see if that works.
Larry Pope
|||

Thanks alot Larry! It was the simple fix you specified. I had gotten other wierd 'QGPL' errors when I played around with the SQL and I am glad this was it!

One problem was my development environment was totally SQL Server while the target environment as AS400. But after your tip and changing the tables schema name in SQL 2005 (owner in 2000), I was able to develop/deploy.

Dan

|||

Hi,

Has anyone come up with a sensible answer on this issue?

Receiving exactly the same error using the IBM ASQL OLE DB R5V3 provider.

If Data Access Mode is:

1. Table or view - works fine

2. SQL Command - The preview button works and displays the information correctly. but fails with error below when executing.

The command is a simple "Select * from ..." a single table.

If it is a provider issue why does it work fully for the Table or View mode, and in Preview for the SQL command but not when run as an SQL Command?

[ASQL SQL Command] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "ASQL SQL Command" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

|||

Has anyone found a workaround for this problem? Table/View is not an option if you want to do a lookup or merge join, since you have to specify an order by clause, which is not possible with Table/View.

I am also having strange problems using the DTS Import/Export tool whether using Table/View or SQL Command. Table/View doesn't translate decimal data types properly and SQLCommand can't seem to map the source and destination columns (although this may be a related issue).

I've asked this on another thread, but can anyone from Microsoft speak to fixes for these issues?

Thanks

|||

The only workaround I have is to use do a two step process.

1. OLE DB Source using Client Access driver and use the Table/View option to dump the table(s) into SQL Server

2. Use an SQL OLE DB Source to run an SQL Command against the dumped table(s) - which works.....

Obviously not ideal.

|||

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

|||

I am getting a very similiar error ...

[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - CYYMMDD in *LIBL TYPE *N not found.'

I can pull data just fine when I use a static search argument, but receive this error while trying to use a date function.

SELECT column1, column2
FROM library.file
WHERE column = CYYMMDD(CURRENT DATE - 1 DAYS)

I know this function exists, because I use it in queries created by a 3rd party app (ASC Sequel Viewpoint).

Any ideas?

Thank you much.

|||

KrustyDeKlown wrote:

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

Here, here! This worked extremely well for me.|||

HI,

I'm Facing the same Problem. Getting same error as you did mention.

what i'm trying to achieve:

Want to take Incremental data ( based on datetime stamp ) from AS400 and dump it to DB2 database using SQL Server 2005.

Have source connection to ISeries AS400 and destinaton connection to DB2 Database.

For fetching data i have two approaches:

1> Using OLEDB source connection : Not able to see the table names associated with any Library in "Table or View mode". can write a SQL query in the "SQL command" mode and see.preview of the table; but get error on execution of task: see Error # 1 below:

2> Using DataReader Source: I can use datareader source with an ADO.Net connection: I get data easily with this mode, but how do i specify the incremental data part in the query within that task.

Even if i stick to the approach 2 ( Datareader source ), i'm not able to insert data into DB2.

1> Here i'm using OLEDB destination to insert data into a DB2 database. not been able to do the same. Task fails with following error (see ERROR # 2 )

## Now my question is that how do i insert data into DB2 database ?

## and what are the alternatives available to insert data into DB2 database.

What solution did you get to your problem, mine is similar to yours....

HELP plz !!!!!!

Error # 1:

Error: 0xC0202009 at DB2 PRODUCTION, OLE DB Source [1]: An OLE DB error has occurred. Error code: 0x80040E00.
Error: 0xC0047038 at DB2 PRODUCTION, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Error # 2:

Error: 0xC0202009 at PRODUCTION SERVER with data reader source TO DB2, OLE DB Destination [587]: An OLE DB error has occurred. Error code: 0x80040E53.
Error: 0xC0047022 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (587) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.

Shah

AS400 imports

Hi,

I am using the IBM OLE DB Provider (IBMDASQL) provided with Client Access (IBM) to access a DB2 database on an AS/400 (iSeries). Using an OLE DB Source in the data flow and Access Mode set to "OpenRowset" this works fine (even though I think it is quite slow).

However, when I change the Access Mode to "SQL Command" my data flow fails at the OLE DB Source component - not giving me any meaningful error description (only the mysterious "... the error is specific to the component..."). Using and SQL Command to access the data is important to me, since I need to put a WHERE-statement on the rows returned from the source.

/Michael

PS: I am not able to use the MS OLE DB Provider for DB2 (as far as I know it is only shipped with HIS?).

I believe you also got generic OLEDB error before seeing failures from OLEDBSrc - Did you see something like "An OLE DB error has occurred. Error code: 0x80040E00."? If yes, we talked about the same issue before in this forum. It's likely a provider issue, here is more info quoted from our dev Ted's explanation on this
"
The error returned by the provider is 0x80040E00, which is DB_E_BADACCESSORHANDLE, and means that there was a problem involving the accessor used to describe how to retrieve data from the data source. This problem is not necessarily an SSIS problem. For example, if the provider has a bug that invalidated the accessor before it is released by the client when the accessor is still needed. On our side, there isn't much we can do to diagnose this without a repro, and even with a repro, we may still need access to the provider source code to know for sure why it is returning the error.
"
Another possible solution for you, besides using HIS provider, is to create an ADO.Net:OLEDB connection for DB2 and use DataReaderSrc to pull out the data. Do you want to try that out? Let me know if any questions on using DataReaderSrc.

Thanks
Wenyang

|||

Wenyang, thanks for your reply... It does not sound too uplifting. I am going to give the ADO.NET connection a go and see if I have any luck setting up a Data Reader Source.

In the meantime: Is there any way to get a hold of the MS OLE DB Provider for DB2 without getting HIS? And if you install the trial version of HIS, is the provider also limited by the 120 day period? Also, does MS consider shipping this provider as a part of SQL Server 2005?

Thanks
Michael

|||You may want to switch to Client Access ODBC. Per IBM, the OLE DB Provider does not provide as much functionality as their ODBC driver.
It has been about 8 months since I've connected to an ISeries, but if I remember correctly the OLE DB provider does not allow you to specify a cursor mode which is why the OpenRowset is slow (the OLE provider does not support scrollable cursors).
I would try connecting to your ISeries via the .Net ODBC Driver and use the DataReader Source. That may resolve some of your issues. I only ever had one problem with the ODBC Driver and that was with PHP/ODBC where I needed to specify the cursor mode. As far as MS development packages were concerned, I never had any issues.
Larry

|||>does MS consider shipping this provider as a part of SQL Server 2005?
Yes. I was told it will be on the web as part of the feature pack of SQL Server 2005 RTM

Thanks
Wenyang|||

I am trying to connect to a DB2 database to retrieve data but always receive errors. I am using an ODBC connection using a datasource (iSeries Access ODBC Driver using SQL naming conventions) with a DataReader Source. I am attempting a simple SLQ statement - 'Select * from tablename'

The error is usually in the following format '[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - tablename in QGPL type *FILE not found.' Am I missing some setting in my DSN? Any help is appreciated.

Dan

|||It has been some time since I've used an iSeries, but the error could because your account does not have a default library setup. I can't remember how to setup default library though.
Try changing your SQL to "select * from library.table" and see if that works.
Larry Pope
|||

Thanks alot Larry! It was the simple fix you specified. I had gotten other wierd 'QGPL' errors when I played around with the SQL and I am glad this was it!

One problem was my development environment was totally SQL Server while the target environment as AS400. But after your tip and changing the tables schema name in SQL 2005 (owner in 2000), I was able to develop/deploy.

Dan

|||

Hi,

Has anyone come up with a sensible answer on this issue?

Receiving exactly the same error using the IBM ASQL OLE DB R5V3 provider.

If Data Access Mode is:

1. Table or view - works fine

2. SQL Command - The preview button works and displays the information correctly. but fails with error below when executing.

The command is a simple "Select * from ..." a single table.

If it is a provider issue why does it work fully for the Table or View mode, and in Preview for the SQL command but not when run as an SQL Command?

[ASQL SQL Command] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "ASQL SQL Command" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

|||

Has anyone found a workaround for this problem? Table/View is not an option if you want to do a lookup or merge join, since you have to specify an order by clause, which is not possible with Table/View.

I am also having strange problems using the DTS Import/Export tool whether using Table/View or SQL Command. Table/View doesn't translate decimal data types properly and SQLCommand can't seem to map the source and destination columns (although this may be a related issue).

I've asked this on another thread, but can anyone from Microsoft speak to fixes for these issues?

Thanks

|||

The only workaround I have is to use do a two step process.

1. OLE DB Source using Client Access driver and use the Table/View option to dump the table(s) into SQL Server

2. Use an SQL OLE DB Source to run an SQL Command against the dumped table(s) - which works.....

Obviously not ideal.

|||

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

|||

I am getting a very similiar error ...

[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - CYYMMDD in *LIBL TYPE *N not found.'

I can pull data just fine when I use a static search argument, but receive this error while trying to use a date function.

SELECT column1, column2
FROM library.file
WHERE column = CYYMMDD(CURRENT DATE - 1 DAYS)

I know this function exists, because I use it in queries created by a 3rd party app (ASC Sequel Viewpoint).

Any ideas?

Thank you much.

|||

KrustyDeKlown wrote:

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

Here, here! This worked extremely well for me.|||

HI,

I'm Facing the same Problem. Getting same error as you did mention.

what i'm trying to achieve:

Want to take Incremental data ( based on datetime stamp ) from AS400 and dump it to DB2 database using SQL Server 2005.

Have source connection to ISeries AS400 and destinaton connection to DB2 Database.

For fetching data i have two approaches:

1> Using OLEDB source connection : Not able to see the table names associated with any Library in "Table or View mode". can write a SQL query in the "SQL command" mode and see.preview of the table; but get error on execution of task: see Error # 1 below:

2> Using DataReader Source: I can use datareader source with an ADO.Net connection: I get data easily with this mode, but how do i specify the incremental data part in the query within that task.

Even if i stick to the approach 2 ( Datareader source ), i'm not able to insert data into DB2.

1> Here i'm using OLEDB destination to insert data into a DB2 database. not been able to do the same. Task fails with following error (see ERROR # 2 )

## Now my question is that how do i insert data into DB2 database ?

## and what are the alternatives available to insert data into DB2 database.

What solution did you get to your problem, mine is similar to yours....

HELP plz !!!!!!

Error # 1:

Error: 0xC0202009 at DB2 PRODUCTION, OLE DB Source [1]: An OLE DB error has occurred. Error code: 0x80040E00.
Error: 0xC0047038 at DB2 PRODUCTION, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Error # 2:

Error: 0xC0202009 at PRODUCTION SERVER with data reader source TO DB2, OLE DB Destination [587]: An OLE DB error has occurred. Error code: 0x80040E53.
Error: 0xC0047022 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (587) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.

Shah

AS400 driver issue

I am connecting to an AS400 from SQL Server using the iSeries Client Access. I am getting the following error when attempting to select from 1 table:

Server: Msg 7317, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' returned an
invalid schema definition.

The select looks something like: SELECT * FROM <instance>..<owner>.<tablename>

Selecting a subset of the fields (instead of all) does not make a difference.

All other table selects work fine - this is only a problem for one table

Any ideas? Your insight would be greatly appreciated. Thanks!I would guess one of the columns is of a type not understood by SQL Server.

You might have to create a view on the problem table that casts the columns to VARCHAR Types and see if it works that way.

Tim S|||We have 2 Windows 2003 servers, one running Standard Edition and the other Enterprise. Both connect to the AS400 via iseries ODBC. What is interesting is the server running standard can see the table via QA while the Enterprise cannot. However both can see the table using DTS. Strange isn't it?

Let me know your insights.

AS400 DB2 connection strings

I am trying to construct a connection string via iSeries OleDb provider. The default list of libraries on the AS400 for my user is fine for production. But in test scenarios the code/data libraries are rolled out through a sequence of test libraries. How do I specify the additional libraries for testing.

I connect fine and can execute sql. the statements like insert into TestLibrary.Table work fine, but the associated trigger code isn't called.

Would it be better to use the .Net Connection set of classes instead. I switched from ODBC to OleDb because of assumed thread safety issues.I don't understand what you're looking for. Can you be more specific? If trigger code isn't called, how is that an SSIS issue?|||Maybe not.

The problem is building a connection string to communicate withe as400 db2. I found other threads in this forum dealing with other issues on this connectivity. The point about the trigger code not executing is because the library on the as400 that contains the trigger code is not part of the default library list for the connecting user.

the question is how to construct the connection string to add additional libraries into the scope of the connection. I was able to do so using ODBC. I wanted to use either the OleDb or the ADO clone iSeries connection stuff to do it as well.|||This link might help: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061900&SiteID=1

AS400 database access

I am trying to run a SQL query from Query Analyzer or from my desktop to data on our AS400. The query checks out ok, but when I execute the query, it just sits there and never returns any data. Can anyone help?Need some more information, want to know if you are using "Linked Server" or just trying to execute the query using a DSN wiht OSQL.

also would like to know which drivers you have used to make the connection with the database on AS400 server.

Thanks.

AS400 Data Pull Fails as Job

When I execute the package in Debug mode, it works.

I then import the package into SQL Server 2005 and it is showing under Stored Packages --> MSDB - AS400Package

I can right click and Run Package from there and it works fine.

When I set the package up to run as a job, it fails. What I have noticed is that the Sign On that I use in the package shows that there was in "invalid login" after the job fails.

I hope I have this post in the right place and hope I have left enough information behind to help. If not let know what else I need to post. I have only been using Business Intelligence Development for a couple days and SQL Server 2005 for about a week or so. All of the SQL DTS packages that I have created seem to be working fine with their jobs as well as my stored procedures.

Thanks for any help you can give me.

I have covered how to run your SSIS packages as a Job in the thread below the error means SQL Server Agent does not have the permissions to run the package because when you run it manually it runs in the context of your account when you run it as a Job it runs in the context of the Agent. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1905695&SiteID=1

|||

Thank you for the response. I did some more research off your link and found some more links that will help those undestand the proxy portion of the post.

Create Credential

https://msdn2.microsoft.com/en-us/library/ms189522.aspx

Add Proxy

https://msdn2.microsoft.com/en-us/library/ms188763.aspx

Grant Login to Proxy

https://msdn2.microsoft.com/en-us/library/ms187338.aspx

Grant Subsystem to Proxy

http://msdn2.microsoft.com/en-us/library/ms186760.aspx

A couple more questions

Code Snippet

CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'RettigB',
SECRET = 'sdrlk8$40-dksli87nNN8';

The IDENTITY is supposed to be the account that is executing the Job and Secret is the password for that account?

I will not be able to test this out for a couple days, but there is something I don't understand. How is creating a proxy and logins going to ensure that the password for my AS400 account gets passed to the AS400 system correctly. Judging from the message in Client Access after logging in with the account that the job is sending across the password as either blank or incorrectly.

Thanks for the help and I look forward to trying this out. I hope this works.

|||

The proxy is for SQL Server Agent to use a clone of your account to run the package nothing more anything more is complication where none exist and it works. All the stored procedures are created by Microsoft for you in the links I gave you. The links you found comes with confusions ignore them use the stored procedure from the MSDN links with the instructions in code project is it very simple but it works.

|||

Using the Code Project sample with a few minor changes

Code Snippet

USE master;

CREATE CREDENTIAL Vicksburg400 WITH IDENTITY = 'myDomain\myAccount', secret = 'WindowsLoginPassword';

USE msdb;

EXEC dbo.sp_add_proxy @.proxy_name = 'VBurg400', @.credential_name = 'Vicksburg400' ;

EXEC dbo.sp_grant_login_to_proxy @.login_name = N'djvmsql', @.proxy_name = N'VBurg400';

EXEC dbo.sp_grant_proxy_to_subsystem @.proxy_name = 'VBurg400', @.subsystem_name = N'SSIS package execution';

I get the following the message.

'djvmsql' is a member of sysadmin server role and cannot be granted to or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.

I then tried to remove the sysadmin role from the account and run just the "sp_grant_login_to_proxy" again but it gave the message "Permission to proxy already granted". Meanwhile, the job is still failing. I am absolutely done for tonight. Thanks for the help so far. I appreciate your time.

|||

My mistake a few things have changed check to verify the proxy account runs then you need to enable xp_cmdshell with the surface area configuration tool, Microsoft finally documented it but also disabled it by default. Another thing is make sure the Agent is running with the correct account not the local systems account in configuration manager. Running DTS/SSIS packages to move data from AS400 with the Agent and xp_cmdshell have worked for more than 8 years, if yours is not running troubleshoot your environment. I would not add the credential, new complications Microsoft added to a very simple task and remember not to define password for your package it add complication this process needs to be very simple. I know it works because this process works in high security companies like banks and Pharmaceutical manufacture, the key is the Agent needs Admin level permissions which you create with the proxy.

http://msdn2.microsoft.com/en-us/library/ms187901.aspx

http://msdn2.microsoft.com/en-us/library/ms139805.aspx

|||

Caddre wrote:

check to verify the proxy account runs

Done

Caddre wrote:

then you need to enable xp_cmdshell with the surface area configuration tool,

Done

Caddre wrote:

Another thing is make sure the Agent is running with the correct account not the local systems account in configuration manager.

Agent is running with our domain administrator account.

Caddre wrote:

I would not add the credential, new complications Microsoft added to a very simple task

It would not allow me to create the proxy without adding a Credential to it.

Caddre wrote:

and remember not to define password for your package it add complication this process needs to be very simple. I know it works because this process works in high security companies like banks and Pharmaceutical manufacture, the key is the Agent needs Admin level permissions which you create with the proxy.

If you are referring to the dtsx package I am not sure how I can execute the package without specifying a password in the connection manager. The Connection Manager Type in the properties reads "ADO.NET: System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral,"

I have changed some items from other post to coincide with what you posted. One example is adding diamondjacksvm\Administrator to the proxy and etc... Here is the log from the failed job.

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
08/05/2007 18:51:00,ImpotHISLogon,Error,0,DJVMWEB01,ImpotHISLogon,(Job outcome),,The job failed. The Job was invoked by Schedule 3 (Every Hour). The last step to run was step 1 (Execute ImportHIS.dtsx).,00:00:01,0,0,,,,0
08/05/2007 18:51:00,ImpotHISLogon,Error,1,DJVMWEB01,ImpotHISLogon,Execute ImportHIS.dtsx,,Executed as user: diamondjacksvm\Administrator. The package execution failed. The step failed.,00:00:01,0,0,,,,0

One thing I still don't understand is how come I am getting Failed Login messages when I login into the AS400 with the account being used to import the data in the package. I run the job 3 times (3 fails) it locks out my AS400 account.

Thanks for your continued patience and help on this issue. If it will help, I am more than willing to send screen shots.

|||

Hi,
Sorry I took so long the first two links below deals with other issues like driver version and permissions because you said the connection to AS400 is closing that is not good. Please check out the DB2 driver link in the article see if you need it.

The last link comes with creating a configuration file if you don't have one because I read in another forum configuration file fixes the problem with IBM Iseries. Hope this helps.

http://msdn2.microsoft.com/en-us/library/bb332055.aspx

http://support.microsoft.com/kb/918760/
http://technet.microsoft.com/en-us/library/ms141747.aspx

|||

Just to clarify, it was the 2nd link that helped me out. What I ended up doing was logging on as administrator to the local server and building the package. I have my personal login already setup as a SQL Server login with rights to run Jobs but it was still failing. I did not change the way the package itself was built. The only difference I can see between the way I was running it before and now is that the 2nd package was built with Administrator. Let me see if I can clarify.

1rst Package - Job Failed

SQL Server Agent running as <domainname>\Administrator

Package Built with <domainname>\mmanuel

Proxy AS400 has both logins above setup as principals

Job run as AS400 Proxy

2nd Package - Job Succeeded

SQL Server Agent running as <domainname>\Administrator

Package Built with <domainname>\Administrator

Proxy AS400 has both logins above setup as principals

Job run as AS400 Proxy

Caddre - Thank you so much for your help, assistance, knowledge and patience. I very much appreciate it and look forward to reading more post from you.

|||I am glad I could help and thanks for posting the final solution it will help others.

AS400 Connection string using OLE DB

Does anyone have an example of a connection string they could post? We're
trying to connect to an AS\400 using the MS OLE DB driver to do reports
w/Reporting Services and not having much luck.On Mon, 24 Jan 2005 15:23:02 -0800, J. Way <J.
Way@.discussions.microsoft.com> wrote:
>Does anyone have an example of a connection string they could post? We're
>trying to connect to an AS\400 using the MS OLE DB driver to do reports
>w/Reporting Services and not having much luck.
I have not tried using RS with AS/400, but here is an example. Replace
the db name, file name, username, etc. as appropriate:
provider=DB2OLEDB;initial catalog=AC4TSRV;data source=AC4TSRV;network
transport library=TCPIP;network address=77.33.12.52;package
collection=PRODGEN;default schema=PRODGEN;Connection
Pooling=True;Client Application name=YourAppName;user
id=YourUserName;password=YourPassword

AS400 and SELECT *

I am migrating from 2000 to 2005 and dts to SSIS on several projects. I have not been able to successfully (without error) pull from an AS400 table using "SELECT *". If I reference all field names, it works fine. SELECT * actually works in the sense that it pulls over all the data, but it fails at the end, almost like an unepected end of stream. Normally it wouldn't be a problem tp qualify each field, but when I do the data gets sorted. In this instance I can't have it sorted because (without going into the stupid details) the production needs to match the development to suit my boss.

Connection = .Net Provider for OleDb/IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

Data Source = DataReader Source (I have yet to be able to use and OLE Source for AS400 without error, although my colleagues can - weird)

Here is the error I get when I change:

SELECT FieldName1, FieldName2, FieldName3 FROM LIBRARY.FILENAME

to

SELECT * FROM LIBRARY.FILENAME

Error: 0xC0047038 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE_SRC_Tebosspf8H" (4492) returned error code 0x80004003. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

I

Any suggestions? Is there a property on the task or connection that will fix this? Or any idea why I can't use an OLE Source to pull from AS400?

I know it may not be an answer you're looking for, but I've had great luck using the Microsoft OLE DB for DB2 driver. It's included with one of their feature packs.

As400

Hi, all!
I try run DTS from SQL with connection to AS400 (Client Access 32-bit ver. 8).I know this should be about 9000 records, but after 5000 records
it's stopped.
Plz , explain!
I'll gone to be crazy... :(((Run the profiler on the SQL Server to see that what exactly is going on..