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

[Oscar]

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.

[MikeSpragg]

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

[Oscar]

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

[jperona]

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.

[Oscar]

Hi John Perona.

The SQL above were copied of Executed SQL section.

Regards

[MikeSpragg]

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).

[Oscar]

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

[Oscar · Accepted solution]

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 

[RJLedger]

I've seen this happen on upgrades before. It "depends" on what version/sp/hf level you start with and where you are "going".

[Oscar]

You're right!!

I do a upgrade from SLX 6.1 to SLX 7.2.0

Thanks to all