1,930
We are currently using 2016 and installed the year end update.
Now when we try to print the AP checks we get an error message "Cannot access SQL data'
I tried this from workstation and server
I logged in as sa
I also added this to the dex.ini file
Finally I tried printing with standard form as opposed to modified form
I also renamed the Report Dic.
Nothing helped - same error message.
Please advise.
Thanks for your input..
In my case it wasn't the "DBA" field that is causing the problem.. Even after exporting every single custom report as a .package file, I couldn't find any references to "DBA" or "VENDDBA"..
I made some research with Notepad++ which allows multiple file content research and focused on the keyword "PM_Vendor_MSTR" as Derek mentioned that too in various tables as being related.
I then imported each one of those reports individually but the problem would not occur.
Next I focused on "receipt" and "invoice receipt date" as those fields seemed to have gone thru some changes as well.
As I started importing those reports too, I tried to print the Batch Edit List between each import and it ended up failing after the "POP Purchase Order Status Detailed By Line" import.
At this point I replaced the REPORTS.DIC with the last good working version and replaced the custom POP report by the one from GP to compare the two layouts. Above is the original GP report from 2018/18.3.. Keep in mind that the original upgraded custom version was created back in 2010 or 2013, thus some fields like Work Flow Status are missing. With the original POP report mentioned, the Batch Edit List report would print just fine. But re-importing the custom POP report would corrupt the REPORTS.DIC right away with no way to fix it, other than replacing it with a good version.
Other than that, there were a couple fields removed, but not many added, except for the RF section where the Grand Total field was possibly replicated from the F1 section. Below is the custom version upgraded from 2013 that was failing in 2016 already.
Nevertheless, there are too many differences between the 2 reports to quickly identify which field is the culprit.. Maybe Derek Albaugh could come up with a quick suggestion. There is no fancy calculated field, as the "dReceiptDate" was already there and still is.
If I'd have to make a bet, I'd say it is related to the missing WF status field..
Hey Beat,
I worked with our support partner and we found a solution.
We started with our test gp with a fresh set of forms and reports dic, and imported our old reports and forms, product by product, backing up the .dic as we went, testing to see if the issue surfaced.
in the end, it was a few Dynamics GP reports that used the DBA rather than VENDDBA, like Derek mentioned on 5 Jan 2021. Once we had isolated the reports that referenced DBA instead of VENDDBA, we restored from a package backup, we opened the trouble packages with text editor, and modified them by replacing DBA with VENDDBA when the table referenced was PM_Vendor_MSTR.
We then re-imported the updated report packages, and we were able to run / print batches with no further issues. I hope this helps.
Thank you Derek for your help!
Thanks Derek for your feedback.
I've gone thru all of those steps and none of them helped.. The field does exist in every single of those tables.. I spent quite some time to run several validations, using GPPT Database Validation, re-creating indexes and even re-creating some of the key tables, but nothing changed the issue.
I then followed the advice of running a repair, first on the actual GP folder, which didn't change anything, and then by creating a new folder with the repair function.
When using the 'stripped' down GP version (without re-installing anything else), the PM Batch Edit List report would come up.
I then tried to update some of the module dictionaries from the bad GP folder with the newly created ones, as I noticed quite some size differences (not in version though), but that didn't change a thing.
Lastly I renamed the REPORTS.DIC (which had already been rebuilt recently due to WennSoft custom reports not working either), and that was the trigger.
It was clear to me that among the some 30+ custom reports for GP, there was one or more causing this problem
I started removing the POP custom reports first, one-by-one, and trying to print the Batch Edit List in between.. but alas, that would not work. Even after I had removed all the custom reports, the report would still not print. To me the REPORTS.DIC was corrupted by the moment the .package file was imported with all the customizations.
Here is a list of the custom reports.. I'm going to export them one by one individually from GP 2016 (in which the Batch Edit list still prints fine) and then import them one after the other to find out which one is causing the problem
I went also thru the previous reports upgrade logs and none of them shows any field or signs of errors related to the new 'invoicereceiptdate' field.
I'll report back once I'm thru all of the list.
PS: Errata: I just validated that on the GP2016 client the PM Batch Edit List report fails too with the same SQL error.. GP build 16.00.0814 (R2).
We haven't seen this issue with PM10300 like we have the issue with PM00200, which I think I mentioned on another forum, but the issue is similar.
When we have seen this issue, it was in regards of the dex procedures not getting created for the table to expect the new Invoicereceiptdate column in the PM10300 table. This column is also in the PM10000, PM20000, PM30200 and POP30300 tables, which you can use these scripts to verify:
select invoicereceiptdate from PM10000
select invoicereceiptdate from PM20000
select invoicereceiptdate from PM30200
select invoicereceiptdate from POP10300
select invoicereceiptdate from POP30300
What we've had to do is rename an existing GP directory, then run Repair against it to get a 'new' GP directory. From this 'new' GP directory, login to Dynamics GP and re-create the dex procedures via the SQL Maintenance table for this PM10300 table, if not all 6 of these that I mentioned.
Another thing to verify in existing and 'new' Dynamics GP instances is that when you go into the Resource Descriptions, these 6 tables make reference of the new InvoiceReceiptDate field/column. If it is missing, that is usually a bad sign that the GP code didn't get updated.
If the above doesn't resolve the issue, we would want to export all modified forms and reports into a package file and look to see if any of them reference these tables, if so, we'd want to look at that as well.
More cases than not, it is the GP code folder that seems to get stuck with incorrect table references, missing this column.
Let us know what you find out.
Thanks
Hi jgilker
I made more testing with my customer and this is really troubling.. GP version is currently 18.3.1200 and the problem that is caused by the POP Receivings Posting Journal report is very curious and I can reproduce it at will.. Derek Albaugh need to look at this more closely and try to resolve it quickly as it's causing major hindrance to many customers apparently.
I was able to find a temporary fix.. By temporary I mean that soon as I close or switch company, the fix is gone and the problem happens again..
Here's is how I can reproduce the problem:
1. create a POP Receivings transactions and assign it to a batch. Print preview of the transaction itself works just fine
2. Open the Batch and use the Print Edit list button.. The report called on screen (POP Receivings Posting Journal) will crash with the usual SQL error. If you try to post the batch, the same happens too.
I tried the SQL maintenance, no results. Use the DMU (Dynamics Maintenance Utility) to re-create all SPs & Functions related to the core GP dictionary (0), no luck either. Ultimately what worked was using the PSTL and go the Toolkit part to recreate SQL objects.
Any functions you would use from this window is going to do the trick. Select simply the company's Purchasing module, the table PM00200 will come up as first, and then use either recreate Index, or StoredProcs or even the 'reindent' function will work.
Once the process is completed, just go back to the Batch post window, click on the Print icon and the report will print just fine on screen.
When exiting GP or switching to another company, the report will fail again. I repeated this process multiple time and even changed table in PSTL, any one of the Purchasing module will do the job. The whole bug leaves me speechless as I couldn't find really what could cause this.
I have the same issue, but when attempting to print a batch in Purchasing -> Transactions -> Batches.
I also tried adding the following to the dex.ini and rebooting, but that did not resolve the issue.
Our accounting found a workaround where you go to the latest transaction (Purchasing -> Transactions -> Batches -> transactions -> back (bottom left of screen arrow)), then print with the report, it would work.
I exported the PM Transaction Posting Journal modified report, and updated the DBA to VENDDBA like the prior comment mentioned. Then I reimported the report, but the issue persisted.
The issue is on the default and the modified version of the PM Transaction Posting Journal report.
I've also tried re-importing the report from the REPORTS.dic, and a backup of a REPORTS.dic via Report Writer, but that did not resolve the issue.
I ran enabled SQL logging in DEX.ini, and reproduced the error. When I checked the DEXSQL.log, I found the following line at the time the error came up:
SWSTATE:(SW041) Native Err:(41):*/
Number of results columns doesnt match table definition.*/
I then checked all of the tables the report references (SY00500, PM10000, PM00200, PM40102 and 'Payables Journal Distributions Temporary File'), and nothing seems out of place. PM00200 even has the new VENDDBA column, and we have no issues with Vendor setup.
*EDIT* I have updated a test SET file to use an empty REPORTS.dic, then launched GP. After assigning the default report to my account profile, and trying to print a batch again, the issue is not their anymore.
I need the modified version of the report, so I still need to find a solution. Exporting and re-importing the modified forms and reports did not resolve the issue.
Derek,
This is interesting, as I had to debug yesterday a similar situation, but it was around the table PM10000 and not PM00200.
The issue was about printing a PM batch before posting or trying to print the Job Costing Batch Edit list, both would fail with "Number of results columns doesn't match table definition"error message and the report would print out half-way blank or with zeros instead of the real values.
We ended up renaming the REPORTS.DIC & WSRPTS.DIC files (the client had WennSoft Job Costing installed) and then the report would print out correct, even if the actual requested reports were not customized at all.. After re-importing the .package files for the customizations, the report would print OK.
This was very weird, and I tracked the bug back to the intermediate upgrade from GP 2013 to GP 2016 were the problem would happen too.
This is actually something we're currently investigating.
If you capture this issue in a dexsql.log, most likely you'll find that an error stating 'number of results doesn't match table definition' or similar, and the cause being a dex procedure for the PM00200 table, i.e. zDP_PM00200SS_1, for example.
We've seen various things fix this type of issue, ruling out third parties, using the Repair process to generate a 'new' GP directory completely, but what seems to be the more common fix is doing the following:
1. Export all your modified reports, if not everything, from the Customization Maintenance window in Dynamics GP, into a package file.
2. Open the package file in NotePad or another text editor.
3. Search for any field value showing as 'DBA' in reference to the 'PM_Vendor_MSTR' table, which is the PM00200 table.
This will look something like this:
= Table "PM_Vendor_MSTR"
{
~Fields
{
- TableField "DBA"
}
}
Change this to be 'VENDDBA', which is the new column that is added to the PM00200 table via the year-end update for both Dynamics GP 2016 and 2018/18.3.
***NOTE: Do NOT do a 'replace all' changing every 'DBA' to 'VENDDBA' as we only want to change those field values related to the PM00200/PM_Vendor_MSTR table.
= Table "PM_Vendor_MSTR"
{
~Fields
{
- TableField "VENDDBA"
}
}
4. Save changes to the package file, then import the package file back into Customization Maintenance, which should overwrite the reports with the updated information. At that point, you can test this issue with the AP checks to see if the issue is resolved or not.
If it doesn't resolve the issue, we may need you to open a support case so we can look at the issue with you.
As mentioned, it's something we've begun to see and are still investigating. While we haven't been able to re-create in-house, we have been able to do so using a package file from a customer experiencing this same issue with AP checks.
Please let me know if you have any questions.
Thank you
Is there a 'More Info' button? If so what is there? You are not using Mekorma to print checks, are you?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,537
Super User 2024 Season 2
#2
Martin Dráb
228,520
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (