SQL Server 2000 on Windows 2003 SP1 and IIS6
I'm getting the following error from my ASP page:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Formal parameter '@.mname' was defined as OUTPUT but the actual parameter
not declared OUTPUT.
However, the formal parameter '@.mname' was used in a previous stored
procedure accessed by the same command object and that parameter has been
been deleted prior to the call.
Here's the DDL for the procedure:
CREATE PROCEDURE InsertAndIdentifyNewStarter
@.peopleID INTEGER,
@.newstarterID BIGINT OUTPUT
AS
INSERT INTO newstarter (peopleID)
VALUES (@.peopleID)
SELECT @.newstarterID = SCOPE_IDENTITY()
GO
And the code snippet that's giving the problem:
Dim cmd, ln
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = "Provider=" & DBPROVIDER & ";" & CONNECTIONSTRING
.CommandType = adCmdStoredProc
.CommandText = "InsertAndIdentifyPerson"
.Parameters.Append .CreateParameter("@.fname", adVarChar, _
adParamInput, 100, firstname)
.Parameters.Append .CreateParameter("@.mname", adVarChar, _
adParamInput, 100, middlename)
.Parameters.Append .CreateParameter("@.sname", adVarChar, _
adParamInput, 100, lastname)
.Parameters.Append .CreateParameter("@.fullname", adVarChar, _
adParamInput, 200, firstname & " " & lastname)
.Parameters.Append .CreateParameter("@.datestarted", adDBDate, _
adParamInput, 10, DBStartDate)
.Parameters.Append .CreateParameter("@.enabled", adInteger, _
adParamInput, 10, 1)
.Parameters.Append .CreateParameter("@.existsinaccounts", _
adInteger, adParamInput, 10, 0)
.Parameters.Append .CreateParameter("@.loggedon", adInteger, _
adParamInput, 10, 0)
.Parameters.Append .CreateParameter("@.itstatus", adInteger, _
adParamInput, 10, 5) ' 5 = New Starter
.Parameters.Append .CreateParameter("@.pID", adInteger, _
adParamOutput, 10)
.Execute ln, , adExecuteNoRecords
pID = .Parameters("@.pID")
' Delete the parameters so that we can reuse the command object
While .Parameters.Count > 0
.Parameters.Delete 0
Wend
' Create a newstarter record for this person
.CommandText = "InsertAndIdentifyPerson"
.Parameters.Append .CreateParameter("@.peopleID", adBigInt, _
adParamInput, 10, pID)
.Parameters.Append .CreateParameter("@.newstarterID", _
adBigInt, adParamOutput, 10)
.Execute ln, , adExecuteNoRecords
' ** The preceding line produces the error **
newstarterID = .Parameters("@.newstarterID")
End With
Set cmd = Nothing
Any ideas what I'm doing wrong or what the problem is?
TIA,
Geoff> CREATE PROCEDURE InsertAndIdentifyNewStarterd">
> snip
> .CommandText = "InsertAndIdentifyPerson"
It looks to me like you are executing the wrong proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"Geoff Lane" <geoff@.nospam.gjctech.co.uk> wrote in message
news:Xns97EA7C83661FBgjctcswxnsrt@.207.46.248.16...
> SQL Server 2000 on Windows 2003 SP1 and IIS6
> I'm getting the following error from my ASP page:
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Formal parameter '@.mname' was defined as OUTPUT but the actual parameter
> not declared OUTPUT.
> However, the formal parameter '@.mname' was used in a previous stored
> procedure accessed by the same command object and that parameter has been
> been deleted prior to the call.
> Here's the DDL for the procedure:
> CREATE PROCEDURE InsertAndIdentifyNewStarter
> @.peopleID INTEGER,
> @.newstarterID BIGINT OUTPUT
> AS
> INSERT INTO newstarter (peopleID)
> VALUES (@.peopleID)
> SELECT @.newstarterID = SCOPE_IDENTITY()
> GO
> And the code snippet that's giving the problem:
> Dim cmd, ln
> Set cmd = Server.CreateObject("ADODB.Command")
> With cmd
> .ActiveConnection = "Provider=" & DBPROVIDER & ";" & CONNECTIONSTRING
> .CommandType = adCmdStoredProc
> .CommandText = "InsertAndIdentifyPerson"
> .Parameters.Append .CreateParameter("@.fname", adVarChar, _
> adParamInput, 100, firstname)
> .Parameters.Append .CreateParameter("@.mname", adVarChar, _
> adParamInput, 100, middlename)
> .Parameters.Append .CreateParameter("@.sname", adVarChar, _
> adParamInput, 100, lastname)
> .Parameters.Append .CreateParameter("@.fullname", adVarChar, _
> adParamInput, 200, firstname & " " & lastname)
> .Parameters.Append .CreateParameter("@.datestarted", adDBDate, _
> adParamInput, 10, DBStartDate)
> .Parameters.Append .CreateParameter("@.enabled", adInteger, _
> adParamInput, 10, 1)
> .Parameters.Append .CreateParameter("@.existsinaccounts", _
> adInteger, adParamInput, 10, 0)
> .Parameters.Append .CreateParameter("@.loggedon", adInteger, _
> adParamInput, 10, 0)
> .Parameters.Append .CreateParameter("@.itstatus", adInteger, _
> adParamInput, 10, 5) ' 5 = New Starter
> .Parameters.Append .CreateParameter("@.pID", adInteger, _
> adParamOutput, 10)
> .Execute ln, , adExecuteNoRecords
> pID = .Parameters("@.pID")
> ' Delete the parameters so that we can reuse the command object
> While .Parameters.Count > 0
> .Parameters.Delete 0
> Wend
> ' Create a newstarter record for this person
> .CommandText = "InsertAndIdentifyPerson"
> .Parameters.Append .CreateParameter("@.peopleID", adBigInt, _
> adParamInput, 10, pID)
> .Parameters.Append .CreateParameter("@.newstarterID", _
> adBigInt, adParamOutput, 10)
> .Execute ln, , adExecuteNoRecords
> ' ** The preceding line produces the error **
> newstarterID = .Parameters("@.newstarterID")
> End With
> Set cmd = Nothing
> Any ideas what I'm doing wrong or what the problem is?
> TIA,
> --
> Geoff|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:elcHsWflGHA.5044@.TK2MSFTNGP02.phx.gbl:
> It looks to me like you are executing the wrong proc.
D'Oh! That'll teach me to cut-and-paste code :(
Thanks - you are correct!
Geoff
Showing posts with label provider. Show all posts
Showing posts with label provider. Show all posts
Thursday, March 22, 2012
ASP 3.0 Error on SQL Server 2005
----
Microsoft OLE DB Provider for SQL Server error '80040e14'
The query processor ran out of internal resources and could not produce a
query plan.
This is a rare event and only expected for extremely complex queries or
queries that reference a very large number of tables or partitions.
Please simplify the query. If you believe you have received this message in
error, contact Customer Support Services for more information.
----
Has anyone else seen the above error message in an Active Server Page 3.0,
running on top of SQL Server 2005?
(The database was restored to a new 2005 database with a complete backup
from the 2000 database.)
The query that causes this error has run fine in an ASP 3.0 on top of SQL
Server 2000 on the same database, on an older Windows server.
The query runs fine in SQL Server 2005 Management Studio (and SQL Server
2000 Query Analyzer too). It returns 556 rows. It runs in 15 seconds on
the new Dell PowerEdge 2850 with SQL Server 2005. It runs in 3 seconds on
the old Dell server with SQL Server 2000.
I used the 2005 Database Engine Tuning Wizard, and applied the suggested
indexes. That did not help. The query still runs in 15 seconds in
Management Studio and still breaks in the ASP.
Any ideas, anyone?!
JimJim Moon (please.reply@.group) writes:
> Has anyone else seen the above error message in an Active Server Page 3.0,
> running on top of SQL Server 2005?
> (The database was restored to a new 2005 database with a complete backup
> from the 2000 database.)
> The query that causes this error has run fine in an ASP 3.0 on top of SQL
> Server 2000 on the same database, on an older Windows server.
> The query runs fine in SQL Server 2005 Management Studio (and SQL Server
> 2000 Query Analyzer too). It returns 556 rows. It runs in 15 seconds on
> the new Dell PowerEdge 2850 with SQL Server 2005. It runs in 3 seconds on
> the old Dell server with SQL Server 2000.
> I used the 2005 Database Engine Tuning Wizard, and applied the suggested
> indexes. That did not help. The query still runs in 15 seconds in
> Management Studio and still breaks in the ASP.
Without knowing the tables or indexes, it is difficult to say. But, one
thing: did you run UPDATE STATISTICS WITH FULLSCAN when you had restored
the database on SQL 2005?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9771753A2067Yazorman@.127.0.0.1...
> Jim Moon (please.reply@.group) writes:
> Without knowing the tables or indexes, it is difficult to say. But, one
> thing: did you run UPDATE STATISTICS WITH FULLSCAN when you had restored
> the database on SQL 2005?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Thank you, Erland!
UPDATE STATISTICS <table_name> WITH FULLSCAN
did the trick.
Microsoft OLE DB Provider for SQL Server error '80040e14'
The query processor ran out of internal resources and could not produce a
query plan.
This is a rare event and only expected for extremely complex queries or
queries that reference a very large number of tables or partitions.
Please simplify the query. If you believe you have received this message in
error, contact Customer Support Services for more information.
----
Has anyone else seen the above error message in an Active Server Page 3.0,
running on top of SQL Server 2005?
(The database was restored to a new 2005 database with a complete backup
from the 2000 database.)
The query that causes this error has run fine in an ASP 3.0 on top of SQL
Server 2000 on the same database, on an older Windows server.
The query runs fine in SQL Server 2005 Management Studio (and SQL Server
2000 Query Analyzer too). It returns 556 rows. It runs in 15 seconds on
the new Dell PowerEdge 2850 with SQL Server 2005. It runs in 3 seconds on
the old Dell server with SQL Server 2000.
I used the 2005 Database Engine Tuning Wizard, and applied the suggested
indexes. That did not help. The query still runs in 15 seconds in
Management Studio and still breaks in the ASP.
Any ideas, anyone?!
JimJim Moon (please.reply@.group) writes:
> Has anyone else seen the above error message in an Active Server Page 3.0,
> running on top of SQL Server 2005?
> (The database was restored to a new 2005 database with a complete backup
> from the 2000 database.)
> The query that causes this error has run fine in an ASP 3.0 on top of SQL
> Server 2000 on the same database, on an older Windows server.
> The query runs fine in SQL Server 2005 Management Studio (and SQL Server
> 2000 Query Analyzer too). It returns 556 rows. It runs in 15 seconds on
> the new Dell PowerEdge 2850 with SQL Server 2005. It runs in 3 seconds on
> the old Dell server with SQL Server 2000.
> I used the 2005 Database Engine Tuning Wizard, and applied the suggested
> indexes. That did not help. The query still runs in 15 seconds in
> Management Studio and still breaks in the ASP.
Without knowing the tables or indexes, it is difficult to say. But, one
thing: did you run UPDATE STATISTICS WITH FULLSCAN when you had restored
the database on SQL 2005?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9771753A2067Yazorman@.127.0.0.1...
> Jim Moon (please.reply@.group) writes:
> Without knowing the tables or indexes, it is difficult to say. But, one
> thing: did you run UPDATE STATISTICS WITH FULLSCAN when you had restored
> the database on SQL 2005?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Thank you, Erland!
UPDATE STATISTICS <table_name> WITH FULLSCAN
did the trick.
Sunday, March 11, 2012
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 Thanks
"
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.
Wenyang
Wenyang, thanks for your reply... It does not sound too uplifting. 
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
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 Thanks
"
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.
Wenyang
Wenyang, thanks for your reply... It does not sound too uplifting.
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
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.
|||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 Thanks
"
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.
Wenyang
Wenyang, thanks for your reply... It does not sound too uplifting.
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
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.
|||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 DB2 connection strings
I am trying to construct a connection string via iSeries OleDb provider. The default list of libraries on the AS400 for my user is fine for production. But in test scenarios the code/data libraries are rolled out through a sequence of test libraries. How do I specify the additional libraries for testing.
I connect fine and can execute sql. the statements like insert into TestLibrary.Table work fine, but the associated trigger code isn't called.
Would it be better to use the .Net Connection set of classes instead. I switched from ODBC to OleDb because of assumed thread safety issues.I don't understand what you're looking for. Can you be more specific? If trigger code isn't called, how is that an SSIS issue?|||Maybe not.
The problem is building a connection string to communicate withe as400 db2. I found other threads in this forum dealing with other issues on this connectivity. The point about the trigger code not executing is because the library on the as400 that contains the trigger code is not part of the default library list for the connecting user.
the question is how to construct the connection string to add additional libraries into the scope of the connection. I was able to do so using ODBC. I wanted to use either the OleDb or the ADO clone iSeries connection stuff to do it as well.|||This link might help: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061900&SiteID=1
Thursday, March 8, 2012
As2000 and Analysis Services provider
I can design the dataset using the graphical tools but the report refuses to
run, is there a work around. Presumably the problem will be fixed in the
next service pack?Hello Phil,
If you change the design mode to text, could you get the proper MDX? If you
run the MDX, could you see the correct results?
It seems you custom the data processing extension. Is this true and how you
perform this customization?
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Phil Nicholas" <PhilNicholas@.nospam.nospam>
>Subject: As2000 and Analysis Services provider
>Date: Tue, 7 Mar 2006 10:21:40 -0000
>Lines: 5
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>Message-ID: <#8D4eCdQGHA.5592@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: cpc2-norw3-4-0-cust28.pete.cable.ntl.com 82.25.55.28
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:69941
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I can design the dataset using the graphical tools but the report refuses
to
>run, is there a work around. Presumably the problem will be fixed in the
>next service pack?
>
>
run, is there a work around. Presumably the problem will be fixed in the
next service pack?Hello Phil,
If you change the design mode to text, could you get the proper MDX? If you
run the MDX, could you see the correct results?
It seems you custom the data processing extension. Is this true and how you
perform this customization?
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Phil Nicholas" <PhilNicholas@.nospam.nospam>
>Subject: As2000 and Analysis Services provider
>Date: Tue, 7 Mar 2006 10:21:40 -0000
>Lines: 5
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>Message-ID: <#8D4eCdQGHA.5592@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: cpc2-norw3-4-0-cust28.pete.cable.ntl.com 82.25.55.28
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:69941
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I can design the dataset using the graphical tools but the report refuses
to
>run, is there a work around. Presumably the problem will be fixed in the
>next service pack?
>
>
Wednesday, March 7, 2012
AS/400 Linked Server
Hello. Anyone know how what's needed to connect to an
AS/400 from SQL Server via a Linked Server? Any special
Provider softwre need to be in place? Any references on
this would help. THanks a lot... BruceBruce,
Try the MS Host Integration Server Administration Client .What you should be
installing is Microsoft ODBC Driver for DB2.
Refer
INF: Configuring Data Sources for the Microsoft OLE DB Provider for AS/400
and VSAM
http://support.microsoft.com/?id=219077
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://support.microsoft.com/?id=218590
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:016701c35218$f63188c0$a501280a@.phx.gbl...
> Hello. Anyone know how what's needed to connect to an
> AS/400 from SQL Server via a Linked Server? Any special
> Provider softwre need to be in place? Any references on
> this would help. THanks a lot... Bruce|||Excellent info Dinesh. I'll check it out... THanks, Bruce
>--Original Message--
>Bruce,
>Try the MS Host Integration Server Administration
Client .What you should be
>installing is Microsoft ODBC Driver for DB2.
>Refer
>INF: Configuring Data Sources for the Microsoft OLE DB
Provider for AS/400
>and VSAM
>http://support.microsoft.com/?id=219077
>INF: Configuring Data Sources for the Microsoft OLE DB
Provider for DB2
>http://support.microsoft.com/?id=218590
>
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:016701c35218$f63188c0$a501280a@.phx.gbl...
>> Hello. Anyone know how what's needed to connect to an
>> AS/400 from SQL Server via a Linked Server? Any special
>> Provider softwre need to be in place? Any references on
>> this would help. THanks a lot... Bruce
>
>.
>
AS/400 from SQL Server via a Linked Server? Any special
Provider softwre need to be in place? Any references on
this would help. THanks a lot... BruceBruce,
Try the MS Host Integration Server Administration Client .What you should be
installing is Microsoft ODBC Driver for DB2.
Refer
INF: Configuring Data Sources for the Microsoft OLE DB Provider for AS/400
and VSAM
http://support.microsoft.com/?id=219077
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://support.microsoft.com/?id=218590
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:016701c35218$f63188c0$a501280a@.phx.gbl...
> Hello. Anyone know how what's needed to connect to an
> AS/400 from SQL Server via a Linked Server? Any special
> Provider softwre need to be in place? Any references on
> this would help. THanks a lot... Bruce|||Excellent info Dinesh. I'll check it out... THanks, Bruce
>--Original Message--
>Bruce,
>Try the MS Host Integration Server Administration
Client .What you should be
>installing is Microsoft ODBC Driver for DB2.
>Refer
>INF: Configuring Data Sources for the Microsoft OLE DB
Provider for AS/400
>and VSAM
>http://support.microsoft.com/?id=219077
>INF: Configuring Data Sources for the Microsoft OLE DB
Provider for DB2
>http://support.microsoft.com/?id=218590
>
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:016701c35218$f63188c0$a501280a@.phx.gbl...
>> Hello. Anyone know how what's needed to connect to an
>> AS/400 from SQL Server via a Linked Server? Any special
>> Provider softwre need to be in place? Any references on
>> this would help. THanks a lot... Bruce
>
>.
>
AS/400 DB2 Driver
Hi,
I tried the way of adding a Linked Server.But DB2OLEDB provider is not listing on the
Provider Name combo box when i go to add it.Waht should I do?
In the Provider String there are two attributes call
Package Collection=MyPkgCol;
Default Schema=Schema;
What does it mean?
This is SQL I used.
EXEC sp_addlinkedserver
@.server = '10.1.0.7',
@.provider = 'DB2OLEDB',
@.srvproduct = '',
@.provstr = 'Provider=DB2OLEDB;Network Transport Library=TCPIP;Network
Address=100.1.0.27;Initial Catalog=Depani;User ID=gift;Password=gift;'
Any coding related to Please send me.
Thanks.
Pubudu
If you have any issue with DB2OLEDB provider then you can use the following Driver.
Note: Your Server need to have the DRIVER/PROVIDER. If it is not available you can get it from IBM-DB2 site.
Driver={IBM DB2 ODBC DRIVER};Database=myDataBase;Hostname=myServerAddress;Port=1234;Protocol=TCPIP;Uid=myUsername;Pwd=myPassword;
|||I have installed the AS400 DB2 client and Im having a provider call IBM AS400
OLEDB Provider.
I have used in this way
'Driver={IBM AS400 OLEDB Provider};Database=upul;Hostname=10.1.0.4;Port=23;Protocol=TCPIP;Uid=upul;Pwd=upul02;'
Please help me.
Thanks,
Pubudu
|||Are you able to connect the server.
If not use the IBM's oledb provider..
Provider=IBMDADB2;Database=myDataBase;Hostname=myServerAddress;Protocol=TCPIP;Port=50000;Uid=myUsername;Pwd=myPassword;
AS/400 DB2 Access through SQL Server 2000
Hi,
I tried the way of adding a Linked Server.But DB2OLEDB provider is not listing on the
Provider Name combo box when i go to add it.Waht should I do?
In the Provider String there are two attributes call
Package Collection=MyPkgCol;
Default Schema=Schema;
What does it mean?
This is SQL I used.
EXEC sp_addlinkedserver
@.server = '10.1.0.7',
@.provider = 'DB2OLEDB',
@.srvproduct = '',
@.provstr = 'Provider=DB2OLEDB;Network Transport Library=TCPIP;Network
Address=100.1.0.27;Initial Catalog=Depani;User ID=gift;Password=gift;'
Any coding related to Please send me.
Thanks.
Pubudu
If you have any issue with DB2OLEDB provider then you can use the following Driver.
Note: Your Server need to have the DRIVER/PROVIDER. If it is not available you can get it from IBM-DB2 site.
Driver={IBM DB2 ODBC DRIVER};Database=myDataBase;Hostname=myServerAddress;Port=1234;Protocol=TCPIP;Uid=myUsername;Pwd=myPassword;
|||I have installed the AS400 DB2 client and Im having a provider call IBM AS400
OLEDB Provider.
I have used in this way
'Driver={IBM AS400 OLEDB Provider};Database=upul;Hostname=10.1.0.4;Port=23;Protocol=TCPIP;Uid=upul;Pwd=upul02;'
Please help me.
Thanks,
Pubudu
|||Are you able to connect the server.
If not use the IBM's oledb provider..
Provider=IBMDADB2;Database=myDataBase;Hostname=myServerAddress;Protocol=TCPIP;Port=50000;Uid=myUsername;Pwd=myPassword;
Friday, February 24, 2012
Arrrggh - Linked Server issue
Help! I can't seem to get the permissions right on my linked server. I keep getting this error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP.3" for linked server "DW" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP.3" for linked server "DW".
Here you go: http://support.microsoft.com/kb/925869
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
Thank you. That helped. Now when i query using a SQL Server user (non-windows) I get this response:
OLE DB provider "MSOLAP" for linked server "DW" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "DW" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "DW".
I noticed in the profiler that the user who is connecting is anonymous logon and I turned the required auth on AS off, but I have no idea what permissions are needed to allow this to work.
Thursday, February 16, 2012
Arithmetic overflow error converting expression to data type int.
I've got this error message while generate the output with ASP:
"Microsoft OLE DB Provider for SQL Server (0x80004005)
Arithmetic overflow error converting expression to data type int."
it indicate that the error is related to this line:
"rc1.Movenext"
where rc1 is set as objconn.Execute(sql).
Not all outputs result like this, it happens when it has many relationships with other records, especially with those records which also have many relationships with other records.
Can anyone suggest a solution?
I've tried to increase the size of the database file, but it doesn't work.could you perhaps show the query that caused the error?|||Here is the query:
CREATE procedure rp_co_relatedcompanies @.companyregistrationno varchar(20)
as
set nocount on
--used for related companies details
select a.companypersonid,a.personname,a.personaddress1,a. personaddress2,a.personcity,a.personstate,a.person country,
a.personzip,a.personphone,a.personfax,a.personemai l,a.personuniqueid,b.principalbusiness,b.secondryb usiness,
c.relationwithcompany,c.numberofshares, c.classofsharesheld , cast(d.noofissuedshares as int) 'noofissuedshares',
'percentageofsharehold' = case when c.numberofshares =0 then 0 else c.numberofshares/d.noofissuedshares*100 end, c.otherremarks
from companyperson a
left join companystructure d on a.personuniqueid=d.companyregistrationno
left outer join operationsandactivity b on a.personuniqueid = b.companyregistrationno
left outer join relationshipcompanyperson c on a.companypersonid = c.companypersonid
where c.companyregistrationno = @.companyregistrationno and c.relationshipwith = 2
and a.deletionflag=0 order by a.personname
set nocount off
GO|||can you see anything in that query where there might be a problem "converting expression to data type int"
the immediate suspect is cast(d.noofissuedshares as int)
another suspect is c.numberofshares/d.noofissuedshares*100
check those and let us know|||Table DDL would help a lot. Heck, I was betting on the c.companyregistrationno being an INT and the @.companyregistrationno failing the implicit Cast...
Anybody want to start a pool on this? Or another option (not as much fun, but the problem would get solved a lot faster) would be for yllas to post the DDL for all of the tables in this query!
-PatP
"Microsoft OLE DB Provider for SQL Server (0x80004005)
Arithmetic overflow error converting expression to data type int."
it indicate that the error is related to this line:
"rc1.Movenext"
where rc1 is set as objconn.Execute(sql).
Not all outputs result like this, it happens when it has many relationships with other records, especially with those records which also have many relationships with other records.
Can anyone suggest a solution?
I've tried to increase the size of the database file, but it doesn't work.could you perhaps show the query that caused the error?|||Here is the query:
CREATE procedure rp_co_relatedcompanies @.companyregistrationno varchar(20)
as
set nocount on
--used for related companies details
select a.companypersonid,a.personname,a.personaddress1,a. personaddress2,a.personcity,a.personstate,a.person country,
a.personzip,a.personphone,a.personfax,a.personemai l,a.personuniqueid,b.principalbusiness,b.secondryb usiness,
c.relationwithcompany,c.numberofshares, c.classofsharesheld , cast(d.noofissuedshares as int) 'noofissuedshares',
'percentageofsharehold' = case when c.numberofshares =0 then 0 else c.numberofshares/d.noofissuedshares*100 end, c.otherremarks
from companyperson a
left join companystructure d on a.personuniqueid=d.companyregistrationno
left outer join operationsandactivity b on a.personuniqueid = b.companyregistrationno
left outer join relationshipcompanyperson c on a.companypersonid = c.companypersonid
where c.companyregistrationno = @.companyregistrationno and c.relationshipwith = 2
and a.deletionflag=0 order by a.personname
set nocount off
GO|||can you see anything in that query where there might be a problem "converting expression to data type int"
the immediate suspect is cast(d.noofissuedshares as int)
another suspect is c.numberofshares/d.noofissuedshares*100
check those and let us know|||Table DDL would help a lot. Heck, I was betting on the c.companyregistrationno being an INT and the @.companyregistrationno failing the implicit Cast...
Anybody want to start a pool on this? Or another option (not as much fun, but the problem would get solved a lot faster) would be for yllas to post the DDL for all of the tables in this query!
-PatP
Monday, February 13, 2012
ARITHABORT error when update from asp page
Hi all,
I get the following error when I try to update the table from asp page:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
UPDATE failed because the following SET options have incorrect settings:
'ARITHABORT'
I try the same update statement in query analyzer and it works fine. The
update command is very simple: update tableA set columnA='something' where
columnID=1
when I use SET ARITHABORT OFF in query analyzer, it fails. So I thought if I
use SET ARITHABORT ON, it would be OK again; but it doesn't work anymore.
The only thing I can get it work again in query analyzer is to close it and
re-open it.
Please help me so I can get the update statement work in asp page. Thanks
very much.VNN wrote:
> I try the same update statement in query analyzer and it works fine. The
> update command is very simple: update tableA set columnA='something' where
> columnID=1
What is the data type for ColumnA a and ColumnID?
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||> update command is very simple: update tableA set columnA='something' where
> columnID=1
Can you post the 'something'?
AMB
"VNN" wrote:
> Hi all,
> I get the following error when I try to update the table from asp page:
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E14)
> UPDATE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> I try the same update statement in query analyzer and it works fine. The
> update command is very simple: update tableA set columnA='something' where
> columnID=1
> when I use SET ARITHABORT OFF in query analyzer, it fails. So I thought if
I
> use SET ARITHABORT ON, it would be OK again; but it doesn't work anymore.
> The only thing I can get it work again in query analyzer is to close it an
d
> re-open it.
> Please help me so I can get the update statement work in asp page. Thanks
> very much.
>
>|||ColumnA: varchar
ColumnID: int
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:uXH9aFiDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> VNN wrote:
where
> What is the data type for ColumnA a and ColumnID?
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||this is the actual sql statement:
UpdateSQL = "UPDATE GusMenu.Depts SET DeptName='" & strValue & "' WHERE
GusMenu.Depts.DeptID = " & rsDept("DeptID") & ";"
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:0F6EDC7F-AB85-49D6-BE37-E0487737A662@.microsoft.com...
where
> Can you post the 'something'?
>
> AMB
>
> "VNN" wrote:
>
where
if I
anymore.
and
Thanks|||VNN wrote:
> UpdateSQL = "UPDATE GusMenu.Depts SET DeptName='" & strValue & "' WHERE
> GusMenu.Depts.DeptID = " & rsDept("DeptID") & ";"
You should really use parameters instead of trying to construct a
dynamic query like this. Check out this page for a good reference on how
to do it.
http://www.uberasp.net/ArticlePrint.aspx?id=46
This may also solve your problem as well.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||still doesn't work. Is there a way I can make it work in Query Analyzer
after I set ARITHABORT OFF? Please advice.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:O9S2HYiDFHA.3416@.TK2MSFTNGP09.phx.gbl...
> VNN wrote:
> You should really use parameters instead of trying to construct a
> dynamic query like this. Check out this page for a good reference on how
> to do it.
> http://www.uberasp.net/ArticlePrint.aspx?id=46
> This may also solve your problem as well.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||VNN wrote:
> still doesn't work. Is there a way I can make it work in Query Analyzer
> after I set ARITHABORT OFF? Please advice.
When you run Profiler, what is being sent down to the SQL Server?
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||You need to post the DDL of the table(s) involved, the actual update
statement (after inclusion of any variables). In addition, you should also
examine the code for any triggers fired by the statement and the DDL of any
views that use the table. Something somewhere is dependent on the
arithabort setting. This could be related to an index on a computed column
(or an indexed view). If this is the problem, then you need to enforce this
setting when connecting to the database (or at least performing operations
involving the problem objects).
"VNN" <VNN@.hotmail.com> wrote in message
news:u8eHscsDFHA.1392@.tk2msftngp13.phx.gbl...
> still doesn't work. Is there a way I can make it work in Query Analyzer
> after I set ARITHABORT OFF? Please advice.
>
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:O9S2HYiDFHA.3416@.TK2MSFTNGP09.phx.gbl...
WHERE
>|||I have the index view link to this table. I read MS website and looks like
the index view is causing this error. I tried to set conn.Execute "SET
ARITHABORT ON" right after I open the connection but didn't work.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:eeVagvsDFHA.3648@.TK2MSFTNGP10.phx.gbl...
> You need to post the DDL of the table(s) involved, the actual update
> statement (after inclusion of any variables). In addition, you should
also
> examine the code for any triggers fired by the statement and the DDL of
any
> views that use the table. Something somewhere is dependent on the
> arithabort setting. This could be related to an index on a computed
column
> (or an indexed view). If this is the problem, then you need to enforce
this
> setting when connecting to the database (or at least performing operations
> involving the problem objects).
> "VNN" <VNN@.hotmail.com> wrote in message
> news:u8eHscsDFHA.1392@.tk2msftngp13.phx.gbl...
> WHERE
how
>
I get the following error when I try to update the table from asp page:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
UPDATE failed because the following SET options have incorrect settings:
'ARITHABORT'
I try the same update statement in query analyzer and it works fine. The
update command is very simple: update tableA set columnA='something' where
columnID=1
when I use SET ARITHABORT OFF in query analyzer, it fails. So I thought if I
use SET ARITHABORT ON, it would be OK again; but it doesn't work anymore.
The only thing I can get it work again in query analyzer is to close it and
re-open it.
Please help me so I can get the update statement work in asp page. Thanks
very much.VNN wrote:
> I try the same update statement in query analyzer and it works fine. The
> update command is very simple: update tableA set columnA='something' where
> columnID=1
What is the data type for ColumnA a and ColumnID?
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||> update command is very simple: update tableA set columnA='something' where
> columnID=1
Can you post the 'something'?
AMB
"VNN" wrote:
> Hi all,
> I get the following error when I try to update the table from asp page:
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E14)
> UPDATE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> I try the same update statement in query analyzer and it works fine. The
> update command is very simple: update tableA set columnA='something' where
> columnID=1
> when I use SET ARITHABORT OFF in query analyzer, it fails. So I thought if
I
> use SET ARITHABORT ON, it would be OK again; but it doesn't work anymore.
> The only thing I can get it work again in query analyzer is to close it an
d
> re-open it.
> Please help me so I can get the update statement work in asp page. Thanks
> very much.
>
>|||ColumnA: varchar
ColumnID: int
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:uXH9aFiDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> VNN wrote:
where
> What is the data type for ColumnA a and ColumnID?
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||this is the actual sql statement:
UpdateSQL = "UPDATE GusMenu.Depts SET DeptName='" & strValue & "' WHERE
GusMenu.Depts.DeptID = " & rsDept("DeptID") & ";"
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:0F6EDC7F-AB85-49D6-BE37-E0487737A662@.microsoft.com...
where
> Can you post the 'something'?
>
> AMB
>
> "VNN" wrote:
>
where
if I
anymore.
and
Thanks|||VNN wrote:
> UpdateSQL = "UPDATE GusMenu.Depts SET DeptName='" & strValue & "' WHERE
> GusMenu.Depts.DeptID = " & rsDept("DeptID") & ";"
You should really use parameters instead of trying to construct a
dynamic query like this. Check out this page for a good reference on how
to do it.
http://www.uberasp.net/ArticlePrint.aspx?id=46
This may also solve your problem as well.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||still doesn't work. Is there a way I can make it work in Query Analyzer
after I set ARITHABORT OFF? Please advice.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:O9S2HYiDFHA.3416@.TK2MSFTNGP09.phx.gbl...
> VNN wrote:
> You should really use parameters instead of trying to construct a
> dynamic query like this. Check out this page for a good reference on how
> to do it.
> http://www.uberasp.net/ArticlePrint.aspx?id=46
> This may also solve your problem as well.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||VNN wrote:
> still doesn't work. Is there a way I can make it work in Query Analyzer
> after I set ARITHABORT OFF? Please advice.
When you run Profiler, what is being sent down to the SQL Server?
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||You need to post the DDL of the table(s) involved, the actual update
statement (after inclusion of any variables). In addition, you should also
examine the code for any triggers fired by the statement and the DDL of any
views that use the table. Something somewhere is dependent on the
arithabort setting. This could be related to an index on a computed column
(or an indexed view). If this is the problem, then you need to enforce this
setting when connecting to the database (or at least performing operations
involving the problem objects).
"VNN" <VNN@.hotmail.com> wrote in message
news:u8eHscsDFHA.1392@.tk2msftngp13.phx.gbl...
> still doesn't work. Is there a way I can make it work in Query Analyzer
> after I set ARITHABORT OFF? Please advice.
>
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:O9S2HYiDFHA.3416@.TK2MSFTNGP09.phx.gbl...
WHERE
>|||I have the index view link to this table. I read MS website and looks like
the index view is causing this error. I tried to set conn.Execute "SET
ARITHABORT ON" right after I open the connection but didn't work.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:eeVagvsDFHA.3648@.TK2MSFTNGP10.phx.gbl...
> You need to post the DDL of the table(s) involved, the actual update
> statement (after inclusion of any variables). In addition, you should
also
> examine the code for any triggers fired by the statement and the DDL of
any
> views that use the table. Something somewhere is dependent on the
> arithabort setting. This could be related to an index on a computed
column
> (or an indexed view). If this is the problem, then you need to enforce
this
> setting when connecting to the database (or at least performing operations
> involving the problem objects).
> "VNN" <VNN@.hotmail.com> wrote in message
> news:u8eHscsDFHA.1392@.tk2msftngp13.phx.gbl...
> WHERE
how
>
Subscribe to:
Posts (Atom)