Functions not recognized in database connectivity toolkit

[ateem]

Hi,
I'm using database connectivity toolset to retrieve data from a database using ODBC. I am trying to run this complex query:

SELECT ibmcu, shdoco, shdcto, shshan, abalph, SDDSC1, SDDSC2, t3rcto, t3amtv, t3rmk2, t3rmk, imsrtx, ibanpl, sdlitm, walotn from PRODDTA.F4211, PRODDTA.F4201, PRODDTA.F4101, PRODDTA.F4102, PRODDTA.F0101, PRODDTA.F00092, PRODDTA.F4801 WHERE shdoco=sddoco AND shdcto=sddcto AND sditm=t3sbn1 AND sditm=ibitm AND SHRORN=STR(WADOCO) AND shshan=aban8 AND TRIM(ibmcu)='FULL' AND T3SDB='I' AND T3TYDT='TP' AND shdoco=3 AND shdcto='E' AND walotn = '2'

When i don't use TRIM function, i don't get any error, but it doesn't return any data. I need to use TRIM to retrieve data, but i get fol
lowing error:

Error -2147217900 occurred at Conn Execute.vi

Possible reasons:

Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [Microsoft][ODBC SQL Server Driver][SQL Server]'TRIM' is not a recognized function name.. in Conn Execute.vi->Fetch_DeviceInfo_DB_AIE.vi

I don't know the reason. How can I solve this. Is there something wrong with the SQL query itself?
Thanks

[Crystal]

Does this exact SQL query work in SQL Server's Query Analyzer? If it works in the Query Analyzer, it should work in LabVIEW.

Microsoft isn't updating their ODBC drivers as often as they do the OLE DB providers. I'd suggest changing the driver you are using for the connection to see if it makes a difference -- the error you mention is coming from the ODBC driver and not LabVIEW. Use the Create Data Link option from the LabVIEW Tools menu to set up the connection with the OLE DB provider.

I'm only familiar with the TRIM clause as used to remove spaces or other characters from an item in the database, and the one you use is complicated enough that I'm unable to figure out exactly what it's doing. If the query returns all the data properly without the TRIM claus
e, you could probably use the LabVIEW string functions to do the same operations. Maybe if you described what the entries in the table look like and what you are trying to do with them, we could offer specific solutions.

[Russ@Davis]

To add to Crystal's repsonse, if it works in server's own SQL analyzer, does the DB have Stored Procedure capabilities? If so, create a stored procedure and make a simple call to that from LabVIEW. Stored procedures work great and allow for the query to change without necessarily having to change LV code.

Happy Wiring!!