Connecting to Oracle Linked Servers
If your linked server connection doesn't work, you won't get far when you try to run distributed queries. Here are some common problems you might encounter when configuring the Oracle client, along with ways to overcome them:
Receiving an Invalid Login message. This problem occurs when the login you use to connect to the linked server isn't valid on the Oracle server. To test the login's validity, first try to connect using the Oracle sample Scott/tiger user ID and password.
Receiving the message Credential Retrieval Failed. This message appears when the client machine is trying to use NTS authentication and authentication is failing. You can correct this problem by commenting out the following parameter in the sqlnet.ora file on the client machine: #Sqlnet.Authentication_services = (NTS). (The # symbol designates a comment.) You can find the sqlnet.ora file in the \oracle\ora92\network\ADMIN directory.
Can't connect from a WAN link or from a different subnet. This problem can occur when the Oracle listen port isn't available. Make sure port 1521 is open. If this port is open, double-check the Oracle listener's configuration to determine whether the default port has changed.
—Michael Otey
"Solving Common Connection Problems" March 2003, InstantDoc ID 37777
Centralize Data Files
When a Data Transformation Services (DTS) package is executing, it runs under the security context of the person or process executing the package. So the security context, be it through the user's account or the SQL Server Agent service's startup account, must have appropriate permissions to read from source files and write to destination files. To reduce administrative overhead, consider using one folder on one server for all data files. By centralizing data files to one server and folder, you can easily manage security access. If data files are in multiple locations, you have to manage the security for all source and destination folders separately. By keeping data files in one place, you know exactly where to locate your source and destination files, reducing the time you spend looking for them. If you need to separate a test file from a production file, simply create a separate folder in your main directory.
—Shane Dovers
"DTS on the Move"
June 2003, InstantDoc ID 38658
Metadata from Oracle Linked Servers
If you aren't comfortable using Oracle tools to query the Oracle data dictionary, some SQL Server system stored procedures can help you obtain Oracle metadata from a linked Oracle server. The procedures in Table 1 accept from four to seven parameters. They all accept the same first three parameters (linked server name, table name, schema name). SQL Server Books Online (BOL) gives full details about the use of these procedures.
In addition, SQL Server's sp_columns_ex system procedure, which returns a list of columns in an Oracle table, requires at least three non-null parameters. The sp_linkedservers procedure, which returns a list of all linked-server definitions, takes no parameters. The sp_catalogs system procedure works when your linked servers are both SQL Server, but it doesn't work with Oracle linked servers.
—John Paul Cook, "System Procedures for Obtaining Linked-Server Metadata"
November 2001, InstantDoc ID 22265
Round-Robin Auto-Filegrowth
One way to get good performance from your database is to stripe the data over as many physical disks as possible. For data files, the best-performing solution is using a hardware-based RAID-10 controller. However, you don't always have the budget to implement such an I/O subsystem, so you might choose to use several regular disks or several RAID 1 controllers and place each data file on a separate disk, thereby achieving the striping that SQL Server performs anyway.
If you choose this method, you need to beware of one gotcha. SQL Server applies a proportional fill algorithm to the data files, meaning that it writes data proportional to the free space available within the file among the files in the filegroup. This algorithm results in all the files filling up at the same time. If you use the auto-growth feature of database files, SQL Server uses a round-robin algorithm to determine which file to expand when the database gets full. This means that only one file expands when the database gets full, and all the following inserts are directed to it and not to the other files because it's the only file with free space. When this file gets full, SQL Server expands another file, and so on.
For better performance, manually expand the files before they get completely full, making sure to expand all files, not just one. You can use an automated script that expands the files before the database gets full if their fill percentage passes a certain threshold that you determine.
—Itzik Ben-Gan, March 2004
Localize Components
The first step in ensuring Data Transformation Services (DTS) package portability is to localize all components. Component localization means that all the workings your DTS package requires must be present on the local system. This is important because a DTS package executes on the system from which you initiate it. If you initiate a DTS package from your workstation, the processing takes place locally. Therefore, all objects, third-party add-ins, ODBC or OLE DB drivers, and access to data sources must be available on all workstations and servers from which a DTS package might be executed. If any of these things are missing from the initiating system, you'll get errors such as Unable to create object.
—Shane Dovers, "DTS on the Move"
June 2003, InstantDoc ID 38658