You administer a SQL Server 2000 computer that hosts your company's Accounts database. Another server running Oracle stores financial information that is frequently updated. You must generate reports several times per day that combine service contract data from SQL Server with financial data from the Oracle server. You want to build these reports on the SQL Server computer while using the minimum amount of extra disk space. What is the best approach?

Introduction / Context:
In heterogeneous database environments, it is common to need reports that combine data from different platforms such as SQL Server and Oracle. SQL Server provides several mechanisms for accessing external data, including linked servers and data import tools. This question focuses on choosing a method that allows frequent combined reporting while minimizing additional disk space usage on the SQL Server machine.

Given Data / Assumptions:

• SQL Server 2000 stores service contract information in the Accounts database.
• An Oracle RDBMS stores financial information that changes throughout the day.
• Reports that combine service contract and financial data must be generated several times a day.
• You want to perform reporting on the SQL Server computer.
• Minimizing extra disk space usage on SQL Server is a primary requirement.

Concept / Approach:
A SQL Server linked server allows you to connect to external data sources such as Oracle and query them via distributed queries without permanently storing copies of the data in SQL Server. By configuring Oracle as a linked server, you can create views in SQL Server that join local tables with remote Oracle tables. This approach uses minimal additional disk space because it does not require duplicating the Oracle data in local SQL Server tables. In contrast, replication or DTS imports would copy data into SQL Server, consuming extra storage and requiring frequent updates to stay current.

Step-by-Step Solution:
1. Configure Oracle as a linked server in SQL Server using the appropriate OLE DB provider for Oracle. 2. Verify connectivity by querying simple Oracle tables from SQL Server using four-part names (for example, SELECT * FROM OracleLinkedServer..Schema.TableName). 3. Create a SQL Server view that joins the local service contract tables with the remote Oracle financial tables using the linked server reference. 4. Base your reporting procedures or reporting tools on this view, so that each report retrieves up-to-date data directly from Oracle without storing duplicate copies. 5. Tune performance as needed by adding appropriate indexes on both sides and adjusting query hints if necessary.

Verification / Alternative check:
After configuring the linked server and the view, run sample reports and verify that the financial figures match those in the Oracle system in real time. Monitor network traffic and query execution times to ensure that performance is acceptable. Because no permanent copies of the Oracle data are stored in SQL Server, you should see minimal growth in your SQL Server databases.

Why Other Options Are Wrong:
Option A, using replication to copy Oracle data into SQL Server, would consume significant disk space for duplicated tables and require complex setup (SQL Server replication is typically for SQL Server-to-SQL Server scenarios). Option C, repeatedly importing data into temporary tables with an ActiveX script, still involves moving large amounts of data and can be inefficient; it also complicates scheduling and may require extra space during each import. Option D, using a DTS package to import and transform data throughout the day, would likewise create and maintain local copies, using more disk space and increasing maintenance overhead. All of these options violate the requirement to minimize extra disk usage.

Common Pitfalls:
A common mistake is to replicate or import large tables unnecessarily when the primary need is read-only reporting that can be satisfied through a linked server. Another pitfall is neglecting performance considerations; distributed queries can be slower if not tuned, so appropriate indexing and selective queries are important. Some administrators also overlook security and connectivity issues when configuring linked servers, so it is important to ensure credentials and network settings are correct.

Final Answer:
You should configure the Oracle server as a linked server in SQL Server and create a view that joins the local service contract tables with the Oracle financial data so that reports can be run with minimal additional disk space usage.