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 move data from informix, continuously

Dear Friends,
There is an "informix" dbserver, with a table that acts as cache for
continuously generated information ( an applications fills it continuously
in UNIX env).
I want to transfer(move) data from above informix table to my mssql table.
I find two way to do above movement
1- write an application that selects some info from Informix and insert
records to destination mssql then delete source records based on last
identity field
2- use schedule "Import Data" to copy data, simple but can't delete copied
data upto moved records not newer records
is there any other method? can I improve 2nd method to delete copied record
also?
thanks for any suggestions
TarvirdiHi Tarvirdi,
I need a wee bit more info to suggest something. What is the version of
Informix you are using, also you refer to a cache table, can you be more
specific? Hopefully you are not trying to examine/manipulate the internal
logging tables which control Informix data cahing and replication - chances
are you will break them.
Also some food for thought. Even when one undertakes continuous replication
from one informix instance to another, the drain on resources is quite
significant and also forces checkpointing to occur at the end of every commit
work statement. Typically one wouldn't attempt to do continuous replication
unless the application was so crucial that automatic and immediate failover
was required - which isn't possible with SQLServer anyway and arguably not a
great idea in Informix (don't the users want to know that something has just
gobe crach-bang?).
Gice me some more info and I'll try and help.
"Tarvirdi" wrote:
> Dear Friends,
> There is an "informix" dbserver, with a table that acts as cache for
> continuously generated information ( an applications fills it continuously
> in UNIX env).
> I want to transfer(move) data from above informix table to my mssql table.
> I find two way to do above movement
> 1- write an application that selects some info from Informix and insert
> records to destination mssql then delete source records based on last
> identity field
> 2- use schedule "Import Data" to copy data, simple but can't delete copied
> data upto moved records not newer records
> is there any other method? can I improve 2nd method to delete copied record
> also?
> thanks for any suggestions
> Tarvirdi
>
>sql

how move data from informix, continuously

Dear Friends,
There is an "informix" dbserver, with a table that acts as cache for
continuously generated information ( an applications fills it continuously
in UNIX env).
I want to transfer(move) data from above informix table to my mssql table.
I find two way to do above movement
1- write an application that selects some info from Informix and insert
records to destination mssql then delete source records based on last
identity field
2- use schedule "Import Data" to copy data, simple but can't delete copied
data upto moved records not newer records
is there any other method? can I improve 2nd method to delete copied record
also?
thanks for any suggestions
TarvirdiHi Tarvirdi,
I need a wee bit more info to suggest something. What is the version of
Informix you are using, also you refer to a cache table, can you be more
specific? Hopefully you are not trying to examine/manipulate the internal
logging tables which control Informix data cahing and replication - chances
are you will break them.
Also some food for thought. Even when one undertakes continuous replication
from one informix instance to another, the drain on resources is quite
significant and also forces checkpointing to occur at the end of every commi
t
work statement. Typically one wouldn't attempt to do continuous replication
unless the application was so crucial that automatic and immediate failover
was required - which isn't possible with SQLServer anyway and arguably not a
great idea in Informix (don't the users want to know that something has just
gobe crach-bang?).
Gice me some more info and I'll try and help.
"Tarvirdi" wrote:

> Dear Friends,
> There is an "informix" dbserver, with a table that acts as cache for
> continuously generated information ( an applications fills it continuously
> in UNIX env).
> I want to transfer(move) data from above informix table to my mssql table.
> I find two way to do above movement
> 1- write an application that selects some info from Informix and insert
> records to destination mssql then delete source records based on last
> identity field
> 2- use schedule "Import Data" to copy data, simple but can't delete copie
d
> data upto moved records not newer records
> is there any other method? can I improve 2nd method to delete copied recor
d
> also?
> thanks for any suggestions
> Tarvirdi
>
>

How long does a query plan stay in the procedure cache?

How long does a a query plan stay in the procedure cache? Is there someway
we can reload a specific sp in SQL Server is restarted? TIADepends on the cost associated with creating the plan as well as how often
the sproc is run. Yes you can configure the sproc to run each time the SQL
Server is started by using the sp_procoption system stored procedure (see
BOL for details).
HTH
J
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>|||Hi,
The most common reasons why a query execution plan is flushed from procedure
cache are:
1. There is not enough internal memory, or the query is not used frequently.
This causes the memory manager to assign the memory to other
procedure to be moved out from cache.You can try to run the query from time
to time.
2. Statistics are updated. When column statistics or index statistics are
updated, all query plans that use those tables will be discarded.
You can turn off auto-update statistics, and schedule UPDATE STATISTICS
yourself. After that you would need to run the query again in order to
can a compiled query plan
3. Schema changes. If the data structure is changed, indexes are added or
deleted, etc., then the related queries are marked for recompile.
Is there some way we can reload a specific sp in SQL Server is restarted?
We can put the procedure in startup . See sp_procoption system procedure in
books online.
Thanks
Hari
SQL Server MVP
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>

how is the cache broken up in SQL 2000?

I want to know how much of my buffer pool is being used for data and how
much is consumed for procedure cache?
Is there a way I can find out?Hi Hassan
"Hassan" wrote:
> I want to know how much of my buffer pool is being used for data and how
> much is consumed for procedure cache?
> Is there a way I can find out?
Have you seen http://support.microsoft.com/kb/271624 ?
John|||Hassan,
please take a look at DBCC MEMORYSTATUS :
http://support.microsoft.com/Default.aspx?id=271624. DBCC CACHESTATS, and
SYSCACHEOBJECTS might also be of interest (see
http://www.quest-pipelines.com/newsletter-v4/0303_B.htm).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||In addition, you can also use the perfmon counters in SQLServer:Buffer
Manager to get the info.
Linchi
"Hassan" wrote:
> I want to know how much of my buffer pool is being used for data and how
> much is consumed for procedure cache?
> Is there a way I can find out?
>
>

how is the cache broken up in SQL 2000?

I want to know how much of my buffer pool is being used for data and how
much is consumed for procedure cache?
Is there a way I can find out?
Hi Hassan
"Hassan" wrote:

> I want to know how much of my buffer pool is being used for data and how
> much is consumed for procedure cache?
> Is there a way I can find out?
Have you seen http://support.microsoft.com/kb/271624 ?
John
|||Hassan,
please take a look at DBCC MEMORYSTATUS :
http://support.microsoft.com/Default.aspx?id=271624. DBCC CACHESTATS, and
SYSCACHEOBJECTS might also be of interest (see
http://www.quest-pipelines.com/newsletter-v4/0303_B.htm).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||In addition, you can also use the perfmon counters in SQLServer:Buffer
Manager to get the info.
Linchi
"Hassan" wrote:

> I want to know how much of my buffer pool is being used for data and how
> much is consumed for procedure cache?
> Is there a way I can find out?
>
>

how is the cache broken up in SQL 2000?

I want to know how much of my buffer pool is being used for data and how
much is consumed for procedure cache?
Is there a way I can find out?Hi Hassan
"Hassan" wrote:

> I want to know how much of my buffer pool is being used for data and how
> much is consumed for procedure cache?
> Is there a way I can find out?
Have you seen http://support.microsoft.com/kb/271624 ?
John|||Hassan,
please take a look at DBCC MEMORYSTATUS :
http://support.microsoft.com/Default.aspx?id=271624. DBCC CACHESTATS, and
SYSCACHEOBJECTS might also be of interest (see
http://www.quest-pipelines.com/newsletter-v4/0303_B.htm).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||In addition, you can also use the perfmon counters in SQLServer:Buffer
Manager to get the info.
Linchi
"Hassan" wrote:

> I want to know how much of my buffer pool is being used for data and how
> much is consumed for procedure cache?
> Is there a way I can find out?
>
>