Sage 100

Has anyone converted an existing client to 4.5 SQL? If so, how did the conversion go, were there any problems, how is the speed, and how long did it take? thx, Leonard

Yes I converted someone from the 3.74 SQL to 4.45 SQL when it was released, and later took them from 4.45 SQL up to 4.50 SQL. Went well overall. There was one small data conversion issue on the 3.x to 4.x conversion (recurring journals). But it was small enough that the customer re-keyed that data.

I have not done any SQL though in reading many of the posts online here I've determined that the speed is not significantly faster (if at all) and I've since had a few inquiries and made sure to drive hom that message. In one case someone wanted to go to SQL and I replaced one of their ""takes all day to run"" reports with a @JeffSchwenk recommended Pass Through Query and now the report runs in three minutes and the customer sure is glad they didn't spend the $20,000 that Sage wanted to migrate to SQL. In general I think it's important to talk to the customer about why they want to go to SQL, what they can expect (most likely not faster) and to be sure you don't line yourself up for complaints if the customer had certain benefits in the back of their mind and those benefits never appear...

PTQ's Crystals are 10 X faster than a PVX odbc Crystal report for the most part. Access can remain in the background on the server and the program does NOT need to be installed on the server. Very powerful solution for someone that doesn't require the full functionality of Dan Burleson's Access mirror or DSD PVX mirror or want to pony up for MAS SQL.

And who taught everyone that trick? The reason it runs so fast is the Pass Through Queries bypass almost levels of the ODBC driver and makes the call directly to the server, hence the speed increase.

@JeffSchwenk and @LouisAraiza Let's schedule something to demonstrate this to the group - I've done the PTQ setup but missed how to use that as the source for a Crystal report. Or if it's something that can be explained - I'm open to just reading it. Basically what I did : 1. Copy each SQL statement for the file 2. Paste into ACCESS as PTQ type SQL Query How do I make the leap to use this as the source for Crystal?

Yes, I failed to give a shout out to BL as it was his idea back from a community posting in the v4.1 days which enabled us to link Worldship to the SO header table so that we could simply enter the SO # in Worldship and the UPS ship to address fields would populate with MAS data. A terrific workaround. Based on BL's posts along with input from Dan Burleson, I was able to take this a step further and use this DB method for Crystal reports and KnowledgeSync using select and union (to merge multiple companies into one report) queries.. It truely is a low end workaround to the ODBC speed issue. Dan Burleson has a nice mirror database if you are looking for more automation and there is DSD SQL mirror for larger companies.

This method has gotten so popular that at the Sage Summit a few years ago Chris Powers suggested it as way to deal with slowness in some reports.

How do you use it as a source for Crystal Reports?

When creating the report start a new connection and then select either ODBC or Database files and go from there. Make sure the database is on the server where it can be read from any work station.

When creating the Crystal, link it to the DB in this manner. This will allow you to hang it on the MAS Custom Report menu. Make sure the path is accessible to all users (I use a UNC).

@LeonardSmith - For me, 2 conversions from 3.74 SQL to 4.45 SQL (one for @JohnShaver client) and 1 from 4.40 pvx to 4.50 SQL. For John's client and the one from 4.40 it wasn't totally smooth there were a few hiccups but we overcame them. But the other 3.74 one was hellacious and contrary to the other 2 experiences. It required me to make several changes to the conversion programs. Let me sum it by just the biggest factors affecting conversion are: * Which MAS level you are coming from * Size of data you are converting. If large enough, you will run into the slow performance design bugs. This shouldn't affect most people (my client had 150 GB database) **** If your starting point is less than 4.40, do any of your file have sizes over 2 GB? (what actually happens is it segments into multiple files with no single segment exceeding 2 GB).

I have done 2 from 3.7X SQL to providex. I have sold to SQL versions but have not implemented yet.