Print Page | Close Window

Crystal Generates Incorrect SQL Statements

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7069
Printed Date: 04 May 2024 at 11:33am


Topic: Crystal Generates Incorrect SQL Statements
Posted By: SDB2
Subject: Crystal Generates Incorrect SQL Statements
Date Posted: 16 Jul 2009 at 1:11pm
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?
 


Replies:
Posted By: SDB2
Date Posted: 17 Jul 2009 at 8:48am
Update: I did some more testing and this same problem occurs only with date time paramters.

Posted By: SDB2
Date Posted: 17 Jul 2009 at 9:28am
Problem solved: Switched from SQL Native Client to SQL Client driver. SQL now runs beautifully. Hope this helps someone else down the road.

Posted By: ayman
Date Posted: 11 Jul 2015 at 9:39pm
sorry, but what do you mean by "SQL Client driver"


Print Page | Close Window