ODBC error with SQL 2000

[psperry2]

The error message: ODBC error: [Microsoft][ODBC SQL Driver] Invalid character value for cast specification.

I am using crystal report 8.5, SQL server 2000.

The datasource for the report is a stored procedure.

I tried several experiments to work around this problem:

1) I removed all the cast statements from the stored procedure and used convert instead. Same message
2) I ran the stored procedure in query analyzer and it worked without any error
3) I examined the data and found no problems to account for this message.

 

[smcnulty2000]

Another experiment to try:

In your crystal reports, go to database, show sql query.

Copy and paste that into the tool you use for running sql. There might be a clue there. I'm curious if something in your record selection formula is causing this.

 

[IanWaterman]

Unfortunately you may find that Crystal just refuses to process the SQL. I have seen this with Oracle Views, the view executes perfectly OK in Oracle but when you try to get Crystal to read it it just refuses.

I got round the problem by doing the cast (or if then else) in Crystal and it was fine. Not very efficient but it works.

Ian