UPS WorldShip and SQL Server

Wed Jan 28, 2009 by Chris Miller

So, we're trying to get UPS WorldShip to work with SQL Server 2005. So here's how it looks from the user's perspective:

Put in your order number (WorldShip pulls the shipping information)
Put in the package weight (Worldship figures out the cost)
Hit OK to confirm everything and then wait 45 seconds'
Get a tracking number

Now, our shipping department sends out 100 packages a day. That little 45 second wait thing…that's an hour and a half of someone's time just waiting. So, what's going on there.

So I did a sql profiler on what was going on.

For some reason WorldShip is pulling a full copy of my entire database schema (calls sp_tables with the "table" parameter, then calls sp_columns for EVERY TABLE IN THE DATABASE) every time someone wants to ship.

This is..um…1700 tables. Somewhat ludicrous.

So, there are two solutions for this. Since the security situation on that server is a complete mess (vendor requires it to be a complete mess) I chose to create another database, put a view in that database that pointed to the original table in the original database. The application administrator then reconfigured WorldShip to work with the new view, and everything's great now. Views: Is there anything they can't do?

The better solution would be to create a WorldShip user and only give them access to one table. That would probably work, but I'm not sure it'd work, and I'm not sure sp_tables respects all of the same conventions as a select * from sysobjects does as far as user security goes.

Legacy Comments


Scott R. 2009-01-29	re: UPS WorldShip and SQL Server Another option would be to ask UPS WorldShip why they need your entire schema metadata to fulfill your shipping request. Automatic recognition of metadata not known in advance is one thing – unlimited access to all metadata for “discovery” purposes might be considered a security breach by many organizations (and a potential risk for UPS as well, should that information be accidentally disclosed). Your work-around is admirable, but I would get to the root cause with UPS if possible, as a long term solution. How fast is the view-based solution? You didn’t state how you interact with UPS WorldShip via SQL Server, or what you have to specify in the request. Can you shed light on that? Thanks, Scott R.

rocketscientist 2009-01-30	re: UPS WorldShip and SQL Server I could ask UPS WorldShip. If I really wanted to wade through 2 hours of tech support hell. I don't get paid by UPS to tell them how their stuff is broken. If they'd like to contact me here I'd be happy to discuss this issue in depth and show them what I found, and provide them with information on how to configure a trace to get the same info. That's a generic problem with nearly all vendors of software. I know what's wrong with your software and why it doesn't work in my organization, but I don't have time to talk to "john" from overseas who I can barely understand over the horrible VOIP connection and badly trained accent and explain the problem to him when I can just work around it. That's actually why I posted here. WorldShip is a very spiffy piece of software, and I hope someone at UPS will read this and go "oh, nuts" and fix it. And use their infinite powers for good to send me a coffee mug. UPS WorldShip is a really cool application. What it does is integrate with pretty much any application you have that uses ODBC for a database. You tell WorldShip (through a mapping interface) where the addresses are you need to ship to and give it a location to place tracking numbers in your database and it will read the information, allow you to enter (or integrate with a scale) the weight of the package, and it will print out the label and save the tracking number for you. Very spiffy. The problem is that every time we ship, it activates the data mapping subsystem for no apparent reason. A simple solution is to isolate it down to one database with one object in it, basically don't let it get all distracted with the rest of the database. The best solution is to contact the vendor and wait for a patch. At one hour per person per day I'm losing to this problem, I think the expedient solution is better than the best one.

UPSer 2009-01-30	re: UPS WorldShip and SQL Server Although not directly involved in developing WorldShip, I am a UPSer in the IS community and have sent this blog entry on to the user reps for WorldShip. Hope it helps for future updates to WorldShip.

Twitter Integration 2009-02-04	re: UPS WorldShip and SQL Server This is absolutely insane! I hope somebody at Worldship is taking note.

UPSer 2009-04-05	re: UPS WorldShip and SQL Server I agree it is really bad. This situation has been there since Worldship v0.8 pre-beta! The Developers have been told about it multiple times, through 10 version of the software. The best proctice as I have found through literally 1000's of WorldShip integartions over the last 10 years is to trim the rights of the user ID that is being used for connection. Here is the issue in a nutshell: Every time Worldship opens an ODBC connection, it checks all the available tables and their structure to see what it can connect to. If it is an existing connection map, it is checkig to see what changes since the map was last used. For connections used to pull data into WorldShip, the connection is opened, does it "check" and the connection stays open until the operator closes it. However, when writing data back to the db from WorldShip, a slightly different process happens. Worldship opens the connection, does the "checking" scan, writes the record(s), then closes the connection. If you opt to do "Export on process shipment" the system will do the table check for each lable printed. If you reduce the user ID rights down to just the tables you need, the speed will be very good. If you can see tables in the map editing tool, they will be checked every time the connection is opened! I have been passing comments on this "design feature" to the developers for many years and have no hope that they will ever change it.

Jon Grimes 2009-04-23	re: UPS WorldShip and SQL Server I am having a similar problem with the import maps in worldship using CSV files. I have an orders file and a static file. the orders file contains your typical order info (name, address, etc) and then every line relates to a row in the static file (which is used to pull in thrid party shipping information that is the same for every order). So i build my map, relate the tables, and then when i hit save and go to try it out keyed import wont work with my primary key. Instead the primary key for import has been switched to the ID of my static file and it does not read the orders file at all. Essentially creating relations between tables is not working at all with CSV files, does anyone know if it works with other odbc connections?. The only solution I was able to make work was to parse my static info onto the end of each of the order rows. It works but its not how a relational database is supposed to work. UPS of course says that what I'm trying to do isn't possible, because their tech support does not know how a database works. Worldship is great, I just wish it worked the way it was designed to. It would also be nice if the people who's job it is to support the software actually knew how it was being used in the real world.

Randy Murphy 2009-04-24	re: UPS WorldShip and SQL Server All I need is a way to tell the keyed import to look at the last file it see's instead of the first. I currently have to key off of a order number, but each year the order numbers start over. the system was picking the last one in the list so it worked well for a few months, then for some reason stopped and started picking the first one. I was looking to see if there was some way to tell the keyed import to select the current order or the last one in sequence. Have any of you see how to do that in WorldShip 10. Thanks