How not to cache in Lookups to Oracle

Why can you not turn off the caching in a Lookup against Oracle?

I have an exceedingly complicated SQL statement like this -

SELECT OBJECT_ID, OBJECT_CODE FROM OBJECT_TABLE

If I turn off the cache for a lookup I get bombarded with this rubbish-

Error 8 Validation error. DFT Load STATUS: LKP Get RESULT_NO [128]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Update.dtsx 0 0

Error 9 Validation error. DFT Load DAY_STATUS: LKP Get RESULT_NO [128]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Update.dtsx 0 0

I have tried modifying the Cache SQL Statement as well, but to no avail. I am using the MSDAORA.1 provider against "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production".

Any ideas ?

I'm not a pro in Oracle, but just to make sure you are modifying the correct SQL statement: if you don't enable memory restrictions and lookup is in Fully cached mode, the SQL statement from first tab page is used. If you enable memory restrictions and the lookup is in Partial or No-cache mode, the caching SQL query from Advanced tab is used.

It looks like you want fully cached mode - so make sure the memory restrictions are not enabled (first checkbox on Advanced page should be clear).|||

Had this problem to,

solved when I used oraoledb.oracle.1 instead of msdaora.1

|||Where did you find this option?|||

J.A.J. wrote:

Where did you find this option?

Which option? The above post refers to a different OLE Oracle driver.|||Darren, did you ever get this resolved and if so how?|||

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

|||

J.A.J. wrote:

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

Looks like it's an Oracle provided OLE DB driver. You can check their Website.

How not to cache in Lookups to Oracle

Why can you not turn off the caching in a Lookup against Oracle?

I have an exceedingly complicated SQL statement like this -

SELECT OBJECT_ID, OBJECT_CODE FROM OBJECT_TABLE

If I turn off the cache for a lookup I get bombarded with this rubbish-

Error 8 Validation error. DFT Load STATUS: LKP Get RESULT_NO [128]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Update.dtsx 0 0

Error 9 Validation error. DFT Load DAY_STATUS: LKP Get RESULT_NO [128]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Update.dtsx 0 0

I have tried modifying the Cache SQL Statement as well, but to no avail. I am using the MSDAORA.1 provider against "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production".

Any ideas ?

I'm not a pro in Oracle, but just to make sure you are modifying the correct SQL statement: if you don't enable memory restrictions and lookup is in Fully cached mode, the SQL statement from first tab page is used. If you enable memory restrictions and the lookup is in Partial or No-cache mode, the caching SQL query from Advanced tab is used.

It looks like you want fully cached mode - so make sure the memory restrictions are not enabled (first checkbox on Advanced page should be clear).|||

Had this problem to,

solved when I used oraoledb.oracle.1 instead of msdaora.1

|||Where did you find this option?|||

J.A.J. wrote:

Where did you find this option?

Which option? The above post refers to a different OLE Oracle driver.|||Darren, did you ever get this resolved and if so how?|||

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

|||

J.A.J. wrote:

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

Looks like it's an Oracle provided OLE DB driver. You can check their Website.

How much space will it Take??

friends,
I m in a process of makin a website tat will store the vital
statistics of users.Users can turn out to be in millions!!!
Can u help me to kno how much space will tat take and how much space
should i book online to keep my site in wrkin condition.
Ur help sought.
Thanx
samraatWithout table definitions (Including indexes) that would be impossible.
100 milllion users at an average of 8 bytes per row is small
100 million users at an average of 7800 bytes per row with 70 indexes most of them compound could be nasty.
Like I said, without definitions the permetations are endless.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||samabhik,
> I m in a process of makin a website tat will store the vital
> statistics of users.Users can turn out to be in millions!!!
> Can u help me to kno how much space will tat take and how much
> space should i book online to keep my site in wrkin condition.
Once you have your schema, you can calculate how much space you will
need. Refer to the topic Estimating Database Size in Books Online
for an example.
Linda

How much of SQL Express is required at user locations?

SQL Express is quite sizable for what I want to do but it appears to have many features that could turn out to be highly desirable.

It makes sense that the entire SQL Express must be installed at the developer's location, but what portion of it is required at the application user's location? All, or some?

Thanks

You still need to install the complete system, by this I mean the standard system. You do not have to install the management tools, but the rest is required.