IMC-IT
asked on
Intermittent ODBC Connection Problems with UPS Worldship
Good Afternoon,
I have been troubleshooting Intermittent ODBC connection issues with my UPS Worldship station to no avail.
Some background on the environment:
We use Microsoft Dynamics AX as our ERP system. This system uses a SQL Server 2012 backend.
I am connecting to the SQL Server via an ODBC connection with a 32-bit Windows 7 Pro workstation. The workstation is running UPS Worldship 2013 and the latest ODBC driver from Microsoft.
The problem:
The problem is tricky because it is intermittent. The UPS station sends tracking data to the SQL DB via an ODBC connection. Most of the time (roughly 90/100), the tracking information is written to a table in the DB without issue.
It is the 10 missing records each day that have me concerned.
The Logs:
UPS Worldship keeps track of errors in a log file. Unfortunately, the UPS error messages I see when a connection fails are generic. The UPS Help Desk doesn't know what they mean and Googling them gets me nowhere.
Error from Worldship: "transferinformationfile.c pp, 108 ODBC failed to get table information!!"
Last Friday I ran an ODBC trace while the warehouse shipped packages (yes it took 5-10 minutes per package!). There were three problem shipments during this time. The ODBC log errors that seem to correlate with the UPS logs read as follows (this is a single example; they're all the same):
WorldShipTD 974-a68 ENTER SQLTablesW
HSTMT 0x0DD37388
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x0DD377C0 [ 5] "TABLE"
SWORD 5
WorldShipTD 974-a68 EXIT SQLTablesW with return code -1 (SQL_ERROR)
HSTMT 0x0DD37388
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x0DD377C0 [ 5] "TABLE"
SWORD 5
DIAG [S1T00] [Microsoft][ODBC SQL Server Driver]Query timeout expired (0)
WorldShipTD 974-a68 ENTER SQLErrorW
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC
SDWORD * 0x0021D768
WCHAR * 0x0021D2FC
SWORD 511
SWORD * 0x0021D76C
WorldShipTD 974-a68 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC [ 5] "S1T00"
SDWORD * 0x0021D768 (0)
WCHAR * 0x0021D2FC [ 56] "[Microsoft][ODBC SQL Server Driver]Query timeout expired"
SWORD 511
SWORD * 0x0021D76C (56)
WorldShipTD 974-a68 ENTER SQLErrorW
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC
SDWORD * 0x0021D768
WCHAR * 0x0021D2FC
SWORD 511
SWORD * 0x0021D76C
WorldShipTD 974-a68 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC
SDWORD * 0x0021D768
WCHAR * 0x0021D2FC
SWORD 511
SWORD * 0x0021D76C
Things I've tried:
(none of this worked)
-Pointed the ODBC driver to the IP address of the DB server instead of the hostname.
-Disabled all client security software.
-Updated the NIC drivers.
-Updated the ODBC driver.
-Tried another workstation (exact config).
-Disabled the TCP chimney.
I also ran wireshark traces on the UPS machine and SQL server. I didn't see any connection failures and the handshakes all looked good.
Any help will be greatly appreciated!
I have been troubleshooting Intermittent ODBC connection issues with my UPS Worldship station to no avail.
Some background on the environment:
We use Microsoft Dynamics AX as our ERP system. This system uses a SQL Server 2012 backend.
I am connecting to the SQL Server via an ODBC connection with a 32-bit Windows 7 Pro workstation. The workstation is running UPS Worldship 2013 and the latest ODBC driver from Microsoft.
The problem:
The problem is tricky because it is intermittent. The UPS station sends tracking data to the SQL DB via an ODBC connection. Most of the time (roughly 90/100), the tracking information is written to a table in the DB without issue.
It is the 10 missing records each day that have me concerned.
The Logs:
UPS Worldship keeps track of errors in a log file. Unfortunately, the UPS error messages I see when a connection fails are generic. The UPS Help Desk doesn't know what they mean and Googling them gets me nowhere.
Error from Worldship: "transferinformationfile.c
Last Friday I ran an ODBC trace while the warehouse shipped packages (yes it took 5-10 minutes per package!). There were three problem shipments during this time. The ODBC log errors that seem to correlate with the UPS logs read as follows (this is a single example; they're all the same):
WorldShipTD 974-a68 ENTER SQLTablesW
HSTMT 0x0DD37388
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x0DD377C0 [ 5] "TABLE"
SWORD 5
WorldShipTD 974-a68 EXIT SQLTablesW with return code -1 (SQL_ERROR)
HSTMT 0x0DD37388
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x00000000 <null pointer>
SWORD -3
WCHAR * 0x0DD377C0 [ 5] "TABLE"
SWORD 5
DIAG [S1T00] [Microsoft][ODBC SQL Server Driver]Query timeout expired (0)
WorldShipTD 974-a68 ENTER SQLErrorW
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC
SDWORD * 0x0021D768
WCHAR * 0x0021D2FC
SWORD 511
SWORD * 0x0021D76C
WorldShipTD 974-a68 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC [ 5] "S1T00"
SDWORD * 0x0021D768 (0)
WCHAR * 0x0021D2FC [ 56] "[Microsoft][ODBC SQL Server Driver]Query timeout expired"
SWORD 511
SWORD * 0x0021D76C (56)
WorldShipTD 974-a68 ENTER SQLErrorW
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC
SDWORD * 0x0021D768
WCHAR * 0x0021D2FC
SWORD 511
SWORD * 0x0021D76C
WorldShipTD 974-a68 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0x0DCF58B8
HDBC 0x0DCFBB90
HSTMT 0x0DD37388
WCHAR * 0x0021D6FC
SDWORD * 0x0021D768
WCHAR * 0x0021D2FC
SWORD 511
SWORD * 0x0021D76C
Things I've tried:
(none of this worked)
-Pointed the ODBC driver to the IP address of the DB server instead of the hostname.
-Disabled all client security software.
-Updated the NIC drivers.
-Updated the ODBC driver.
-Tried another workstation (exact config).
-Disabled the TCP chimney.
I also ran wireshark traces on the UPS machine and SQL server. I didn't see any connection failures and the handshakes all looked good.
Any help will be greatly appreciated!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sure. Running traces shouldn't have a huge impact as far as I know. But you might want to be selective in what activity you log. That's a bit beyond my expertise though. Do you have a DBA to help you out?
ASKER
I have a SQL Server Profiler trace running filtered by the Worldship user. It doesn't seem to be causing any performance issues. I will post back if I get anything interesting.
Does anyone know anything about the logs I've already posted?
Does anyone know anything about the logs I've already posted?
ASKER
We got a live one!
I finally got an error in the Worldship machine log while the SQL Profiler trace was running.
All of the successful transactions have three "RPC:Completed" events, then it runs the insert statement.
Successful transactions look like this before the insert statement:
Audit Login
RPC:Completed - exec sp_tables NULL,NULL,NULL,N'''TABLE'' '
RPC:Completed - exec DynamicsAXR2PROD..sp_colum ns N'SHIPCARRIERSTAGING',N'db o',N'Dynam icsAXR2PRO D',NULL
RPC:Completed - exec sp_tables NULL,NULL,NULL,N'''VIEW'''
Audit Logout
Failed transactions look like this and then no Insert statement takes place:
Audit Login
RPC:Completed - exec sp_tables NULL,NULL,NULL,N'''TABLE'' '
Audit Logout
Thoughts??
I finally got an error in the Worldship machine log while the SQL Profiler trace was running.
All of the successful transactions have three "RPC:Completed" events, then it runs the insert statement.
Successful transactions look like this before the insert statement:
Audit Login
RPC:Completed - exec sp_tables NULL,NULL,NULL,N'''TABLE''
RPC:Completed - exec DynamicsAXR2PROD..sp_colum
RPC:Completed - exec sp_tables NULL,NULL,NULL,N'''VIEW'''
Audit Logout
Failed transactions look like this and then no Insert statement takes place:
Audit Login
RPC:Completed - exec sp_tables NULL,NULL,NULL,N'''TABLE''
Audit Logout
Thoughts??
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm working with our AX var but they don't seem to know anything either, unfortunately.
ASKER