Today I was dealing with this error message. It was generated by a report that uses a SQL command with a parameter. The parameter was a numeric and when we replaced the parameter with a specific number, the error went away. Putting the parameter in the same spot generated the error.  So as part of troubleshooting I started taking out pieces of SQL until there was very little left of the query, and the error remained.  So we were surprised to find that moving the same SQL to a new report worked fine, even with the parameter.  The customer was ready to recreate the report from scratch.

Then she made a comment that she had once deleted and recreated a parameter to fix a similar problem.  Unfortunately that hadn’t worked for her today.  But this rang a bell, so I asked her if she had used the same parameter name when recreating the parameter.  She had, so I deleted the parameter and created a new parameter with a different name. I used the new parameter in place of the old one and the SQL ran correctly with no error message.  It appears that this parameter name, which she had used successfully in several other reports, had somehow gone flaky inside this report.  She tried to rename the working parameter back to its original name and the same error popped up.  So the problem wasn’t with the SQL Syntax or with the value but something specific to that parameter name in that one report.

So if you run into a similar problem with a SQL Command parameter and nothing else seems to fix it, here is one more thing you can try.

A few months back I wrote about Sage (Peachtree) dropping the built in Crystal license from their 2015 accounting software. Today I heard from a customer who has already upgraded and realized something that I had missed.  There is no longer the ability to run Crystal Reports from within the Sage menu. You can still write reports in Crystal if you have it.  You can even use the “GetPeach” custom functions.  But Crystal Reports can now be run only outside of Sage software.

This is important because it involves extra costs. To run the reports outside of Sage you either have to have a copy of Crystal Reports, or a third party report viewer. Since Crystal Reports is licensed by “named user” an office would need to have a separate Crystal Reports license for each person running reports – even if they all ran the reports on the same machine.  Even with just a handful of users you would be talking thousands of new dollars.  Fortunately these reports can also be run from third party viewer programs which are much less expensive.  A viewer lets users open, refresh, preview, print and export reports.  The difference is that the viewers don’t allow you to modify the reports. So I expect to see Sage customers shopping for viewers in the next year.

If you are not familiar with the available viewers you can read my annual comparison.

I have just updated my comparison of RPT management utilities for 2014. These are tools that allow you to scan, document, compare and update RPT files.

One vendor has closed shop since last year’s list. Within a month or so I expect one new product to be added and one existing product to release a major upgrade. Also, one of the current vendors is looking to sell the rights to their software. The current list of tools is below:

Report Miner by the Retsel Group
Find it EZ Code Search Professional by Find it EZ Software Corp.
Report Analyzer by Cortex Systems
.rpt Inspector 3 Professional Suite by Software Forces, LLC

This post will only be useful for developers who create reports that need to be launched from an application. I have written before about property called “Overriden Qualified Table Names” or OQTN. Filling in this property makes it easier to point the report to a different connection at runtime. The OQTN property starts out empty for each table. To use it you go into the properties of a table in “Set Datasource Location” and enter that table’s name into the OQTN property. You have to do this for each table, view or stored procedure in the report. It surprises me that this property isn’t filled in automatically at the time the table is added, like the table alias property.

A few months back a user contacted me because Continue Reading »
Automatically fill in “Overridden Qualified Table Names”