Error Handling

When a SQL-DMO operation fails, it returns an error number and description. This error can be one of the following:

A SQL Server error from the sysmessages table.
A DB-Library error. Error numbers range from SQLOLE_E_DBLIBFIRSTERROR through SQLOLE_E_DBLIBLASTERROR.
A SQL-DMO error. Error numbers range from SQLOLE_E_FIRSTERROR through SQLOLE_E_LASTERROR.

Each SQL-DMO error number has a SQLOLE_E_ERROR error constant. Use these SQL-DMO error constants (instead of hard-coded error numbers) in your program.

SQL-DMO errors are grouped into categories. Each category has a SQLOLE_ECAT_CATEGORY category constant. To get the category constant, binary And the SQL-DMO error number with SQLOLE_ECAT_MASK.

The binary And operator in Visual Basic is And.
The binary And operator in C++ is &.

The following table describes each error category:


SQL-DMO Error Category	Description


SQLOLE_ECAT_INVALIDCONTEXT	The requested operation is not valid in the current context.
SQLOLE_ECAT_INVALIDOBJECT	The SQL-DMO object is no longer valid.
SQLOLE_ECAT_ INVALIDOBJECTDEFINITION	The attempt to add a new SQL-DMO object to a collection failed because the properties of the new object were set incorrectly or incompletely.
SQLOLE_ECAT_INVALIDPARAMETER	An invalid parameter was passed to a method or property.
SQLOLE_ECAT_INVALIDPLATFORM	The client or SQL Server platform does not support the requested operation. The version of SQL Server is not supported, the SQL-DMO stored procedures are not properly installed, or the client platform is not supported.
SQLOLE_ECAT_ITEMNOTFOUND	The object name passed to a collection was not found, or the ordinal value passed to a collection was out of range.
SQLOLE_ECAT_UNPRIVILEGEDLOGIN	The current login ID does not have sufficient privilege to perform the requested operation.
SQLOLE_ECAT_EXECUTION	An error occurred when executing a Transact-SQL query.
SQLOLE_ECAT_CONNECTION	The connection to SQL Server was broken and cannot be restored.
SQLOLE_ECAT_RESOURCE	The client or SQL Server does not have sufficient resources to perform the requested operation.

Visual Basic

By default, any SQL-DMO run-time error that occurs is fatal. This will stop execution of your Visual Basic program and display an error message. To handle SQL-DMO run-time errors in your program, you must use an On Error statement.

Use an On Error Resume Next statement to continue program execution at the statement immediately following the statement where the error occurred.
Use an On Error GoTo HandlerLabel to branch program execution to the HandlerLabel label, making the error handler active.

Vtable-binding

If your version of Visual Basic supports early, vtable-binding, then it supports the Visual Basic Err object, which is used to return SQL-DMO run-time error information to the program. The Err object includes properties that can be used as shown in the following table:


Use	To Get


Err.Number - vbObjectError	SQL-DMO error number
Err.Description	SQL-DMO error description
Err.Source	SQL-DMO component name

Note that you must subtract the Visual Basic constant vbObjectError from the Err.Number property to get the SQL-DMO error number.

For example:

On Error Resume Next
oSQLServer.Connect "myserver", "mylogin", "mypassword"
If Err.Number <> 0 Then
    Debug.Print Err.Source & " Error " & _
        Err.Number - vbObjectError & ": " & _
        Err.Description
End If

DispID-binding, late-binding

If your version of Visual Basic supports dispID-binding or late-binding, then it supports the Err and Error functions, which are used to return SQL-DMO run-time error information to the program. The Err function returns the SQL-DMO error number, and the Error function returns the description of the SQL-DMO error.

For example:

On Error Resume Next
oSQLServer.Connect "myserver", "mylogin", "mypassword"
If Err <> 0 Then
    Debug.Print "SQL-DMO Error " & Err & ": " & Error(Err)
End If

C++

You should use the OLE macros SUCCEEDED(hr) and FAILED(hr) with the HRESULT hr returned by a SQL-DMO function to determine if that SQL-DMO function was successful.

SUCCEEDED(hr) will return TRUE if the HRESULT indicates that the SQL-DMO function succeeded.
FAILED(hr) will return TRUE if the HRESULT indicates that the SQL-DMO function failed.

For example, to use the FAILED macro:

if FAILED (hr = pSQLServer->Connect (TEXT("myserver"),
    TEXT("mylogin"), TEXT("mypassword")))
{
    // Connection to SQL Server failed
}
else
{
    // Established connection to SQL Server
    // Use pSQLServer object
}

To use the SUCCEEDED macro:

if SUCCEEDED (hr = pSQLServer->Connect (TEXT("myserver"),
    TEXT("mylogin"), TEXT("mypassword")))
{
    // Established connection to SQL Server
    // Use pSQLServer object
}
else
{
    // Connection to SQL Server failed
}

Use the OLE macros HRESULT_CODE(hr) or SCODE_CODE(hr) to get the SQL-DMO error number from the HRESULT hr returned by a SQL-DMO function.

Use the SQL-DMO macro SQLOLE_ECAT(hr) to get the SQL-DMO error category from the HRESULT hr returned by a SQL-DMO function that failed.

SQL-DMO supports the OLE IErrorInfo interface for passing rich error information back to the program. Use the following steps to get rich SQL-DMO error information:

Call the OLE GetErrorInfo function to get an OLE ErrorInfo object (a pointer to the IErrorInfo interface).
Use the ErrorInfo functions, including GetDescription and GetSource, to get rich SQL-DMO error information.
Release the ErrorInfo object.

For example:

if FAILED(hr = pSQLServer->Connect (TEXT("myserver"),
    TEXT("mylogin"), TEXT("mypassword")))
{
    // Connection to SQL Server failed
    // Get OLE ErrorInfo object
    LPERRORINFO pErrorInfo = NULL;
    GetErrorInfo (0, &pErrorInfo);
    // Get SQL-DMO error information
    BSTR strDescription, strSource;
    pErrorInfo->GetDescription (&strDescription);
    pErrorInfo->GetSource (&strSource);
    // Display SQL-DMO error information
    // The SQL-DMO error code is HRESULT_CODE(hr)
    pErrorInfo->Release ();
    SysFreeString (strDescription);
    SysFreeString (strSource);
}