Sage 50 error in Windows 11

Saturday 10 June 2023 @ 5:12 pm

If you are using Sage 50 (PeachTree) on Windows 11 and your Crystal Report gets the SQL error below, I think I have a fix for you.

The error says:

Failed to retrieve data from the database
Details: ADO Error Code: 0x
Source: Pervasive PSQL OLE DB Provider
Description: [LNA][Pervasive][ODBC Engine Interface]Syntax error
[Database Vendor Code: 268435456]

The error means that Crystal is generating Pervasive SQL that has bad syntax. In the case I faced the same report would work on an older system but not on the Windows 11 system. Other reports were fine in Windows 11 but not this one.

First we were able to narrow it down to a subreport. Then I found that the subreport would work with no selection criteria so we knew it was an error in the WHERE clause. We took out each rule in turn to narrow it down to the Date criteria. The failure only occurred when there was a Date rule in the selection formula.

I took the faulty SQL that Crystal generated and tried running it as a command a few different ways until I figured out the Pervasive SQL was looking for a DateTime value instead of a Date Value. I changed the selection formula to use DateTime values and the syntax error went away. Here is the syntax I used with two date parameters:

{JrnlHdr.TransactionDate} in
DateTime({?FromDate}, Time(0,0,0)) to DateTime({?ToDate} , Time(0,0,0))

If you have a date range parameter you could use:

{JrnlHdr.TransactionDate} in
DateTime(Minimum({?DateRange}), Time(0,0,0)) to
DateTime(Maximum({?DateRange}), Time(0,0,0))


I can’t say for sure this error only occurs in Windows 11 since I found the exact same error in a forum question from 2018 (before Windows 11 was released).

Update – 6/24/2023:

In the forum discussion above a Sage specialist reports that the error is related to CR 2008 SP1, and that upgrading to CR 2008 SP2 solves the problem. You can check your version by looking up the full Crystal Reports version number on the [Help > About] screen.   If your version starts with  12.1 you are on CR 2008 SP1.  If it starts with 12.2 you are on SP2.   If your version doesn’t start with 12, then you aren’t using CR 2008. 

And thanks to John Dierkhising of Bottom Line Accounting for point this out.





Using CR 2020 with Raiser’s Edge data

Thursday 25 May 2023 @ 11:35 am

I just wrote about installing 64-bit drivers to get CR 2020 to work with Raiser’s Edge legacy MDB files. Here are a few other things I learned:

1) If you modify a Raiser’s Edge report using CR 2020 you are saving it connected to a 64-bit data source. Raiser’s Edge will still run the report just fine using 32-bit connections. Apparently, when RE runs the report automatically replaces any connections in the report with the connection  configured in RE. This was a happy surprise.

2) When running reports from inside other applications you can export the report to .rpt format. This saves the report with data, letting you open it in Crystal Reports and page through the saved data. When I tried this from RE the saved data would generate an error. It appears that RE adds a new formula to the report when it is run.  This formula uses a proprietary custom function that is only available inside the RE environment. This prevents the report’s saved data from opening up in Crystal.

3) You can make most changes using CR 2020 without having to connect to the data at all. You wouldn’t be able to preview the changes in CR, and you wouldn’t be able to add any new tables, but most other changes can be made without a connection.

4) You can still purchase Crystal Reports 2016 and keep using the legacy connections. CR 2016 is still for sale from SAP and simplifies things when you are using legacy data like this. SAP’s web site shows that support for CR 2016 will no longer be available in 2025, but that isn’t an issue for most users. I have many customers using ‘unsupported’ versions, some from 20+ years ago, and the lack of SAP support has never been an issue.





Installing the 64-bit Access/Excel Drivers for CR 2020

Tuesday 23 May 2023 @ 9:27 am

Like many users, I have not yet upgraded to CR 2020. I connect to lots of legacy data using DAO connections (e.g. Raiser’s Edge export data). To do this using CR 2020 involves installing new 64-bit connectors and updating each report. I’ve written up instructions on installing the new drivers but I haven’t applied the steps here since I am still on CR 2016.

This week I heard from a user who has to read legacy MDB files from Raiser’s Edge, using CR 2020. They followed my instructions to download the new driver, but got an error message when they tried to install it. The error said:

“You cannot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2016, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2016:”

The funny thing is that they also tried to install the 32-bit driver and got the opposite error. So even though I don’t have CR 2020 I downloaded the 64-bit Access driver and tried to install it. I got the same error.

After a bit of research I found a way to get the driver to install without having to uninstall anything, by running the install from the command line using the ‘quiet’ switch. Here are the steps I used:

1) Download the file to my downloads directory
2) open a command line window
3) change the directory in the command line window to \downloads
4) Run the command with the “quiet” switch. It will look something like this:

C:\Users\ken\Downloads>AccessDatabaseEngine_X64.exe /quiet

You can also read the discussion where I found this method here:

https://superuser.com/questions/1557350/

After testing it here I did the same thing for Raiser’s Edge user and got them working as well. 





Why do some variables fail when you drill-down?

Saturday 18 February 2023 @ 12:02 am

I resolved another minor mystery recently after a question from a customer. It has to do with the behavior of variables whenever you open a new tab by doing a drill-down on a group. The same thing can happen when you double click on a subreport in preview mode to open the preview of that subreport.

What I had seen was that some variables would carry to the new tab others would reset to zero/blank. So when a customer explained that this was causing a problem in their report I did some digging and experimenting. I discovered that this problem only occurs with shared variables, which pass to and from subreports. Shared variables will not carry from the preview into the new tab, while default (global) variables carry just fine.

I did some more digging to find a workaround and only found a partial one. I say partial because it will work for group drill-downs but not subreport drill-downs.  If you drill down on a group, typically you will see the Group Header, the Group Footer and the details for that group. The workaround is to make a second copy of the subreport that generates the shared variable, and put it into the group header of that group. To make sure that subreport doesn’t run repeatedly you can either hide the group header, or suppress the subreport with a condition like this one:

DrillDownGroupLevel = 0

Either of these will keep the extra subreport from firing until you do the drill-down. Then one instance of the subreport will run inside the drill-down and the shared variable will carry to the details and group footer inside that drill-down tab.

The downsides are

1) The subreport will have to run again if you do another a drill-down.

2) It won’t work for a subreport because you can’t put one subreport inside another.

If you have any questions about using variables, call to schedule a session.


Added 4/2/2023 – Another solution, if you are willing to use a third party dll, is to use the “Key-Value pair” functionality in CUT Light. This allows you to “Set” and “Get” values that are independent of the report’s variable process, so they aren’t reset by the drill-down.
https://www.milletsoftware.com/CUT_Light_User_Manual/#uflLookupGetEntry()





Generating and using Null values in formulas.

Tuesday 24 January 2023 @ 8:43 pm

I was recently reminded of how handy it can be to create a “NULL” value in a formula. I had to use this in two recent situations and thought a description of these two uses would make a great blog post. But, as happens so often, I looked at my past posts and found I had written about both of these before, one in 2016 and one in 2007. So rather than write them up again I am just going to link to those posts with some brief comments.

1) If you ever need to do a distinct count of values that meet a condition. This usually means an IF THEN ELSE formula and if you don’t use a NULL as your ‘ELSE’ your distinct count is likely to be inflated by one.

2) If you want to do a minimum or maximum of values that met a condition. Again you would need an IF THEN ELSE formula and the simplest approach I have found is to use a NULL as your ‘ELSE’ value.

Note 1: Don’t think that skipping the ELSE altogether will generate a NULL value.  It will generate a default value (empty string, zero, etc.).

Note 2:  These NULL formulas are strings, so if you need an else that is a NULL numeric you can use:

Val({@Null})

If your else value needs to be a date you can use:

Date (Val({@Null}),1,1 )

These will still be null values, but they will have the correct data type for your formula.





SQL Commands using CTEs and temp tables

Friday 18 November 2022 @ 1:10 am

I have been writing lots of SQL commands for Crystal Reports recently. One of them required me to link a sub query to itself, repeatedly, to get multiple levels. Fortunately I had recently read about Common Table Expressions (CTEs) which simplify SQL commands in cases like this.
A CTE allows you to write a query, give it a name, and then refer to it throughout your SQL command as if it were a table. You would start your command with something like this:

with trans as (
select Item, Date, Amount, CustName
from Items
inner join customers on Items.CustID = Customers.CustID
Where Items.Type = 3)

You put your subquery in the parens and Trans becomes the name of the results.
After that you start your SELECT and use Trans as if it were a table with those 4 columns.

This is especially helpful when you have to use the same results multiple times, because you don’t have to repeat the SQL like you normally would with a normal subquery. If you have to change that subquery you can make the change in one place, so the SQL is more efficient to write and read.

While CTEs make writing more efficient, they don’t make the SQL processing any more efficient. Each time you reference a CTE in your SQL it has to repeat the CTE query to get results. If the subquery is complex you want to have it processed only once and then be able to refer to the results several times without having the database repeat the query. For that you switch from a CTE to a Temp table.

With a temp table the results of the query are written to an actual table (in a tempDB). Temp tables are deleted when you are done with your DB session. And because temp tables are written to tempDB you don’t even need to have “write” permissions for the main database. You can even add an index to the temp table if it is large enough to need one.

With a temp table your SQL command can read that table as many times as necessary without having to generate the data again. This can make a significant difference in the processing time of a query, especially when you have to refer to those results multiple times. It is a good idea to start the SQL by checking to see if the temp table already exists and deleting it. Otherwise you will get an error when you try to create it.

Below is an example of a command that shows how to do the CTE above as a temp table. First it drops the temp table and then it recreates it:

if Object_ID (N'tempdb..#TempItems') IS NOT NULL
BEGIN
DROP TABLE #TempItems
END;

select Items.Item, Items.Date, Items.Amount, customers.CustName
Into #TempItems
from Items Items
inner join customers customers on Items.CustID = Customers.CustID
Where Items.Type = 3 ;

If you start your command with the SQL above you can follow that with a SELECT that references the table #TempItems (all temp tables start with #). You treat a temp table just like any other table. Note that there are semicolons after each step which we didn’t need with the CTE.

If you think your reports might benefit from these techniques, call to schedule a session.





Comparison of Crystal Reports Viewers (2022)

Friday 30 September 2022 @ 5:19 pm

You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?

The most cost-effective method for letting a user run reports is to install a third-party client-based viewer. These are offered by ten different vendors.  Don’t get sidetracked by the official SAP “viewer” because that tool won’t refresh reports.  Every viewer in my list allows you to refresh reports.

Every September I update the features of these viewers. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (xls) that shows some of the specifics for comparison, like prices. I have even included a glossary of features in case you aren’t familiar with the terminology. There are a couple of new features added to this year’s matrix which are marked in blue.

There are 10 active products in this year’s review and 4 “ghost” products that are mentioned as warnings.  A ghost product has a web site but it hasn’t changed in years and no one responds to requests for information.

The active vendors are:

Crystal Corral by Groff Automation
rptView by Pursuit Technology
CR Dispatch by APB Reports
cView by Chelsea Technologies
ViewerFX by Origin Software
CrystalKiwi Viewer by CrystalKiwi
Logicity Pro by SaberLogic
Report Runner Viewer by Known Keep
RTag Report Viewer by RTag
DataLink Viewer by Millet SW

If you have feedback to provide on any of these products, I would love to know what you think.





Update to my SQL functions “cheat sheet”

Thursday 15 September 2022 @ 8:32 pm

This week I had to do something new in SQL.  I needed to take a list of multiple route records for each driver and append the routes into single string for that driver.  This is relatively simple in a Crystal report, but not so simple in a SQL Query.  After I realized how complex this would be in a normal SQL query I searched to see if there was a simpler way.  What I found was that several databases have a function specifically designed for this task.

In SQL Server and Postgre SQL it is called String_Agg().
In Oracle and DB2 it is called ListAgg().
In MySQL it is called Group_Concat().

Even though SQL is considered a “standard” language, some function names and syntax will vary from one database to another. That is why I created a “cheat sheet” a few years ago that lists the most common SQL functions (with their correct syntax) for the 8 SQL flavors I encounter the most:

      • SQL Server
      • MySQL
      • Oracle
      • MS Access
      • Pervasive
      • Progress
      • PostgeSQL
      • DB2

Today I added these “aggregate” functions to the cheat sheet along with a few other updates.  The cheat sheet is now also available on the library page of my web site.





Enlarging the icons in Crystal Reports on Hi-Res monitors

Monday 1 August 2022 @ 5:14 pm

My customers often use large, high-resolution monitors.  Sometimes the icons and menu options are microscopic – even to the local user. It is even worse for me since I am seeing their screen through GoToMeeting which reduces the screen even further.

But recently, one of my colleagues pointed me to a video that might help. It shows how to change a compatibility setting specific to high-resolution monitors.

If you are seeing microscopic icons on your Crystal toolbar, check out the video and see if it helps. And thanks to Gordon Portanier with the Crystalize consulting group in Canada for sharing this tip.  And also to Mike Pieczynski of OnPoint Consulting for sharing the video.





Desktop and Server-based scheduler comparisons (2022)

Thursday 30 June 2022 @ 9:59 pm

I have just updated my comparisons of server-based scheduling tools and desktop-based scheduling tools for 2022.  I have changed the schedule to write about both of these groups every June.  The desktop products are designed to be managed on a single PC, while the server based tools allow multiple people to manage schedules through a server.  Both provide automated delivery by Email, FTP or directly to a network folder.

There are 11 products in each comparison.  There are also a few feature updates and price changes for 2022. The pages provide a brief overview of each product. Each page also has a link to the feature matrix that compares dozens of features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.





Next Posts »» «« Previous Posts
Recrystallize Pro