Oracle Transparent Gateway Microsoft SQL Server Administrator's Guide
Release 8.1.6 for Windows NT

Part Number A82868-01




Go to previous page Go to next page

Diagnosing Problems

This chapter provides information about diagnosing problems and understanding error messages. The following sections are included:

Problem Identification

When you encounter a problem, you first should determine where the problem occurred. The following is a list of possible problem areas and references to more information about those areas.

Installation and Configuration Issues

The issues surrounding installation and configuration usually involve verifying if the installation process was successful, and wanting to know what to configure.

How do I verify that the installation succeeded?

Review the installation log file, which lists any problems encountered during installation. By default, the file has the following name:


where orainventory_location is the directory where the OraInventory is located.

Does the Oracle Installer configure everything?

No. The Oracle Installer simply installs the software. You must configure the software after its installation. Follow the tasks in Chapter 4, "Configuring the Gateway", to configure the gateway and its components.

Net8 Configuration Problems

If you receive errors that refer to TNS when accessing the gateway, the problem is probably with Net8 or the Net8 configuration. Following is a list of the most likely TNS errors and the actions you should take.

ORA-12154 "TNS:could not resolve service name"

Cause: The service name specified is not defined correctly in the tnsnames.ora file.

Action: Check the following items:

ORA-12203 "TNS:unable to connect to destination"

Cause: An invalid TNS address was supplied or the destination is not listening. This error can also occur because of underlying network transport problems.

Action: For the Oracle database server:

For the gateway:

If the Net8 configuration is correct but you still encounter errors, set up a loop-back link, which is a database link in the Oracle database server that points back to itself. If Net8 is working properly you can access a table in the Oracle database server using a loop-back link. For example:

SELECT * FROM table@loop

If this link does not enable you to access a table in the Oracle database server, see the Net8 documentation for the platform for information about troubleshooting Net8.

See Also:

For more information about configuring the listener.ora and tnsnames.ora files, see "Configuring Net8 Listener for the Gateway" and "Configuring the Gateway for Multiple Microsoft SQL Server Databases"

ORA-12500 "TNS:listener failed to start a dedicated server process"

Cause: The process of starting the Oracle database server failed. The Oracle database server executable could not be found or the environment is set up incorrectly.

Action: Check these items:

  • Verify that the SID value specified in the tnsnames.ora file matches the gateway SID.

Common Gateway Problems

Many common gateway problems are caused by errors in the initialization parameter file, and are described in this section. For other errors, review the log files for more information. If no log file exists, the gateway might not have started. In that case, Net8 configuration might be the problem.

See Also:

"Oracle Support Services"

ORA-02068: following severe error from database_link_name

ORA-01012: not logged error_found_in_init_file

Cause: One or more gateway initialization parameters might be configured incorrectly or there are syntax errors.

Action: Review the gateway log file for error messages that describe the problem. Correct the inittg4msql.ora file as needed.

ORA-02068: following severe error from database_link_name

ORA-03113: end-of-file on communication channel

Cause: The HS_FDS_TRACE_LEVEL parameter is set to an invalid value in the initialization parameter file, or the target database is not set up correctly.

Action: Ensure the values are set correctly and the target database is set up correctly. Refer to the documentation for the target database.

ORA-02085: database link database_link_name. WORLD connects to database_link_name.domain_name

Cause: The Oracle database server has GLOBAL_NAMES set to TRUE in its initialization parameter file. This error occurs because the value specified by HS_DB_DOMAIN in the initialization parameter file, displayed as domain_name in the error message, does not match a domain name of WORLD.

Action: Change the value of HS_DB_DOMAIN in the initialization parameter file to WORLD.

ORA-02085: database link database_link_name1.domain_name connects to database_link_name2.domain_name

Cause: The Oracle database server has GLOBAL_NAMES set to TRUE in its initialization parameter file. The value specified by HS_DB_NAME in the initialization parameter file (database_link_name1) must match the value of the database link used by the gateway (database_link_name2). The error occurs because the values do not match.

Action: Change the value of HS_DB_NAME in the initialization parameter file to match the database link name being used.

Analyzing Error Messages

This section describes the following topics:

Message and Error Code Processing

The gateway architecture includes a number of separate components. Any of these components can detect and report an error condition while processing an SQL statement that refers to one or more Microsoft SQL Server database tables. An error condition can be complex, involving error codes and supporting data from multiple components. In all cases, the application receives a single Oracle error code upon which to act.

Error conditions are represented in the following ways:

Oracle Database Server Error Codes

The Oracle database server messages and error codes are documented in Oracle8i Server Messages. Refer to that publication for information about interpreting the Oracle database server messages and for information about specific error codes.

If an error is detected by the Oracle database server, gateway message lines do not occur. For example, if the gateway cannot be accessed because of a Net8 or gateway installation problem, a gateway message line is not present in the generated error.

Another example of error messages without gateway message lines occurs when an INSERT statement attempts to insert data into a table, but does not include values for all of the columns in the table. The following SQL statement causes an error message:

SQL> insert into "EMP"@MSQL values(9999);

ERROR at line 1:
ORA-00947: not enough values

The ORA-00947 message is not accompanied by gateway message lines because the error is detected by the Oracle database server. The Oracle database server obtains a description of the Microsoft SQL Server table before sending the INSERT statement to the gateway for processing. This allows the Oracle database server to detect when the INSERT statement is invalid.

Gateway Error Codes and Message Formats

Error codes are generally accompanied by additional message text, beyond the text associated with the Oracle message number. The additional text includes details about the error.

Gateway messages have the following format:

ORA-nnnnn: error_text
[Transparent gateway for MICROSOFT SQL SERVER]gateway_error_text

ORA-02063: preceding n lines from dblink



is an Oracle error number where nnnnn ranges from 28500 to 28561.


is the text of the message associated with the error.


are additional messages generated by the gateway or by Microsoft SQL Server. See "Oracle Database Server Error Codes" for more information.

The format of the gateway_error_text is:

[Transparent gateway for MICROSOFT SQL SERVER]gateway_error_text


is the total number of gateway message lines.


is the name of the database link used to access the gateway.

For example, if you get a message where nnnnn is between 28500 and 28561, it is a gateway message and that is where the error is occurring. A gateway message line such as the following means that the error originates in Microsoft SQL Server or the gateway:

[Transparent gateway for MICROSOFT SQL SERVER]

The ORA-28500 error code is returned for all errors for which a more specific error code does not exist. When the ORA-28500 error code is returned, the error might have been caused in the gateway by the Microsoft SQL Server components of the target database.


Use error tracing to pinpoint problem areas involving the gateway, the Oracle database server, and Microsoft SQL Server.

You can enable error tracing to record by setting the HS_FDS_TRACE_LEVEL gateway initialization parameter.

See Also:

For information, see Appendix C, "Heterogeneous Services Initialization Parameters"

Use tracing only while testing and debugging the application. Do not enable tracing when the application is running in a production environment because tracing reduces gateway performance.

Trace Log Files

A log file is created with each SQL session to collect the following information:

Trace files are created in the ORACLE_HOME\tg4msql\trace directory, where ORACLE_HOME is the ORACLE_HOME directory under which the gateway is installed. The trace file names have the format agent_sid_agt_process_id.trc where agent_sid is the sid for the gateway and process_id is the operating system process ID of the gateway session.

Reviewing the Trace Output

If you are having trouble accessing data from the Microsoft SQL Server database, you might receive the following messages:

ORA-00942: table or view does not exist
[Transparent gateway for MICROSOFT SQL SERVER]Object not found....
ORA-02063: preceding n lines from database_link_name

or the following message:

ORA-00904: invalid column name

To find the source of the problem, review the trace output from the gateway.

If you are certain that the identifier exists in the Microsoft SQL Server database, the trace output might show, for example, that the table or column name in the SQL statement sent to the Microsoft SQL Server database has the wrong case, or you forgot to enclose it in double quotation marks. The Oracle database server identifiers must be in uppercase unless you enclose them in double quotation marks. The case for identifiers for Microsoft SQL Server depends on whether it is case-sensitive or not.

The outgoing parsed SQL statement listed in the trace output is the SQL statement passed to the gateway and on to the target database. If you think the SQL statement might have been generated incorrectly, test the SQL statement by executing it with Microsoft SQL Server's native tools or using an ODBC application such as ODBC-Test. If you need help to execute the statement using these tools, contact the Microsoft SQL Server vendor for support.

Oracle Support Services

If you determined that the problem does involve an Oracle component, but you have not been able to solve it, gather the information and fill out the worksheet before contacting Oracle Customer Support Services:


Support for non-Oracle components is the responsibility of the vendor providing that component. 

Table 7-1  Oracle Support Services Worksheet
Required Information  Site-specific Values 

Name and full version numbers for: 


- the gateway 

Oracle Transparent Gateway for Microsoft SQL Server

Version: ____________________________________________ 

- the platform/OS on which the gateway resides 

Microsoft Windows NT____________________________ Version:__________________________________________________________

Machine Name:______________________________________ 

Versions of Net8 Server used by the gateway 

Net8 Version:________________________________________ 

Net8 Adapter used by the gateway 

Circle one: Named Pipes - SPX/IPX - TCP/IP


Full pathname to directory in which gateway resides 


Oracle database server and the platform/OS on which it resides 

Oracle___ Server, Version:____________________________


Machine Name:_____________________________________ 

Oracle SID of the Oracle database server 


Full pathname to directory where the Oracle database server is installed 


Version of Net8 Client used by the Oracle database server 


Net8 adapter used by the Oracle database server 

Circle one: Named Pipes - SPX/IPX - TCP/IP


The database and any patch information for it 

Server Name:______________________________________

Database Name:____________________________________


Listing of spool log if using SQL*Plus, or the SQL statement and error message received 

Check here:


Relevant table and view definition (a full description of the tables involved in the SQL query) 

Check here:


Description of the problem (input, output, test cases, and so on) 

Check here:


Listings of key files: 

Check here:

tg4msql_agt_pid.trc log file gathered with HS_FDS_TRACE_LEVEL

Net8 configuration files, tnsnames.ora and listener.ora

initialization parameter file


Go to previous page Go to next page
Copyright © 2001 Oracle Corporation.

All Rights Reserved.