Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)
share|improve this question
add comment

1 Answer

With the limited information to hand, I'd guess that you've defined a User DSN, where it should be a System DSN.

If that isn't it, we'd minimally need the exact error message(s) seen when things fail. A client-side ODBC trace would probably reveal more than a SQL Server log.

Good luck!

share|improve this answer
    
Thank you for the information. I will turn on tracing and go from there. –  idon'twearsuits Jun 4 '12 at 15:21
    
If you would kindly direct your attention to an edit I made you will find a small portion of the trace file. –  idon'twearsuits Jun 5 '12 at 21:56
    
The errors you've revealed are essentially cosmetic, as they are related to some third-party-driver-specific options which are not implemented in the driver you're using. –  TallTed Jun 6 '12 at 15:21
    
Some missing details which may matter... Version of Windows? ODBC Driver name and version? bitness of Worldship, Windows, and Driver? is DSN actually System or User? Complete DSN configuration? Any of these may provide the key -- or they may just help narrow the field of possibility. You might also try another ODBC driver, such as my employer's offering -- uda.openlinksw.com/odbc-sqlserver-st –  TallTed Jun 6 '12 at 15:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.