SQL State error messages
search cancel

SQL State error messages

book

Article ID: 54762

calendar_today

Updated On:

Products

IDMS IDMS - Database IDMS - ADS

Issue/Introduction

Where can I locate the SQL State error messages for the Visual DBA product?

For example while trying to connect Visual DBA to the IDMS Mainframe, it fails with the message: SQL State =IM002?

  • From the Visual DBA Node Menu, Client selects one of the nodes, hits the 'Connect DOM' button, in the 'CA IDMS DriverConnect' panel, enters Userid and Password and hits the 'OK' button. The connect fails with the following message:
        CA IDMS Connect status for SQL connect
        SQL state = IM002. 

Resolution

The SQL State error messages for the Visual DBA or IDMS Server products are located in the Microsoft's ODBC Programmer's Reference. For the most common values encountered, see the table at the end of this document.

To resolve two particular errors, IM002 & IM003, see the details below.

IM002 means the Data source name not found and no default driver specified.

The resolution is to check if a valid Data Source name has been provided. If no data source is defined, one must be created. If none was specified, one must be chosen from the existing data sources. If one was provided, then the definition must be verified. All these tasks can be done in the ODBC Administrator, which can be accessed as follows:

Start -> Programs -> Computer Associates -> Advantage -> CA-IDMS Server -> ODBC Administrator. Once the menu opens, get current on the Data Source that is being used to connect to the mainframe, and click 'Configure'.

If Visual DBA is using an existing data source, verify the definition. Use the 'Test' button to validate that the definition is correct. If no definition is specified, one must be created here. In creating the driver, or verifying it, the IDMS Driver must be specified. 

Another reason for the IM002 error is if IDMS Server is not installed on that PC and therefore the CA IDMS Driver is not available.
The IDMS Server PC-based components must be installed on every PC where Visual DBA is to be executed.

IM003 means the Specified driver could not be loaded.

One resolution is to check if the C:\CA_APPSW directory is included in the system path. This setting can also be checked in the Windows/Control Panel/System/

System Properties/Advanced/Environment Variables tab. The 'User Variables' for your PC should contain:

   Variable                  Value 
    Path                   C:\CA_APPSW 

Another reason for the error is if CAICCI-PC is not installed. CAICCI-PC can be installed off the IDMS Server, or Visual DBA CDROM.

To verify CAICCI-PC is on the PC; go to Start/Programs/CAICCI NT Applications should be listed. Another way to check is in Windows/Control Panel/System/System Properties/Advanced/Environment Variables/. In the User Variables the Path statement should include C:\CA_APPSW. The C drive should contain a folder called CA_APPSW.

Other commonly-encountered SQLSTATE values, and their meanings, follows :

SQLSTATE Error
01000 General warning
01001 Cursor operation conflict
01002 Disconnect error
01003 NULL value eliminated in set function
01004 String data, right-truncated
01006 Privilege not revoked
01007 Privilege not granted
01S00 Invalid connection string attribute
01S01 Error in row
01S02 Option value changed
01S06 Attempt to fetch before the result set returned the first rowset
01S07 Fractional truncation
01S08 Error saving File DSN
01S09 Invalid keyword
07001 Wrong number of parameters
07002 COUNT field incorrect
07005 Prepared statement not a cursor-specification
07006 Restricted data type attribute violation
07009 Invalid descriptor index
07S01 Invalid use of default parameter
08001 Client unable to establish connection
08002 Connection name in use
08003 Connection does not exist
08004 Server rejected the connection
08007 Connection failure during transaction
08S01 Communication link failure
21S01 Insert value list does not match column list
21S02 Degree of derived table does not match column list
22001 String data, right-truncated
22002 Indicator variable required but not supplied
22003 Numeric value out of range
22007 Invalid datetime format
22008 Datetime field overflow
22012 Division by zero
22015 Interval field overflow
22018 Invalid character value for cast specification
22019 Invalid escape character
22025 Invalid escape sequence
22026 String data, length mismatch
23000 Integrity constraint violation
24000 Invalid cursor state
25000 Invalid transaction state
25S01 Transaction state
25S02 Transaction is still active
25S03 Transaction is rolled back
28000 Invalid authorization specification
34000 Invalid cursor name
3C000 Duplicate cursor name
3D000 Invalid catalog name
3F000 Invalid schema name
40001 Serialization failure
40002 Integrity constraint violation
40003 Statement completion unknown
42000 Syntax error or access violation
42S01 Base table or view already exists
42S02 Base table or view not found
42S11 Index already exists
42S12 Index not found
42S21 Column already exists
42S22 Column not found
44000 WITH CHECK OPTION violation
HY000 General error
HY001 Memory allocation error
HY003 Invalid application buffer type
HY004 Invalid SQL data type
HY007 Associated statement is not prepared
HY008 Operation canceled
HY009 Invalid use of null pointer
HY010 Function sequence error
HY011 Attribute cannot be set now
HY012 Invalid transaction operation code
HY013 Memory management error
HY014 Limit on the number of handles exceeded
HY015 No cursor name available
HY016 Cannot modify an implementation row descriptor
HY017 Invalid use of an automatically allocated descriptor handle
HY018 Server declined cancel request
HY019 Non-character and non-binary data sent in pieces
HY020 Attempt to concatenate a null value
HY021 Inconsistent descriptor information
HY024 Invalid attribute value
HY090 Invalid string or buffer length
HY091 Invalid descriptor field identifier
HY092 Invalid attribute/option identifier
HY095 Function type out of range
HY096 Invalid information type
HY097 Column type out of range
HY098 Scope type out of range
HY099 Nullable type out of range
HY100 Uniqueness option type out of range
HY101 Accuracy option type out of range
HY103 Invalid retrieval code
HY104 Invalid precision or scale value
HY105 Invalid parameter type
HY106 Fetch type out of range
HY107 Row value out of range
HY109 Invalid cursor position
HY110 Invalid driver completion
HY111 Invalid bookmark value
HYC00 Optional feature not implemented
HYT00 Timeout expired
HYT01 Connection timeout expired
IM001 Driver does not support this function
IM002 Data source name not found and no default driver specified
IM003 Specified driver could not be loaded
IM004 Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
IM005 Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
IM006 Driver's SQLSetConnectAttr failed
IM007 No data source or driver specified; dialog prohibited
IM008 Dialog failed
IM009 Unable to load translation DLL
IM010 Data source name too long
IM011 Driver name too long
IM012 DRIVER keyword syntax error
IM013 Trace file error
IM014 Invalid name of File DSN
IM015 Corrupt file data source