SLX OLEDB SQL Parser

[mrogers27]

just tried to execute the following SQL in Admin, Execute SQL:

select top 1 firstname, left(firstname,2) from contact order by firstname desc

If the last firstname I have is "Victor", I would expect the query to return "Victor", "Vi" as it did in SQL 2005.

Instead I get "Victor", "0" (that is a zero)

Is there a list somewhere of  "allowed SQL Functions" for the SLX OLEDB? As my t-sql skills get better, I find my frustration with the SLX parser growing by the same scale.

[jameswsutton]

Hi There,

  I've definitely used many SQL functions before and I thought this should work.  I used another database query tool and connected through the SLX OLDDB provider and your SQL worked exactly as expected.  I got "ZsaZsa", "Zs".  I'm not sure if there's a difference in running through the Administrator but typically I use a third party OLE db tool, like ADO Explorer to test my SQL statements going through the provider.

  In general I've found the provider allows all the normal SQL functions to pass straight through to the SQL server.  I've usually run into difficulties with Sub queries or window functions but that's about it.

Regards,

James.

[RJLedger]

Although the ExecuteSQL in the Admin is supposed to be the Like James, for may years I used ADO Explorer (and still do) to connect via the SalesLogix OLEDB provider to test SQL. A little while ago Walter Shpuntoff (http://www.radishtools.com) tipped me off to a free/open source tool called Query Express - http://www.albahari.com/queryexpress.aspx. Something slightly better is Query Express Plus (a derivative of QE) -  http://sourceforge.net/projects/queryexplus/.

Running your Select statement thru ExecuteSQL should produce the desired results (if valid).  I just ran it thru QE plus as well as ExecuteSQL on a 7.5.3 system w/SQL2008 as the db and it worked fine in both cases.