Troubleshooting inconsistent ODBC connectivity
- by Chris
I'm attempting to integrate UPS WorldShip with a SQL Server 2008 R2 database but the connection is very inconsistent. UPS claims this is a DSN/Windows problem and I have not been able to convince them otherwise.
The integration is quite simple: my shipping guy clicks a button which opens a form where he enters an order #. After pressing enter the shipping information will be pulled from the database for that order #.
The problem is that WorldShip often times thinks the DSN does not exist. However, I am able to open WorldShip's customization tool and browse all the tables and fields in the database my DSN is connected to which means at the very least my DSN does, in fact, exist.
The reason this has been so difficult to troubleshoot is because there is no consistency to the problem and I'm not able to reliably repeat any behavior. That is to say that rebooting the PC doesn't cause the connection to break and opening the integration tool and viewing the tables and fields doesn't cause the integration button to work.
Is there some way for me to monitor this connection from the SQL server or get any clues as to why it fails?
As requested by TallTed here is a sample of the trace file I created. After a mere 5 hours the trace file was over 130MB so there's no way I could provide it in its entirety.
WorldShipTD d94-690 EXIT SQLSetStmtAttrW with return code -1 (SQL_ERROR)
SQLHSTMT 0x0C6632A0
SQLINTEGER 1227 <unknown>
SQLPOINTER [Unknown attribute 1227]
SQLINTEGER -5
DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0)
WorldShipTD d94-690 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x0C662FC0
SQLHANDLE * 0x03EBCE38
WorldShipTD d94-690 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x0C662FC0
SQLHANDLE * 0x03EBCE38 ( 0x0C6632A0)
WorldShipTD d94-690 ENTER SQLSetStmtAttrW
SQLHSTMT 0x0C6632A0
SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>
SQLPOINTER 30
SQLINTEGER -5
WorldShipTD d94-690 EXIT SQLSetStmtAttrW with return code -1 (SQL_ERROR)
SQLHSTMT 0x0C6632A0
SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>
SQLPOINTER 30
SQLINTEGER -5
DIAG [HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106)
WorldShipTD d94-690 ENTER SQLGetDiagFieldW
SQLSMALLINT 3
SQLHANDLE 0x0C6632A0
SQLSMALLINT 1
SQLSMALLINT 4
SQLPOINTER 0x00520708
SQLSMALLINT 12
SQLSMALLINT * 0x0028E2A8
WorldShipTD d94-690 EXIT SQLGetDiagFieldW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 0x0C6632A0
SQLSMALLINT 1
SQLSMALLINT 4
SQLPOINTER 0x00520708
SQLSMALLINT 12
SQLSMALLINT * 0x0028E2A8 (10)
WorldShipTD d94-690 ENTER SQLGetInfoW
HDBC 0x0C662FC0
UWORD 77 <SQL_DRIVER_ODBC_VER>
PTR 0x03EBCEDC
SWORD 100
SWORD * 0x0028E290
WorldShipTD d94-690 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0x0C662FC0
UWORD 77 <SQL_DRIVER_ODBC_VER>
PTR 0x03EBCEDC [ 10] "03.51"
SWORD 100
SWORD * 0x0028E290 (10)
WorldShipTD d94-690 ENTER SQLSetStmtAttrW
SQLHSTMT 0x0C6632A0
SQLINTEGER 1228 <unknown>
SQLPOINTER [Unknown attribute 1228]
SQLINTEGER -5
WorldShipTD d94-690 EXIT SQLSetStmtAttrW with return code -1 (SQL_ERROR)
SQLHSTMT 0x0C6632A0
SQLINTEGER 1228 <unknown>
SQLPOINTER [Unknown attribute 1228]
SQLINTEGER -5
DIAG [HY092] [Microsoft][ODBC Microsoft Access Driver]Invalid attribute/option identifier (86)
WorldShipTD d94-690 ENTER SQLGetDiagFieldW
SQLSMALLINT 3
SQLHANDLE 0x0C6632A0
SQLSMALLINT 1
SQLSMALLINT 4
SQLPOINTER 0x00520708
SQLSMALLINT 12
SQLSMALLINT * 0x0028E2A8
WorldShipTD d94-690 EXIT SQLGetDiagFieldW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 0x0C6632A0
SQLSMALLINT 1
SQLSMALLINT 4
SQLPOINTER 0x00520708
SQLSMALLINT 12
SQLSMALLINT * 0x0028E2A8 (10)
WorldShipTD d94-690 ENTER SQLSetStmtAttrW
SQLHSTMT 0x0C6632A0
SQLINTEGER 1227 <unknown>
SQLPOINTER [Unknown attribute 1227]
SQLINTEGER -5
WorldShipTD d94-690 EXIT SQLSetStmtAttrW with return code -1 (SQL_ERROR)
SQLHSTMT 0x0C6632A0
SQLINTEGER 1227 <unknown>
SQLPOINTER [Unknown attribute 1227]
SQLINTEGER -5
DIAG [HY092] [Microsoft][ODBC Microsoft Access Driver]Invalid attribute/option identifier (86)
WorldShipTD d94-690 ENTER SQLGetDiagFieldW
SQLSMALLINT 3
SQLHANDLE 0x0C6632A0
SQLSMALLINT 1
SQLSMALLINT 4
SQLPOINTER 0x00520708
SQLSMALLINT 12
SQLSMALLINT * 0x0028E2A8
WorldShipTD d94-690 EXIT SQLGetDiagFieldW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 0x0C6632A0
SQLSMALLINT 1
SQLSMALLINT 4
SQLPOINTER 0x00520708
SQLSMALLINT 12
SQLSMALLINT * 0x0028E2A8 (10)