SQL About AMO: client

Showing posts with label client. Show all posts

Tuesday, March 27, 2012

Access DB2 on AS/400 using IBM OLE DB Provider

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

sql

Access DB2 on AS/400 using IBM OLE DB Provider

Hi,

/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?).

Thanks
Wenyang

|||

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

Thanks
Michael

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

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!

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.

|||

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.

|||

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:

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 # 2:

Shah

Access DB2 on AS/400 using IBM OLE DB Provider

Hi,

/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?).

Thanks
Wenyang

|||

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

Thanks
Michael

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

Dan

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!

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.

|||

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.

|||

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:

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 # 2:

Shah

Access DB2 on AS/400 using IBM OLE DB Provider

Hi,

/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?).

Thanks
Wenyang

|||

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

Thanks
Michael

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

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!

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.

|||

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.

|||

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:

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 # 2:

Shah

Access DB2 on AS/400 using IBM OLE DB Provider

Hi,

/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?).

Thanks
Wenyang

|||

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

Thanks
Michael

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

Dan

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!

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.

|||

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.

|||

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:

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 # 2:

Shah

Access DB2 on AS/400 using IBM OLE DB Provider

Hi,

/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?).

Thanks
Wenyang

|||

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

Thanks
Michael

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

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!

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.

|||

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.

|||

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:

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 # 2:

Shah

sql

Access DB2 on AS/400 using IBM OLE DB Provider

Hi,

/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?).

Thanks
Wenyang

|||

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

Thanks
Michael

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

Dan

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!

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.

|||

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.

|||

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:

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 # 2:

Shah

Thursday, March 22, 2012

Access a database from a client application

Hi all...

I am writing a Windows Application which will be used by about 100 clients. (NT Authenticated)

The application is going to be used in a LAN enviroment. User who have access will download a VB.NET application from a web site.

Of course, I want to prevent any of the 100 users from using a tool such as Query Analysis or ODBC to connect directly to the database and modify data.

So, it appears the way to go is to the "Application Roles". For this to work, the application roll password has to be saved in the application to execute the sp_setapprole procedure.

Is there a way to secure this password? I am worried about a user somehow extracting the password from the application and connecting directly.

Am I on the right track here? Any ideas appreciated!

Thanks,

Forch

One method I have used is to create an admin system where you would create a Hash of the application name and version + a secret string.... Then use this hash as the password for the Application Role. Doing it this way will also make sure that the users will use the current version only..

|||

Note that if the user has full control over your application and can debug it, he can break any protection scheme that you build into it. It's worth obfuscating the password, as Glenn suggested, but this protection can be broken by a knowledgeable user.

Thanks
Laurentiu

Tuesday, March 20, 2012

Access 2003 and SQL Server 2005

I have a client who is having trouble modifying a SQL Server 2005 database
from Access 2003. I think they are trying to change not only data, but the
DB fields as well (tables, etc.)Access 2003 knows nothing of SQL Server 2005 and is not a proper tool to try
and change DDL. Why would they even think of doing that? Do they not have
SSMS?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Harold Shapiro" <HaroldShapiro@.discussions.microsoft.com> wrote in message
news:D5B22100-B390-4714-B4C9-A9ADBC483FF2@.microsoft.com...
>I have a client who is having trouble modifying a SQL Server 2005 database
> from Access 2003. I think they are trying to change not only data, but
> the
> DB fields as well (tables, etc.)
>|||=?Utf-8?B?SGFyb2xkIFNoYXBpcm8=?=<HaroldShapiro@.discussions.microsoft.com> wrote in
news:D5B22100-B390-4714-B4C9-A9ADBC483FF2@.microsoft.com:
> I have a client who is having trouble modifying a SQL Server 2005
> database from Access 2003. I think they are trying to change not only
> data, but the DB fields as well (tables, etc.)
Get them to use SQL Server Management Studio Express. It's a free tool to
management SQL Server.
--
spamhoneypot@.rogers.com (Do not e-mail)

Monday, March 19, 2012

Access / SQL Server or Filemaker from Paradox?

I've been programming in Paradox since DOS, but one mail-order database is getting rather large (100K client records, and associated orders, etc).

Our client wants us to move to Filemaker. In fact he'd like to have a 3rd party write the programme and me maintain it. The current package has been written & maintained by myself, with an awful lot lot going on in the Object PAL code.

I already have a copy (legal) of SQL Server & 10 licences for Access, and think that's a better option to re-develop in (although I could do with some training in both).

What are the pros & cons for each app, and what would people recommend. I'm going to cross-post into Access, Paradox & Filemaker forums to get an equal view.

Thanks

MarkIf he wants an efficient, robust, scalable solution than develop the database in SQL Server. Use MS Access as front-end for the time being, but you will eventually want to migrate to other interfaces.

access

oops.. excuse french..
i have a client that i have 2 db on server, but there are a lot of db, and
the security is present!. 1 is for live operation, 1 is for test. The
problem is that i cannot restore db on the test. But i have DBO and backup
operator access. So.. there is a way for me to restore the db and preserve
access for another db ?
thank!Hi,
Restore can be done by an user who got "dbcreator" fixed server role or
"sysadmin" fixed role.
How to assign "DBCREATOR" to a user:-
sp_addsrvrolemember <user>,'dbcreator'
Since you are doing the backup and restore in the same SQL Server machine,
the access previlages will not be a problem.
The access issues will only araise when you restore the database to a new
SQL Server, where your Ligin/User chain will be broken.
This case you can use the system procedure "sp_change_users_login" (refer
books online) to recreate the link.
Thanks
Hari
MCDBA
"Marty" <martin.foucault@.nospam.cgsi.qc.ca> wrote in message
news:#APmf3HPEHA.2740@.TK2MSFTNGP11.phx.gbl...
> oops.. excuse french..
> i have a client that i have 2 db on server, but there are a lot of db, and
> the security is present!. 1 is for live operation, 1 is for test. The
> problem is that i cannot restore db on the test. But i have DBO and
backup
> operator access. So.. there is a way for me to restore the db and
preserve
> access for another db ?
> thank!
>

Sunday, March 11, 2012

Accesing database after website deployment

I'm using sqlexpress on development machine and on client server. I'm building a webapp using membership and role provided for asp. In web.config I have definet membership provider and role provider to point to my database.

After web site deployment I get allways message : Failed to update database "c:\app\app.MDF" because the database is read-only, and I'm forced to apply rights for ASPNET. More than that somethimes I must re-register users.

I have tried to modify in web.config with <remove name="LocalSqlServer"/> <add name="LocalSqlServer"...> but I get errors if I try to design my dataset file:"Cannot get web application service".

TIA

Paul

To manipulate the database file, the account used to connect to the database must have read/write permission on the folder where the database file locates.|||Do you get other error message? I guess the problem is permission.|||

I think the problem is when I use SourceSafe, all folders in projects are maded read only.

Sometimes I get some error about NT Serveces does not have right on database, but that eror I have fised from SQL .

Paul

|||

I agree with you.You should add NT Service to access some folders in security.Change your database file property not to readonly.

Accelerating MS SQL Client

Does a product exist that might solve the following problem?

I have an application developed in VB6 that accesses data residing on a
central Microsoft SQL server in our datacenter. While I would argue
that this application stinks, I have been assigned to deploy it to the
laptops of about 100 people. These people travel to sites and connect
to the SQL server using a VPN connection and MDAC 2.8. In testing, I
found that the performance of this application suffers when connecting
to SQL via VPN, and since this application seems to hammer SQL when
loading menus in the least optimum way possible, the program will hang
while waiting for data.

Is there software I can install on the users' laptops that might
"accelerate" the connection to the SQL server? Perhaps this software
might be an alternative to the Microsoft-provided SQL client? I saw a
product called ZCache that seems to provide some caching functionality
for Oracle and MySQL databases, but nothing for MS SQL.Joe (josephtermine@.hotmail.com) writes:
> Does a product exist that might solve the following problem?
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
> Is there software I can install on the users' laptops that might
> "accelerate" the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.

That sounds like about an impossible thing for me. Then again, if there is
such a product for Oracle or MySQL, I guess it should be possible for
MS SQL Server. Yet then again, the accellerating effect of that product
may apply only to certain usage patterns.

Of course, if one analyses what this product is upto, it is possible
that you could implement a local cache, but it would certainly be
far cheaper to fix the application itself. It sounds if there are
tons of roundtrips and server-side cursors in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108413195.029686.70090@.z14g2000cwz.googlegro ups.com...
> Does a product exist that might solve the following problem?
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
> Is there software I can install on the users' laptops that might
> "accelerate" the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.

If you just connect VB6 bound datagrids to sql server and that's your lot
then you'll potentially get a hell of a lot of IO.
Plus the ones in the box didn't work so well, maybe they're patched or third
party or whatever.
It does sound that the app was developed without considering what's likely
over something like a 28k connection.

I've used vpn.
It can be very slow.
It can also have a limit on concurrent users, depending on how you're
vpn-ing.

Personally, I would have thought caching some data in a local database of
some sort a must.
At least for the sort of app this sounds like.
Whether that would best be an access mdb, full msde installation or whatever
would depend on the details.
vb.net and xml on the laptops would be another alternative but quite likely
involving a complete rewrite.

--
Regards,
Andy O'Neill|||I suspect that this application does use the VB6 grids out of the box,
and these are known to be resource-intensive. The newer VB.NET grids
seem to perform better in disconnected environments.

Anyway, there doesn't seem to be much we can do if we don't have access
to the VB6 source code.

Might there be a VPN accelerator we could try? Would this help us much?|||There are tons of round-trips with this application, but as we don't
have access to the VB6 source (this is an off-the-shelf product) there
might not be much we can do to fix the inefficiencies or implement a
local cache. There's a lot to think about.|||Joe (josephtermine@.hotmail.com) writes:
> There are tons of round-trips with this application, but as we don't
> have access to the VB6 source (this is an off-the-shelf product) there
> might not be much we can do to fix the inefficiencies or implement a
> local cache. There's a lot to think about.

The one thing I can think would be replication. Assuming that you can
update from the app, that would be merge replication.

And, no, I have experience of merge replication. (And not much of other
sorts of replication either.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 15 Feb 2005 10:33:18 -0800, Joe wrote:

> I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.
> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.
> Might there be a VPN accelerator we could try? Would this help us much?

Perhaps this could help?
http://sqlrelay.sourceforge.net/

It looks complicated, but it might suit you.

Replication of the database to a local MSDE might be another choice.|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108492398.045929.319930@.l41g2000cwc.googlegr oups.com...
>I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.

I used to use flexgrid to display data, user clicks on a row and gets a
bunch of text/combo boxes to work on it .
I worte my own code to update.
This was because of the bugs in the datagrid.
You might want to investigate those.
I forget but there are circs when updates just didn't happen.

WIth dotnet I use the datagrids and they're good.
The dataadaptor and (disconnected) dataset functionality is very useful.
There would be issues meant added code in an app where a salesman wants to
use the thing completely disconnected from the database.

> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.

Well... that's a mistake someone's made right there.

> Might there be a VPN accelerator we could try? Would this help us much?

No idea mate.

--
Regards,
Andy O'Neill

Thursday, March 8, 2012

abrupt client disconnect

Hi,
If the client is disconnected abruptly from the server, from where do we get
the log?
how do we get the data from the server that the particular client is
disconnected abruptly?
Thanks
VanithaHi
You will need to code your application to handle errors returned from the
calls that interface with the database e.g. if you use ado check out the
section "Handling Errors and Messages in ADO" in books online and the sample
application "C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ADO\VB\
Intro" also check out the ADO
programmers guide
http://msdn.microsoft.com/library/d...orhandling.asp.
You may find more information regarding the reason why the connection failed
in the SQL Server error log, the event log on the SQL server server, or the
clients event log.
If disconnected you will need to re-connect and start your processing again.
SQL server will usually at some point detect your previous connection has
been aborted and rollback the work.
HTH
John
"vanitha" wrote:

> Hi,
> If the client is disconnected abruptly from the server, from where do we g
et
> the log?
> how do we get the data from the server that the particular client is
> disconnected abruptly?
> Thanks
> Vanitha

Tuesday, March 6, 2012

About the sql 2005 client software

Hello,

Assume that I have a ms sql 2005 server running,

and the clients wants to connect to the database through their Windows XP desktops,

is there any sql 2005 client software that can install on the clients desktop for them to use? (e.g. like MySQLFront or PLSQL)

so that they can input sql statement or store procedure on the client side to make query to the database.

Does any things should notice during installation?

Thanks for helping!!

Ah, if you write a program (or use a program) that can create an OLE DB or .NET SqlClient connection, you can connect to SQL Server 2005--assuming that it's enabled to see the LAN (it's disabled by default). Even VB6 or older programs can use the SQL Native Client with an OLE DB-enabled application to connect to SQL Server 2005.

hth

|||

Hi William,

Do you know is this possible for using a free Microsoft SQL Server 2005 Express Edition as a client side interface, intall it on Windows XP. Use it to connect to a sql 2005 standard server to make query and update information on table or views, by using the user's sql login name & password of the sql 2005 standard server to authenticate.

Or I must use win2003 remote desktop with terminal server client license to let the users to remote to the sql 2005 standard server directly & logon locally to achieve this goal.

Thanks!

|||Again, this is a very fundemental question that can best be addressed by some research. No, you don't need SQL Server Express edition installed on the client systems if you intend to write (or use) and application that connects to a remote server. Try picking up a copy of my Hitchhiker's Guide to Visual Basic and SQL Server 6th Edition that discusses these basic concepts.

Tuesday, March 27, 2012

Access DB2 on AS/400 using IBM OLE DB Provider

Access DB2 on AS/400 using IBM OLE DB Provider

Access DB2 on AS/400 using IBM OLE DB Provider

Access DB2 on AS/400 using IBM OLE DB Provider

Access DB2 on AS/400 using IBM OLE DB Provider

Access DB2 on AS/400 using IBM OLE DB Provider

Access DB2 on AS/400 using IBM OLE DB Provider

Thursday, March 22, 2012

Access a database from a client application

Tuesday, March 20, 2012

Access 2003 and SQL Server 2005

Monday, March 19, 2012

Access / SQL Server or Filemaker from Paradox?

access

Sunday, March 11, 2012

Accesing database after website deployment

Accelerating MS SQL Client

Thursday, March 8, 2012

abrupt client disconnect

Tuesday, March 6, 2012

About the sql 2005 client software

SQL About AMO

Blog Archive

About Me