Solved

Intermittent ODBC Connection Problems with UPS Worldship

Posted on 2014-10-27
8
83 Views
Last Modified: 2016-06-15
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.cpp, 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!
0
Comment
Question by:IMC-IT
  • 4
  • 3
8 Comments
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40407718
Have you tried running a trace on the database itself? If the connection times out, it might be a database lock or another issue on the database itself that's preventing the data from being returned. The problem might not be with the ODBC connection itself, but with the database failing to return the expected result.
0
 

Author Comment

by:IMC-IT
ID: 40408393
Is there a way to run a SQL trace on the server without crippling the system?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40408447
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:IMC-IT
ID: 40408711
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?
0
 

Author Comment

by:IMC-IT
ID: 40410912
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_columns N'SHIPCARRIERSTAGING',N'dbo',N'DynamicsAXR2PROD',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??
0
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 500 total points
ID: 40410968
Hmm, not really. On the plus side, you've confirmed your ODBC is not at fault. Can you contact your local Dynamics support for help? Most likely you'll need the input from someone who knows how dynamics works on the inside.
0
 

Author Comment

by:IMC-IT
ID: 40453714
I'm working with our AX var but they don't seem to know anything either, unfortunately.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now