11/26/2024 6:25:50 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.
|
|
|
|
Parsing Error
Posted: 14 Aug 07 1:27 PM
|
Here is the scenario:
I am creating a recordset in SLX using the following...
Set objRS_DATA = objSLXDB.GetNewRecordSet
objRS_DATA.Open strSQL, objSLXDB.Connection
The SQL statement executes successfully when logged into SLX as Admin. When any other user executes the SQL I get a "Failed to Parse SQL" error.
I have confirmed that they are executing the EXACT same statement. It appears that when not logged in as Admin it fails to parse the "AND..." statements that are part of the LEFT JOIN clauses. I gather this from the SLXProfiler error messages.
Any ideas? The SQL is below.
SELECT
A.ACCOUNT AS CLIENTNAME,
CC.FIRSTNAME + ' ' + CC.LASTNAME AS CLIENTCONTACT,
CO.SO_NUMBER AS SONUMBER,
ISNULL('Cell-' + CC.MOBILE + ' ','') + ISNULL('Work-' + CC.WORKPHONE,'') AS CLIENTCONTACTNUMBER,
O.TYPE AS INSTALLTYPE,
CO.TENTINSTALLDATE AS INSTALLDATE,
CO.TENTINSTALLTIME AS INSTALLTIME,
ISNULL(RQ.MALLNAME, OA.ACCOUNT) AS INSTALLLOCATION,
ISNULL(OAAD.ADDRESS1,'') + ' ' + ISNULL(OAAD.ADDRESS2,'') +
' / ' + ISNULL(OAAD.CITY,'') +
', ' + ISNULL(OAAD.STATE,'') +
' ' + ISNULL(OAAD.POSTALCODE,'') AS LOCATIONADDRESS,
CO.SPACENUMBER AS SPACENUMBER,
CO.SHIP_NOTES AS STORAGENOTES,
MC.FIRSTNAME + ' ' + MC.LASTNAME AS PROPMGRNAME,
ISNULL('Cell-' + MC.MOBILE + ' ','') + ISNULL('Work-' + MC.WORKPHONE,'') AS PROPMGRNUMBER,
OC.FIRSTNAME + ' ' + OC.LASTNAME AS OPSMGRNAME,
ISNULL('Cell-' + OC.MOBILE + ' ','') + ISNULL('Work-' + OC.WORKPHONE,'') AS OPSMGRNUMBER,
TC.FIRSTNAME + ' ' + TC.LASTNAME AS TCNAME,
ISNULL('Cell-' + TC.MOBILE + ' ','') + ISNULL('Work-' + TC.WORKPHONE,'') AS TCNUMBER
FROM OPPORTUNITY O
INNER JOIN ACCOUNT A ON A.ACCOUNTID = O.ACCOUNTID
INNER JOIN C_OPPORTUNITY CO ON CO.OPPORTUNITYID = O.OPPORTUNITYID
LEFT OUTER JOIN CONTACT CC ON CC.ACCOUNTID = A.ACCOUNTID AND CC.ISPRIMARY = 'T'
LEFT OUTER JOIN ACCOUNT OA ON OA.ACCOUNTID = CO.SITEREQ_ACCTID
LEFT OUTER JOIN C_ACCT_REQ RQ ON RQ.ACCOUNTID = OA.ACCOUNTID
LEFT OUTER JOIN ADDRESS OAAD ON OAAD.ADDRESSID = OA.ADDRESSID
LEFT OUTER JOIN CONTACT MC ON MC.ACCOUNTID = OA.ACCOUNTID AND MC.TITLE = 'Mall Manager'
LEFT OUTER JOIN CONTACT OC ON OC.ACCOUNTID = OA.ACCOUNTID AND OC.TITLE = 'Operations Manager'
LEFT OUTER JOIN CONTACT TC ON TC.ACCOUNTID = OA.ACCOUNTID AND TC.TITLE = 'Tenant Coordinator'
LEFT OUTER JOIN OPPORTUNITY_CONTACT PC ON PC.OPPORTUNITYID = O.OPPORTUNITYID AND PC.SALESROLE = 'Installer'
LEFT OUTER JOIN CONTACT PCC ON PCC.CONTACTID = PC.CONTACTID
LEFT OUTER JOIN ACCOUNT PA ON PCC.ACCOUNTID = PA.ACCOUNTID
LEFT OUTER JOIN OPPORTUNITY_CONTACT DC ON PC.OPPORTUNITYID = O.OPPORTUNITYID AND DC.SALESROLE = 'Driver'
LEFT OUTER JOIN CONTACT DCC ON DCC.CONTACTID = DC.CONTACTID
LEFT OUTER JOIN ACCOUNT DA ON DCC.ACCOUNTID = DA.ACCOUNTID
LEFT OUTER JOIN OPPORTUNITY_CONTACT EC ON EC.OPPORTUNITYID = O.OPPORTUNITYID AND EC.SALESROLE = 'Electrician'
LEFT OUTER JOIN CONTACT ECC ON ECC.CONTACTID = EC.CONTACTID
LEFT OUTER JOIN ACCOUNT EA ON ECC.ACCOUNTID = EA.ACCOUNTID
WHERE OPPORTUNITYID = 'O6UJ9A000UBJ' |
|
|
|
Re: Parsing Error
Posted: 14 Aug 07 3:33 PM
|
When you are logged on as a user other that ADMIN the provider adds a security clause to the SQL. Run the SLX Profiles, both as ADMIN and another user, so you can see the SQL that the provider sends to the DB Engine.
Once you see what is going on you should be able to figure out how to restructure your SQL satement so the provider will do what you want. |
|
|
|
Re: Parsing Error
Posted: 30 Aug 07 3:38 PM
|
| I used profiler and in fact the SQL in the error log is identical to that being executed under the admin login. Perhaps it is failing before it add the security clause? |
|
|
|
Re: Parsing Error
Posted: 30 Aug 07 6:23 PM
|
If it runs when you are logged on as ADMIN, it is not failing before the security stuff is added - otherwise it would not run logged in as ADMIN 
Please double check - using SLX Profiler (not SQL Server Profiler) - it's easier. This is something which we've all come across before and is definitely the most likely reason for your problems. |
|
|
|
Re: Parsing Error
Posted: 31 Aug 07 8:14 AM
|
Also, what version of SLX?
The profiler in version 6 when there were errors on the Parsing didn't show the failed statement, only an entry on the Error tab stating that an error had ocurred.
If you are running SLX Version 7, then my advice would be to hide the complexity of the Statement in an SQL view |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|