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

ASP error while accessing the website

Hi
I get different errors when I access the same web page....This page opens a
ADO connection....
First time --
__________________________________________________ ___
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/common/common.asp, line 65
_______________________________________________--
Another time...I got an Invalid connection error..
Can someone tell me what the problem really is...The website was working
perfectly fine until few days agao..There were no changes to the ASP code...
Thanks & Regards
Imtiaz
sql client changed from tcp\ip to named pipes ?
nic card setting set from 100mb Full to "Auto"
?
Greg Jackson
PDX, Oregon
|||Timeout occurs if you don't take network congestion (i.e. lag) into account.
Double check your connection string to see if you have given enough time for
sql connection (default is 15 sec) and sql command execution (default is 30
sec).
-oj
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
> Hi
> I get different errors when I access the same web page....This page opens
> a
> ADO connection....
> First time --
> __________________________________________________ ___
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> /common/common.asp, line 65
> _______________________________________________--
> Another time...I got an Invalid connection error..
> Can someone tell me what the problem really is...The website was working
> perfectly fine until few days agao..There were no changes to the ASP
> code...
>
> Thanks & Regards
> Imtiaz
>
|||Problem has been solved...Web Server wasn't able to ping to SQL Server
properly...
Thanks for your replies...
"oj" wrote:

> Timeout occurs if you don't take network congestion (i.e. lag) into account.
> Double check your connection string to see if you have given enough time for
> sql connection (default is 15 sec) and sql command execution (default is 30
> sec).
> --
> -oj
>
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
>
>

ASP error while accessing the website

Hi
I get different errors when I access the same web page....This page opens a
ADO connection....
First time --
________________________________________
_____________
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/common/common.asp, line 65
________________________________________
_______--
Another time...I got an Invalid connection error..
Can someone tell me what the problem really is...The website was working
perfectly fine until few days agao..There were no changes to the ASP code...
Thanks & Regards
Imtiazsql client changed from tcp\ip to named pipes ?
nic card setting set from 100mb Full to "Auto"
?
Greg Jackson
PDX, Oregon|||Timeout occurs if you don't take network congestion (i.e. lag) into account.
Double check your connection string to see if you have given enough time for
sql connection (default is 15 sec) and sql command execution (default is 30
sec).
-oj
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
> Hi
> I get different errors when I access the same web page....This page opens
> a
> ADO connection....
> First time --
> ________________________________________
_____________
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> /common/common.asp, line 65
> ________________________________________
_______--
> Another time...I got an Invalid connection error..
> Can someone tell me what the problem really is...The website was working
> perfectly fine until few days agao..There were no changes to the ASP
> code...
>
> Thanks & Regards
> Imtiaz
>|||Problem has been solved...Web Server wasn't able to ping to SQL Server
properly...
Thanks for your replies...
"oj" wrote:

> Timeout occurs if you don't take network congestion (i.e. lag) into accoun
t.
> Double check your connection string to see if you have given enough time f
or
> sql connection (default is 15 sec) and sql command execution (default is 3
0
> sec).
> --
> -oj
>
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
>
>

ASP data access difficulties

I'm using ASP pages to access a Microsoft SQL 2005 SP1 database server for information that populates dropdown menus. I ran into an interesting problem. We can query all existing data without a problem (all expected rows return and correctly populate the dropdowns).

I manually add a new row to the database table for a dropdown. I can manually query the database from the SQL2005 management tool, and the new records are included in the results.

I load the ASP page, and the newly added records never appear in the dropdown... only the old, existing data.

What could cause this? The queries from ASP are the same as the queries I make directly from the SQL server.

Could you please post the part of your code that is filling the dropdown boxes ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

-relevant snip-

<% SQL = "sp_GetVMFarmList"
set rs = conn.execute(SQL) %>
<select name="VMFarm" class="InputBox">
<option></option>
<% while not rs.eof
strSelected = ""
if intFarmID <> "" then
if cint(intFarmID) = cint(rs("FarmID")) then
strSelected = "Selected"
end if
end if %>
<option <%=strSelected %> value="<%=rs("FarmID")%>"><%=rs("FarmName")%></option>

<% rs.movenext
wend %>
</select>

|||

Any page directives or server settings to cache the pages ? BTW you should not use sp_ prefixes as SQL will *always* do a recompilation to the procedures as the prefix is reserved for system procedures. Better use usp_Something or spSomething.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||No caching, nothing on the web server (IIS 6) has changed. I restarted the IIS service several times to make sure it wasn't trying to hold onto any data|||

Probably a silly question, but have you checked from another computer to make sure your computer isn't caching the data?

|||

Oh yes... in fact it was several other users reporting that they couldn't see the new data I added to the tables. they all use different PCs.

|||

I assume you've made a backup of your data If not, that would be my first step right now. Now I'm going to go into what I call "guess mode because I don't really know what's going on"....

Then I would start playing with it, see if the website even recognizes any sort of change, such as removing some rows. If nothing is responding I would drop the table and reload it and see if that helps. However, like I said, you definitely need to have a backup of your data because you never know how the reloading stage might go.

|||When you say "I can manually query the database from SQL2005 management tool", you mean you run sp_GetVMFarmList, or you are doing the query that is defined in the stored procedure?|||

Both

|||

try Response.Expires = 0 at the top of the ASP page, to make sure you don't have some additional caching. But probably you should now examine your update code, instead of the dropdown code.

ASP data access difficulties

I'm using ASP pages to access a Microsoft SQL 2005 SP1 database server for information that populates dropdown menus. I ran into an interesting problem. We can query all existing data without a problem (all expected rows return and correctly populate the dropdowns).

I manually add a new row to the database table for a dropdown. I can manually query the database from the SQL2005 management tool, and the new records are included in the results.

I load the ASP page, and the newly added records never appear in the dropdown... only the old, existing data.

What could cause this? The queries from ASP are the same as the queries I make directly from the SQL server.

Could you please post the part of your code that is filling the dropdown boxes ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

-relevant snip-

<% SQL = "sp_GetVMFarmList"
set rs = conn.execute(SQL) %>
<select name="VMFarm" class="InputBox">
<option></option>
<% while not rs.eof
strSelected = ""
if intFarmID <> "" then
if cint(intFarmID) = cint(rs("FarmID")) then
strSelected = "Selected"
end if
end if %>
<option <%=strSelected %> value="<%=rs("FarmID")%>"><%=rs("FarmName")%></option>

<% rs.movenext
wend %>
</select>

|||

Any page directives or server settings to cache the pages ? BTW you should not use sp_ prefixes as SQL will *always* do a recompilation to the procedures as the prefix is reserved for system procedures. Better use usp_Something or spSomething.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

No caching, nothing on the web server (IIS 6) has changed. I restarted the IIS service several times to make sure it wasn't trying to hold onto any data|||

Probably a silly question, but have you checked from another computer to make sure your computer isn't caching the data?

|||

Oh yes... in fact it was several other users reporting that they couldn't see the new data I added to the tables. they all use different PCs.

|||

I assume you've made a backup of your data If not, that would be my first step right now. Now I'm going to go into what I call "guess mode because I don't really know what's going on"....

Then I would start playing with it, see if the website even recognizes any sort of change, such as removing some rows. If nothing is responding I would drop the table and reload it and see if that helps. However, like I said, you definitely need to have a backup of your data because you never know how the reloading stage might go.

|||When you say "I can manually query the database from SQL2005 management tool", you mean you run sp_GetVMFarmList, or you are doing the query that is defined in the stored procedure?

|||

Both

|||

try Response.Expires = 0 at the top of the ASP page, to make sure you don't have some additional caching. But probably you should now examine your update code, instead of the dropdown code.

ASP COM and database access

Hello All,

I am currently creating a browser interface for our thick client which
was built on Visual Basic using COM ( a three tier environment). We
support environments where the SQL database server is separate from the
application server.

The problem I am facing is this.

When I try to access the database server from the ASP page through the
COM object, I get access denied. This is because the COM is running
under the IWAM account and it does not have access to the database
tables. Since this is a system account I cannot add it to the database
server which is on a different machine possible a different domain too.
When I gave anonymous access to the database server, it worked fine but
this is obvously not a solution I want. The other option is to create a
COM+ wrapper for every objects (possibly 100)which can be run under a
specific account. But this will be a huge task.
Are there any other options? Any ideas are appreciated.
Thanks
SatishHi.I think this id COM fault not Database server.

Try to change access permissions using "dcomcnfg". On the Server where
IIS running.
Add IWAM account to allow access and to launch com objects.
It woks fine in my case. And i had the same situation.

sss wrote:
> Hello All,
>
> I am currently creating a browser interface for our thick client
which
> was built on Visual Basic using COM ( a three tier environment). We
> support environments where the SQL database server is separate from
the
> application server.
>
> The problem I am facing is this.
>
> When I try to access the database server from the ASP page through
the
> COM object, I get access denied. This is because the COM is running
> under the IWAM account and it does not have access to the database
> tables. Since this is a system account I cannot add it to the
database
> server which is on a different machine possible a different domain
too.
> When I gave anonymous access to the database server, it worked fine
but
> this is obvously not a solution I want. The other option is to create
a
> COM+ wrapper for every objects (possibly 100)which can be run under a
> specific account. But this will be a huge task.
> Are there any other options? Any ideas are appreciated.
> Thanks
> Satishsql

ASP and SQL Server

I am moving from Access 2000 to SQL Server. I am using ASP. Is there anything I should change in the ASP while migrating? Any ideas would haelp. Thanks.Hi,
I passed through the same situation, so I can be a bit of usefull for you.

Two changes can be done when moving from Access to SQL Server.

The first one is required, it is the connection string to oped the database.

The second is optional, you can move some of the procedures to the SQL Server database it self as a Stored Procedures so things will run faster. This step is not required but it helps.

Now, start by changing the connection string and use the IMPORT EXPORT wizard in SQL Server to transfer your existing data, or use the UPSIZE wizard in Access 2000 to transfer your existing data.

Hope this helps as a first step.

good luck|||Thanks a lot, this really helps.
So do you think it's better to move only the data to SQL Server instead of migrating everything?
I am using forms to enter data, and I am wondering if it is better to leave the forms in Access to enter data in SQL Server. Thanks.|||Well, I am glad that my experience could be usefull for you.
As I know, you can't have forms in SQL Server, so you can migrate:
Databases
Tables
Queries

Thats all

If you have more data to be intered you can use your forms to enter it in Access DB first then, migrate it to SQL Server DB.

Good luck

asp / sql server dates in recordsets

Hey,

I've got an Classic ASP app pulling some records out of a DB, which
worked fine when it was Access. When we upped to SQL Server, all of a
sudden, when the Recordsets came back, all the dates were empty, even
though the data was showing up in the DB.

About a year ago (only code asp apps once and a while), I remember
seeing this and finding something that just had something to do with the
order in which something was being referenced, but I just can't for the
life of me remember what it was.

The Dates are set as datetime, length of 8.

Anyone know why this happens and a solution?

many thanks,

rob"rob" <rob@.pleaseremovespamcherny.com> wrote in message
news:XYadnScIEbMPLpvdRVn-uA@.giganews.com...
> Hey,
> I've got an Classic ASP app pulling some records out of a DB, which
> worked fine when it was Access. When we upped to SQL Server, all of a
> sudden, when the Recordsets came back, all the dates were empty, even
> though the data was showing up in the DB.
> About a year ago (only code asp apps once and a while), I remember
> seeing this and finding something that just had something to do with the
> order in which something was being referenced, but I just can't for the
> life of me remember what it was.
> The Dates are set as datetime, length of 8.
> Anyone know why this happens and a solution?
> many thanks,
> rob

It's not really possible to give a good answer without more information. Do
you have an example of a query which returns data in Query Analyzer, but not
in ASP?

You might also look at www.aspfaq.com, if you haven't already - there are
quite a lot of articles about handling dates.

Simon

asking for opinions

Hello,
I want to collect system information (e.g. who have what access to what
databases, how much space has been used by each database, what version of the
service pack installed, etc.) of our SQL 2000 server. I can get on the SQL
server and use the system stored procedures to get those information manually
every time I need it. But I also need to show the information to the
managers and other system admins who don't deal with SQL server very often.
So I'm thinking about showing those information on the web which is only
accessible to a certain group of people. How people usually do it? If
coding is involved, which language (PHP, Perl ,etc) has a better handle of it
in terms of SQL server? I know some people use email notification to send
reports. But I prefer web. By the way, we don't use any reporting software
here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
Thanks,
Bing
I've done this at my last three jobs. Build the infrastructure for the info
you want to collect and use linked servers, DTS and a central DB to collect
and store. Summarize it through stored procs that are called via the web.
For ultra critical items, generate email alerts such as the creation of
objects in a locked down environment.
"bing" wrote:

> Hello,
> I want to collect system information (e.g. who have what access to what
> databases, how much space has been used by each database, what version of the
> service pack installed, etc.) of our SQL 2000 server. I can get on the SQL
> server and use the system stored procedures to get those information manually
> every time I need it. But I also need to show the information to the
> managers and other system admins who don't deal with SQL server very often.
> So I'm thinking about showing those information on the web which is only
> accessible to a certain group of people. How people usually do it? If
> coding is involved, which language (PHP, Perl ,etc) has a better handle of it
> in terms of SQL server? I know some people use email notification to send
> reports. But I prefer web. By the way, we don't use any reporting software
> here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
> Thanks,
> Bing

asking for opinions

Hello,
I want to collect system information (e.g. who have what access to what
databases, how much space has been used by each database, what version of th
e
service pack installed, etc.) of our SQL 2000 server. I can get on the SQL
server and use the system stored procedures to get those information manuall
y
every time I need it. But I also need to show the information to the
managers and other system admins who don't deal with SQL server very often.
So I'm thinking about showing those information on the web which is only
accessible to a certain group of people. How people usually do it? If
coding is involved, which language (PHP, PERL ,etc) has a better handle of i
t
in terms of SQL server? I know some people use email notification to send
reports. But I prefer web. By the way, we don't use any reporting software
here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
Thanks,
BingI've done this at my last three jobs. Build the infrastructure for the info
you want to collect and use linked servers, DTS and a central DB to collect
and store. Summarize it through stored procs that are called via the web.
For ultra critical items, generate email alerts such as the creation of
objects in a locked down environment.
"bing" wrote:

> Hello,
> I want to collect system information (e.g. who have what access to what
> databases, how much space has been used by each database, what version of
the
> service pack installed, etc.) of our SQL 2000 server. I can get on the SQ
L
> server and use the system stored procedures to get those information manua
lly
> every time I need it. But I also need to show the information to the
> managers and other system admins who don't deal with SQL server very often
.
> So I'm thinking about showing those information on the web which is only
> accessible to a certain group of people. How people usually do it? If
> coding is involved, which language (PHP, PERL ,etc) has a better handle of
it
> in terms of SQL server? I know some people use email notification to send
> reports. But I prefer web. By the way, we don't use any reporting softwa
re
> here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
> Thanks,
> Bing

asking for opinions

Hello,
I want to collect system information (e.g. who have what access to what
databases, how much space has been used by each database, what version of the
service pack installed, etc.) of our SQL 2000 server. I can get on the SQL
server and use the system stored procedures to get those information manually
every time I need it. But I also need to show the information to the
managers and other system admins who don't deal with SQL server very often.
So I'm thinking about showing those information on the web which is only
accessible to a certain group of people. How people usually do it? If
coding is involved, which language (PHP, Perl ,etc) has a better handle of it
in terms of SQL server? I know some people use email notification to send
reports. But I prefer web. By the way, we don't use any reporting software
here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
Thanks,
BingI've done this at my last three jobs. Build the infrastructure for the info
you want to collect and use linked servers, DTS and a central DB to collect
and store. Summarize it through stored procs that are called via the web.
For ultra critical items, generate email alerts such as the creation of
objects in a locked down environment.
"bing" wrote:
> Hello,
> I want to collect system information (e.g. who have what access to what
> databases, how much space has been used by each database, what version of the
> service pack installed, etc.) of our SQL 2000 server. I can get on the SQL
> server and use the system stored procedures to get those information manually
> every time I need it. But I also need to show the information to the
> managers and other system admins who don't deal with SQL server very often.
> So I'm thinking about showing those information on the web which is only
> accessible to a certain group of people. How people usually do it? If
> coding is involved, which language (PHP, Perl ,etc) has a better handle of it
> in terms of SQL server? I know some people use email notification to send
> reports. But I prefer web. By the way, we don't use any reporting software
> here, like Crystal Report. Anyway, I'd appreciate any ideas and advice.
> Thanks,
> Bingsql

Asking

At Ms Access, I can export data to text or excel with only to write:
SELECT * INTO TD in 'C:' [Text;]
From TS
Now, Can SQL Server 2000 do like this? IF can, How is its syntax?Bpk,
Here is an example to work with:
EXEC master..xp_cmdshell 'osql -E -dPubs -Q"select * from
authors" -o"C:\authors.txt"'
You can also use BCP (out) and DTS.
HTH
Jerry
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:ulzUGe2xFHA.2652@.TK2MSFTNGP14.phx.gbl...
> At Ms Access, I can export data to text or excel with only to write:
> SELECT * INTO TD in 'C:' [Text;]
> From TS
> Now, Can SQL Server 2000 do like this? IF can, How is its syntax?
>|||This will work for the publisher table in the Pubs DB
exec master..xp_cmdshell 'bcp pubs.dbo.publishers out "c:\files.txt" -S
{YourServerNameHere} -T -t "," -c'
or just from the command line bcp pubs.dbo.publishers out "c:\files.txt" -S
{YourServerNameHere} -T -t "," -c
http://sqlservercode.blogspot.com/
"Bpk. Adi Wira Kusuma" wrote:

> At Ms Access, I can export data to text or excel with only to write:
> SELECT * INTO TD in 'C:' [Text;]
> From TS
> Now, Can SQL Server 2000 do like this? IF can, How is its syntax?
>
>

asdb - what is it? Shold I back it up?

We developed a DB for our customer using MS Access and upsized it to
SQL Server. Our customer does daily back up of his DB and he asked me
whether or not 'asdb' should be backed up as well.

My question is: what is 'asdb' and whether or not it should be
backed up. Any information or web links will be appreciated.

Thanks,
Mark.mfine@.array.ca wrote:

Quote:

Originally Posted by

We developed a DB for our customer using MS Access and upsized it to
SQL Server. Our customer does daily back up of his DB and he asked me
whether or not 'asdb' should be backed up as well.
>
My question is: what is 'asdb' and whether or not it should be
backed up. Any information or web links will be appreciated.

Google turns up nothing obviously relevant. Ask the customer to
show you 'asdb' - maybe it's the name of another SQL or Access DB,
or maybe they meant something else but botched the name.|||Thaks.

Ed Murphy wrote:

Quote:

Originally Posted by

mfine@.array.ca wrote:
>

Quote:

Originally Posted by

We developed a DB for our customer using MS Access and upsized it to
SQL Server. Our customer does daily back up of his DB and he asked me
whether or not 'asdb' should be backed up as well.

My question is: what is 'asdb' and whether or not it should be
backed up. Any information or web links will be appreciated.

>
Google turns up nothing obviously relevant. Ask the customer to
show you 'asdb' - maybe it's the name of another SQL or Access DB,
or maybe they meant something else but botched the name.

|||mfine@.array.ca wrote:

Quote:

Originally Posted by

We developed a DB for our customer using MS Access and upsized it to
SQL Server. Our customer does daily back up of his DB and he asked me
whether or not 'asdb' should be backed up as well.
>
My question is: what is 'asdb' and whether or not it should be
backed up. Any information or web links will be appreciated.

Is it perhaps "msdb"? "The msdb database is used by SQL Server, SQL
Server Enterprise Manager, and SQL Server Agent to store data,
including scheduling information and backup and restore history
information." (SQL Server 2000)

If that sounds like something you will make use of, then Books Online
has tips on backing it up. If that information isn't useful in your
disaster recovery plan, don't bother - you can use this database to
manage backups and automated maintenance, but you can do without it,
restore your customer's data onto a newly installed server.

We presume your customer's data is in a specially created database and
not in one of the built-in ones.

I suppose database creation date also is likely to reflect whether the
database was created during installation of SQL Server, or when you
started using it. But if the database was yours, then you'd know,
surely?

Is someone else keeping data on the same server?|||I believe this is the database that Arcserve backup for windows uses.
Its a product by Computer Associates.
HTH
Rob
(open it up. if you see tables like asfilename, ashost, asjob,
asjobmap,
then this is what it is)

mfine@.array.ca wrote:

Quote:

Originally Posted by

We developed a DB for our customer using MS Access and upsized it to
SQL Server. Our customer does daily back up of his DB and he asked me
whether or not 'asdb' should be backed up as well.
>
My question is: what is 'asdb' and whether or not it should be
backed up. Any information or web links will be appreciated.
>
Thanks,
Mark.

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

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

AS/400 Linked Server Error

Hello,
I have installed IBM AS/400 Client Access on my machine and then added a new
ODBC Data Source.
I have been working with this ODBC connection on several DTS packages and
everything has worked just fine.
Right now, I need to access some information on my AS/400 server from
several stored procedures on my SQL Server. I already configured a Linked
Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
Driver.
When I click the Tables folder under the new linked server, I can browse
through all the tables on the 400 Database. But when I execute any query
(using OPENQUERY or 4-part names in a normal query) I get the following error:
*******************************************+++
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32 bits)] Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
0x80004005: ].
******************************************
Why is this happening? What is the error? Is there anything I am doing
wrong? I have been "fighting" with this problem for several hours.
Thanks a lot, in advance, for all the help you can give me.
is your ib client access the most updated version?
"Gabriel Giraldo" wrote:

> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a new
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following error:
> *******************************************+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
> 0x80004005: ].
> ******************************************
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>
|||Hi,
0x80004005 usually has something to do with permission issues. You might
want to check that out first.
Regards,
Steve
"Gabriel Giraldo" <GabrielGiraldo@.discussions.microsoft.com> wrote in
message news:C84D15C4-6087-4377-B66B-1F4C3EDEF7C2@.microsoft.com...
> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a
> new
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following
> error:
> *******************************************+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows
> returned
> 0x80004005: ].
> ******************************************
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>