servers (Oracle)

I'm interested in linking to an Oracle 8i database that is storing
data for a COTS program we are using which does not support the
detailed level of reporting that my organization needs. Anybody have
any tips or advise for this type of operation?
--
HTH
Dale FyePretty broad question... in general it's doable. Queries
with linked servers aren't necessarily known for their
blazing speed but whether that impacts you or not, too hard
to say. You'd have options with this but it all depends on
what will work for your data, your requirements, your
hardware/network, number of users, general use, latency
tolerance, security requirements, etc.
You could look at OpenQuery to do pass-through queries, you
could look at loading the Oracle data needed during off
hours. There are a lot of different ways you could approach
it all depending on what the business, technical needs are.
-Sue
On Mon, 20 Oct 2003 10:34:27 -0400, "Dale Fye"
<spam.saic.com@.nomore.dale.fye> wrote:
>I'm interested in linking to an Oracle 8i database that is storing
>data for a COTS program we are using which does not support the
>detailed level of reporting that my organization needs. Anybody have
>any tips or advise for this type of operation?|||I guess that was a little cryptic.
The Oracle database is the backend for an Commercial product.
Unfortunately, the organization I work for is interested in
investigating the level of usage of the product and generating reports
on these issues, a function that is not provided by the product,
although it does have a table which records many admin defined
transactions. Since I don't need real-time access to the data, we
anticipate migrating the data from the Oracle db to SQL Server (our db
of choice) each night, but would prefer to do an incremental update of
the SQL db rather than wiping the tables and copying the entire tables
worth of data each night.
I'm open to ideas(DTS, Linked Server, or any other options that might
be available) for this relatively simple process. Unfortunately, we
have no one with any Oracle experience.
--
HTH
Dale Fye
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:k3o8pv03dohhacnfd8q1naa1p5tdrurt47@.4ax.com...
Pretty broad question... in general it's doable. Queries
with linked servers aren't necessarily known for their
blazing speed but whether that impacts you or not, too hard
to say. You'd have options with this but it all depends on
what will work for your data, your requirements, your
hardware/network, number of users, general use, latency
tolerance, security requirements, etc.
You could look at OpenQuery to do pass-through queries, you
could look at loading the Oracle data needed during off
hours. There are a lot of different ways you could approach
it all depending on what the business, technical needs are.
-Sue
On Mon, 20 Oct 2003 10:34:27 -0400, "Dale Fye"
<spam.saic.com@.nomore.dale.fye> wrote:
>I'm interested in linking to an Oracle 8i database that is storing
>data for a COTS program we are using which does not support the
>detailed level of reporting that my organization needs. Anybody have
>any tips or advise for this type of operation?|||If you were asking...yes you can do incremental updates. You
could use dts and insert the Oracle records that don't exist
in the SQL Server tables. If you had a date time value
updated for modifications on the Oracle side, you can use
this to compare for any changes, needed updates as well.
-Sue
On Tue, 21 Oct 2003 08:49:10 -0400, "Dale Fye"
<spam.saic.com@.nomore.dale.fye> wrote:
>I guess that was a little cryptic.
>The Oracle database is the backend for an Commercial product.
>Unfortunately, the organization I work for is interested in
>investigating the level of usage of the product and generating reports
>on these issues, a function that is not provided by the product,
>although it does have a table which records many admin defined
>transactions. Since I don't need real-time access to the data, we
>anticipate migrating the data from the Oracle db to SQL Server (our db
>of choice) each night, but would prefer to do an incremental update of
>the SQL db rather than wiping the tables and copying the entire tables
>worth of data each night.
>I'm open to ideas(DTS, Linked Server, or any other options that might
>be available) for this relatively simple process. Unfortunately, we
>have no one with any Oracle experience.

server/SQL Server OLE DB performance problem with parameters?

Hi,
I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.

I have set the dynamic paramters option on, and use collation compatible.Linked servers in general are a performance nightmare.

Get me that bucket of data over there...ALL of it.

Careful...carry it over here now. Be careful. Oh crap...I dropped some. You need to start over.

Great...it's finally here....ALL OF IT. Can you throw away everything that doesn't match this one value?

That's great. Thanks.|||Replace a straight DML/SELECT with a call to a stored procedure on the remote side and you'll be home free!|||SQL Attempts to retrieve statistics information from remote tables to determine which server should be the driving server - or where to filter rows first before joins. It runs some system stored procedures (I forget the names at the moment) If the account connecting to the remote server does not have permisisons on the procedures SQL will assume the worset and generate a query plan with the statistics available on the local server. I'm not recommending you grant the remote user dbo rights (not that I would do that ...) but if you did do it temporarily and saw a performance increase you could research exactly which permissions were really needed. Actually you can run profiler on the remote server and you will see the statistics gathering queries and identify the objects involved.|||In testing I found that the query performed faster with the parameters declared 'inline'. Does this make sense? Or am I suffering from inconsistent testing conditions? Our production databases are on the same servers as our test dbs.

The distributed queries are in stored procedures. And they (usually) don't join to the local server. However, the stored procs need to query the local db and store results there. It would be possible to put stored procs on the remote db. But we have already taken the functions into production so code changes are undesired.|||Try using the openquery() method. When this method is used the calling SQL server instance does not attempt to retrieve statistics info and the linked server processes the SQL and simply returns the results.

This especially makes a huge difference when the linked server is Sybase ASE.