Solved

UPS Worldship 2014 ODBC connection problem to SQL Server 2008

Posted on 2014-04-23
  • Windows 7
  • MS SQL Server 2008
  • MS SQL Server
  • Windows Networking
  • Databases
  • +1
15
4,328 Views
Last Modified: 2016-11-23
I have two intermittent problems with a Windows 7-32 bit Dell Optiplex 3010 connecting using ODBC to a SQL Server 2008 database using Dymanics GP 2013.   I'm using both SQL Native Client 10.0 and 11.0.  

The first problem is the Worldship 2014 software cannot connect to the Import Key map every Monday Morning and sometimes other mornings.   It will try to bring up the key for about 10 seconds then the key shows up but will not connect to the server.  The key is used to pull in the Sales Order address info.  When it does work, it takes 2 seconds to connect then pops up the import key window.  It will work the rest of the day after that.  I have to reboot every Monday and that works about 80% of the time.  20% of the time I will have to go reconfigure the ODBC 32-bit DSN Connection.  I just test it.

UPS Tech Support had me setup two test machines in my office and they both would not error out so they told me it's an isolated issue with my factory PC.

Things I've tried;
Connected the PC in my office over the weekend to change the LAN. Still error-ed out.
Change the NIC.
Removed all sleep and power settings.
Tried ODBC SQL Driver 10.0 and 11.0.
Removed Trend Micro antivirus.
Reinstalled UPS Worldship 2014.
Can not find any events in logs for both the PC and Server.  
Deleted and recreated the ODBC maps in Worldship 2014.  
Tried a ODBC Data Source Trace but it never connects with the trace on and just fills the log.

The second problem is with the Export Map back to my database.  It's set to write the UPS Tracking Number back to a table in SQL at the End of Day processing.  This works about 80% of the time.  When it doesn't write the data to the table, I can go to the PC and manually export the data to the SQL table and that works every time.  

I've attached the generic UPS error messages but they do not create an event.

This problem started on the Windows XP PC after upgrading to Worldship 2014.  I lived with it for a month because I was upgrading to the Windows 7-32 PC.  The problem has continued on the new PC.
UPS-ERROR-3-24-14.png
UPS-ERROR.png
0
Comment
Question by:Chinook50
  • 7
  • 7
15 Comments
 

Author Comment

by:Chinook50
ID: 40018800
Update:

The automatic export on the End Of Day process failed today.  I went out and tried to export the data manually.  It paused for about 10 seconds then gave me the attached error message.

I closed Worldship, opened it up and was able to transfer the data manually then.
UPS-EXPORT-ERROR-4-23-14.png
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 40025985
Run profiler on sql server and see if the client is even reaching it. If it does, see what happens next. If it does not, run network sniffer (microsoft network monitor, wireshark etc.) on the client and see if it actually tries to connect. If it does, run sniffer on the server and see if connection attempt reaches it.
0
 

Author Comment

by:Chinook50
ID: 40027530
Thanks vadimrapp1.  I will run those tools and see what I find.
0
 

Author Closing Comment

by:Chinook50
ID: 40051024
Since vadimrapp1 has pointed out the tools to monitor the network traffic between the workstation and server, I have not had a single error in 10 days.  I have logged all the traffic.  I will continue to monitor each day.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40051175
Yes, when the problem knows it's being watched in the right way, it will lower its head. In fact I'm sure it did as soon as it knew that E-E has joined the battle!
0
 

Author Comment

by:Chinook50
ID: 40059398
I was able to capture data this morning when the UPS workstation tried to connect to the SQL Server.  I have identified the SQL call that timed out and I have the Microsoft Network Monitor capture of the conversation.  I also have data from other days when the connection worked successfully.  

Can anyone help me decipher the data?   The capture file and the monitor trace are not accepted extensions per EE.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40059747
> the capture file and the monitor trace are not accepted extensions per EE.

1. see if you can use www.ee-stuff.com .

2. change file extension to .txt to full e-e

3. upload it on some other website and give the link.

4. encode it into text and post here; you can use website like http://base64-encoding.online-domain-tools.com/ , or utility like uuencode.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Chinook50
ID: 40059796
Thanks vadimrapp1.  

Uploaded both files to ee-stuff.com under id 40059747

https://filedb.experts-exchange.com/incoming/ee-stuff/8356-5-12-14.caphttps://filedb.experts-exchange.com/incoming/ee-stuff/8357-5-12-14.trc
https://filedb.experts-exchange.com/incoming/ee-stuff/8358-5-5-14.trc


The use is sa and the application us UPS Worldship.   When the connection works, all ten lines in the trace are together.  In this one 5-12-14, the first three lines from sa are together, other users lines, then the next two sa lines.  The first one shows the call, then pauses for 15 seconds.  

I will upload the trace from 5-5-14 when it worked.  All sa lines are together.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40059856
thanks, will examine it tonight.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40059942
<sales pitch>We also used worldship for a while, importing the orders (actually, the packlists) from the database, but then created an application that generates the shipment directly by using UPS and Fedex webservices API's. This application also utilized another API from SolvingMaze.com, so it actually knows to ship not 10 items but 2 larger boxes these 10 items are packed into. Much more accurate, less errors, and the cost and tracking number gets directly into the database at once. If interested, click my profile link.</sales pitch>
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40060447
Looking at the Profiler 5-12-14, we can see report server's batch at 6:52:24.383, which has UPDATE [Event] WITH (TABLOCKX) in it, i.e. it exclusively locks the table. Probalbly that's why when Worldship sends exec sp_tables, which enumerates all tables in the database,  it has to wait until report server releases. In the trace 5-5-14. Worldship is lucky because by the time it sends exec sp_tables, report server's exclusive updates have been completed 3 seconds before.

One way to address this is to try to convince UPS developers that unless you are are designing a mapping, enumerating all tables in the database is unnecessary.  Another is remove TABLOCKX hint from report server's query, if you can. Yet another is schedule report server's event to some time when his exclusive locks on tables won't interfere with others.
0
 

Author Comment

by:Chinook50
ID: 40062196
Thanks vadimrapp1 for finding that.  I've tried working with UPS on the issue and they've given up on me.    I've see other UPS users have issues with the enumerating all the tables when we only access one.  

I just started using SSRS reports last year so I've started running the Report Manager on my production server which is the report server job.  The TABLOCKX query runs every 10 seconds and I've found some other users that are having the same issue.   I also found where to turn it off by I'm not sure the ramifications yet.  I will work that angle and appreciate your help.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40062219
We were using UPS worldship integration for a long time, and it's indeed because of instability that we eventually developed our own solution that replaced it. Specifically, I recall that it was exactly end-of-day upload that was lottery-like event.
0
 

Author Comment

by:Chinook50
ID: 40063296
OK, so after doing some research on the Reporting Service settings I decided to try and turn these services off:

 IsSchedulingService
IsNotificationService
IsEventService

I have a small user base that runs all SSRS reports on demand so I currently don't need Scheduled Reports or Subscriptions.  

I will keep monitoring Worldship to see if it errors out again.  You are right on the End-Of-Day process but it should be easy.  I'm writing 20 to 30 tracking numbers back once a day.  If I continue to have problems with Worldship, I'll look into the software you mentioned.

Thanks again for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Log Backup 2 13
Dell PowerConnect 6248 switch - set to unmanaged mode? 5 10
SQL JOIN + SUBQUERY? 3 6
Stored procedure 23 0
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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

18 Experts available now in Live!

Get 1:1 Help Now