Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Nickel Contributor
Posts: 58
Registered: ‎05-05-2009
Accepted Solution

SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

Hi Everyone!!!

 

I've a problem when i clicked in Sales Dashboard Icon (NavBar). Inmediately, appears this message error:

An error occurred executing active form script (Sales Dashboard: Sales). Error in include script (DASHBOARD: CONTENT SET SUPPORT): Failed to parse SQL. at line 683, char 8.

 

After, appears Dashboard Detail View, but when i choose any tab (Win Rate, Pipeline Status, etc.), this error is repeated.

 

Thanks for advance.

Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

Oscar

Use SLXProfiler.exe to find out what the SQL that is being run. Maybe able to back-step from there. Also, seriously advise you to get a virtual machine running that has both 7.2 and 7.5 on it - you can then script compare between the 2 and see if you are hitting defects already corrected (which you can then back-fix yourself).

Regards
Mike
Highlighted
Nickel Contributor
Posts: 58
Registered: ‎05-05-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

Hi Mike!

 

I use the SLXProfiler and get this SQL at line 683:

SELECT DISTINCT  'GrandTotal' AS NAME, Count(*) AS NCOUNT, CAST((SUM(CASE BT.STATUS
WHEN 'Closed - Won' THEN 1.0 ELSE 0.0 END)/COUNT(*))AS FLOAT)* 100.00 AS WINRATE,
(SUM(CASE BT.STATUS WHEN 'Closed - Won' THEN 1 ELSE 0 END)) AS WINS,
(SUM(CASE BT.STATUS WHEN 'Closed - Lost' THEN 1 ELSE 0 END)) AS LOSSES,
CAST(1.0/(1.0 + (SUM(CASE BT.STATUS WHEN 'Closed - Lost' THEN BT.SALESPOTENTIAL
ELSE 0.0 END)/(CASE WHEN SUM(CASE BT.STATUS WHEN 'Closed - Won' THEN BT.ACTUALAMOUNT
ELSE 0.0 END) = 0.0 THEN 1.0 ELSE SUM(CASE BT.STATUS WHEN 'Closed - Won' THEN
BT.ACTUALAMOUNT ELSE 0.0 END) END)))AS FLOAT)*100.00 AS AMTWINRATE,
(SUM(CASE BT.STATUS WHEN 'Closed - Won' THEN BT.ACTUALAMOUNT ELSE 0.0 END)) AS WONAMOUNT,
(SUM(CASE BT.STATUS WHEN 'Closed - Lost' THEN BT.SALESPOTENTIAL ELSE 0.0 END)) AS LOSTMOUNT
FROM Opportunity BT
WHERE (SLXDATEPART(YY, BT.ACTUALCLOSE )) = ('2009')
AND (SLXDATEPART(MM,BT.ACTUALCLOSE)) = ('10')
AND (BT.ACTUALCLOSE) <= ('20091026 08:24:35')
AND   (BT.STATUS IN ('Closed - Won','Closed - Lost'))

When i execute this SQL in Query Analyzer, it says:

Server: Msg 195, Level 15, State 10, Line 12
'SLXDATEPART' is not a recognized function name.

 

Also the error occurs at line 645 and the SQL is:


SELECT DISTINCT  BT.STAGE AS NAME, SUM(BT.SALESPOTENTIAL) AS SALESPOTENTIAL,
SUM(BT.SALESPOTENTIAL*(BT.CLOSEPROBABILITY)/100) AS WEIGHTED,
COUNT(BT.SALESPOTENTIAL) AS OPPORTUNITIES,
AVG(SLXDateDiff(DD,BT.DATEOPENED,SLXGETDATE(LOCALTIME))) AS DAYSOPEN
 FROM Opportunity BT
 WHERE  (BT.STATUS = 'Open')
 GROUP BY BT.STAGE
 ORDER BY  BT.STAGE ASC

 

In this case the Query Analyzer said:

Server: Msg 195, Level 15, State 10, Line 4
'SLXGETDATE' is not a recognized function name.

SQL Server need a functions library of SalesLogix?

 

Thanks for advance

 

Moderator
Posts: 440
Registered: ‎05-07-2008

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

SLXGETDATE and SLXDATEPART are SLX OLEDBProvider functions so you'll need to run those queries through a tool that can use the SalesLogix OleDB Provider such as ADO Explorer.

 

Alternatively, If you are using the SLX Profiler then grab the executed SQL instead of the Client Sql if you want to execute against the Native SQL Query analyzer.

John Perona

Director, Infor CRM Development

Infor CRM

Infor CRM

Nickel Contributor
Posts: 58
Registered: ‎05-05-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

Hi John Perona.

 

The SQL above were copied of Executed SQL section.

 

Regards

Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

As John says, this is an internal function. When you upgraded - did you select the option that updated the internal scalar functions (they are in the bundle and it's possible you missed them).
Nickel Contributor
Posts: 58
Registered: ‎05-05-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

How I can know if this bundle was missing for me?

 

Can I install this bundle now?

Know you the name of this bundle?

 

 

Regards

Nickel Contributor
Posts: 58
Registered: ‎05-05-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

Hello!

 

This is the solution:

As you can see above, I said that section Client SQL was identical to Executed SQL. The strange thing here is that Executed SQL section is using internal functions (SLXGETDATE, SLXDATEPART, etc.). For this, i continue research and found four related tables with SalesLogix OLEBD: 

 

- SLXOLEDBPLUGIN

- SLXOLEDBPLUGINDATA

- SLXSQLDEFAULT

- SLXSQLSCALARFN 

 

To comparing records with other SalesLogix DB, I noticed that it had missing records. For this, create a Bundle with missing records in each of these tables, I installed the bundle and works fine!

 

Regards 

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

I've seen this happen on upgrades before. It "depends" on what version/sp/hf level you start with and where you are "going".
--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Nickel Contributor
Posts: 58
Registered: ‎05-05-2009

Re: SalesLogix v7.2 - Dashboard Error: Failed to parse SQL

You're right!!

 

I do a upgrade from SLX 6.1 to SLX 7.2.0

 

Thanks to all