Crystal Generates a Bad SQL Query when connecting to a stored proc.

[SDB15]

I wrote a stored proceedure that selects the top 5 most run reports in our system.
The query runs fine in SQL studio and if a co-worker logs in and puts it into a report, everything goes fine. (We are logging on to the same computer. Same version of crystal, etc.)
When I try to select the stored proceedure, i get the following error:

Query Engine Error: 'ADO Error Code: 0x80040e14
Source: Microsoft SQL Native Client
Description: Incorrect syntax near the keyword 'CONVERT'.
SQL State: 42000
Native Error: 156'

(I do not use 'CONVERT' in my query.
I noticed that in my version of crystal the sql query reads like this:

"HDSLive"."dbo"."HDS_SP_CR_Top5Reports";1 CONVERT(DATETIME, '2009-07-01 00:00:00', 120), CONVERT(DATETIME, '2009-07-15 00:00:00', 120)

When my coworker runs the report, crystal generates SQL without the 'CONVERT' operators.

Is there a setting that governs SQL generation? Why would my co-worker's SQL be different than mine?

 

[AndersonCJA]

Are your odbc system dsn's using the same driver to access the database?

 

[Turkbear]

Hi,
What is your selection formula...Is co-workers version ( both CR and the ADO drivers) the same as yours?


It looks like a extra character ( the 1 before the first CONVERT) may be the issue, since the syntax for that function is
Convert (datatype, datetime string, date style)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[joneed2nomo]

I think my problem is related to this thread so I will post it here. I have one server that will run a report that connects to a stored procedure in both Crystal Enterprise and Crystal Reports 9. On a newly created Crystal enterprise server and every PC running Crystal Reports 9, I get the following error when I try to update the report:

Query Engine Error: 'HY000:[DataDirect]{ODBC Oracle Driver][Oracle]ORA-6550:line1, column8:wrong number or type of arguments in call to 'RPT_FILE_STATUS'.....

I have verified the exact same ODBC driver is being used on both servers and my PC. I'm stuck. I want to move all reports from one CE server to another but none of my reports connecting to a stored procedure work. Any ideas?