We are up against one heck of an issue right now related to several very basic gadgets on our interactive dashboards. Gadgets that previously worked in 7.1.d no longer work in v2017. The errors we're seeing within the CRM log (and crmtomcat7-stderr if we turn logging up) are the following:
ERROR [http-apr-10009-exec-6]: 09-Mar-2018 08:56:01.184 20. PreparedStatement.executeQuery() SELECT oppo_description FROM OpportunityHistory WITH (NOLOCK) WHERE oppo_opportunityid='55284'java.sql.SQLException: Invalid column name 'oppo_opportunityid'.
The gadget generating the error is a List gadget based on Opportunity using a saved search for all opportunities in progress for the current user. This saved search when run from the Find --> Opportunity screen works just fine.
To further troubleshoot, I created a report using the same fields defined/selected for the list gadget, and based it on the current user (therefore producing the same records). When I create another list gadget, but base it on the report I just created, I get the same error in the logs.
For some reason, it really wants to get the Oppo_OpportunityID from OpportunityHistory, where it of course doesn't exist.
How can I fix this? I have similar issues with other gadgets based on Waves:
ERROR [http-apr-10009-exec-5]: 09-Mar-2018 15:35:13.525 8. PreparedStatement.executeQuery() SELECT camp_name FROM Campaigns WITH (NOLOCK) WHERE wait_waveitemid='178'java.sql.SQLException: Invalid column name 'wait_waveitemid'.
I've been searching but can't find anything similar to this on the community. Have you logged a case?
If you are working with the Saved Searches associated with the Opportunity Search screen then you would be using the view vSearchListOpportunity.
This does not include the OpportunityHistory table.
If you recreate the gadget does the error occur?
Hi Jeff, we haven't logged a case yet, but that will be our next step.
If we delete the gadget and re-create it (for Opportunities, or Leads, or some others), we get the same kind of error referring to some bad SQL referencing a column that doesn't exist in the source it's querying. It's extremely strange.
Our thinking is it must be some old metadata or schema somewhere that it's referencing. We've refreshed metadata a number of times mind you, and it didn't change anything.
I've talked with a couple of colleagues about this. I think someone in support will have seen this before. I am sure that I've seen a discussion of something very similar on the community but I can't for the life of me find it.
Thanks Jeff. I've come across a few posts that speak about the error the gadget throws ("An error occurred when attempting to process the Gadget. Please contact your System Administrator"), and some mentioning the "bad SQL grammar", but none have had a resolution that worked. I'll begin the process of opening a case - any advice as to the best way to do that?
Do you have a Sage business partner? They should be able to do that for you.
The links for Canada should be here:
Hi Jeff, I created a case on customers.sagenorthamerica.com, and they've requested that I call them as the issue is not conducive to email support (canned answer). I will give them a call and update everyone with what the issue was!
We are having a very similar issues with 2017 R3 and spent hours one the phone with Sage Support and there is no resolution as to why we can't use most of our gadgets.
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT comp_name FROM OpportunityHistory WITH (NOLOCK) WHERE comp_companyid=?]; nested exception is java.sql.SQLException: Invalid column name 'comp_companyid'.
Did you find a resolution for this issue. Sage is telling me that they are working on a fix for 2018 R3, but we have been running 2017 R3 for over 6 months without an issue until this morning and nothing has changed in the software/server.
Hi Tom, after weeks we were told the same thing you were. Sage CRM 2018 R3 would correct the issue. Did you happen to upgrade from a much earlier version of Sage CRM? We moved from 7.1.d.1 to 2017 R3 and ran into the issue immediately. I vaguely recall that the issue is only present if a customer has taken this kind of upgrade path. Users that start off on more modern versions of CRM don't encounter this.
Sage did provide a little more detail and said the issue stems from the search select advanced field oppo_primarycompanyid. Something to do with the datatype. I know that's super vague and I'm not quite sure I understand how this manifests into a problem all of a sudden.
Make sure all OpportunityHistory rows in Custom_Edits have the proper colp_customtableidfk. In one of the 7.x versions, it was being set to the wrong value.
We are working with a client that consistently requests a fix for this issue. My colleague informed me that she has been in touch tech support on the issue and has been told that the issue has been resolved in CRM 2018 R3. However, the client is currently on CRM 2017 R3. Upgrade is not sitting very well with them because the gadgets worked fine previously.
Is there a patch that can be run on CRM 2017 R3 to resolve this issue? Desperately seeking a resolution to remove the steam rolling off their heads! :-)
This client has 129 active users in Sage CRM across different time zones. Therefore, users are accessing the system almost 24 hours a day. Therefore, they don't want to take a pause right now to upgrade.
Any assistance that can be provided will be greatly appreciated. I will take a look at HwangL's recommendation. However, I am not sure I will know what the proper value should be.
I did review the ColP_CustomTableIDFK for OpportunityHistory in Custom_Edits and it does appear to be wrong...
Therefore, I ran the following SQL statement (in a sandbox of the client's live system. I DID NOT DO THIS IN THEIR LIVE SYSTEM. Sorry, I was hearing this little voice in my head (Jeff Richard's) saying "STEP AWAY FROM THE SQL". LOL)
Update t1 set Colp_CustomTableIDFK = '128' From Custom_edits as t1 Where ColP_ColPropsId = '12281'
I then did a meta data refresh and checked the dashboard. Still getting the error.
Is there anything else that needs to be done?
© The Sage Group plc 2017All Rights Reserved