sql expressions with ms-sql / odbc

[crystalvictim]

With Crystal Reports 8.5 we are desperately trying to build an sql expression that will extract a substring from a memo-field for further use within formulas. Unfortunately, this doesn't seem to work except with an oracle native driver.
Our problem is that we want to access a ms-sql database (or to be more specific: the Crystal Enterprise Server's own database) and get the information that is stored in APS_InfoObjects.ObjName. The connection is established via ODBC and we've been trying different MS-SQL-Drivers as well as the standard ODBC-Connection that is created by default.
But no success at all :-(
After reading some questions/answers posted here, I understand that at least some people seem to have succeeded in using sql expressions with ODBC (or even with MS-SQL). Is there any special hint or a special driver that does not ship with Crystal Reports?
I should add that unfortunately using the sql-designer is no valid option as we haven't been able to get reports based on .qry-files running on our CE-Server (but if this should turn out to be the only solution, I'll have a talk with our admin and we'll try to solve this problem).

Thanks in advance for any clues.

 

[FVTrainer]

Which version of CE are you running? In CE 8.5, APS_InfoObjects.ObjName is not a memo field. Are you running CE 8.0?

Be that as it may, it would be helpful if you provided the actual syntax you had tried in order to get the SQL Expression to work. I can get the SQL Expression to return a portion of a memo field from SQL Server via ODBC using the following syntax:

SUBSTRING(client_notes."Note",1 ,10 )

This syntax differs slightly from the normal SQL Expression syntax in that there is no {fn...} statement. It would work with that statement as well, but it is not necessary. Also, the field name "Note" doesn't show up as an item to choose in the Field Tree of the formula editor. I had to type it in myself.

As for the driver itself, I'm using the SQL Server ODBC driver supplied by Microsoft.

I hope that helps. If not, please post your syntax and what the error message/result is.

 

[crystalvictim]

FVTrainer,

thanks for your reply. We're using CE8.5. The ODBC-Driver is: SQL Server 2000.81.9001.00 from Microsoft (SQLSRV32.DLL; 2002/02/19). Nevertheless Crystal Reports regards 'ObjName' as a memo field. That's strange becase when connecting to the database from MS Access, the field is correctly recognized as string(255).

I have tried to use you syntax:
SUBSTRING(APS_InfoObjects."ObjName",1 ,10 )
(I also tried putting a ';' at the end).

As we're using the german version, I can only guess what the error message might look like in English: Something like 'error compiling sql expression'. 'not implemented'.

Meanwhile I've figured out that we would technically be able to use a workaround with SQL-Designer as datasource for the report, but that's not really the way thing should be, isn't it ? ;-)

 

[FVTrainer]

hmmm...sounds like a driver issue. My driver is SQL Server 2000.81.9041.40. And given that you're running the German version, I'd wager that is part of the issue. What version of the MDAC are you running? Is it the German (or whatever language version MS offers)? I believe my server is running 2.7x.

On another track, is using a stored procedure or view out of the question for you? That would, I should think, eliminate the issue because you could handle the substring on the server side. Or, you could run it through an Access query (which wouldn't be great for performance at all). The reason I mention that is that isn't SQL-Designer to be avoided if you are running the report on CE?

 

[crystalvictim]

I think we're runnin MDAC 2.7 (German version) so it might be a language issue.
I'm sorry, but stored procedures are out of question. I thought SQQL-Designer wasn't possible when using CE but it seems to work ;-). We've put the .qry-File someplace where the user running the ce-processes has access rights, fill the database logon-information in ce and it does the trick.

We've originally intended to use this datasource to create and periodically run a report that informs us about failed instances on the ce-server by sending an email via a ufl directly from the report if errors have occured. Alas, this works excellently on our local machine but not on the server. So we'll have to check this out first (which can take quite some time) before we'll be in need of a solution to sql expression issue.