Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Server Native Client 10 - DateTime Parameters Fail on Stored Procedures

We are having a problem with Crystal Reports 2008 SP2 when attempting to bind a report to stored procedure using the u201CSQL Server Native Client 10.0u201D provider. Any time we use a stored procedure that takes a date parameter the following error is produced:

Database Connector Error: 'ADO Error Code: 0x80040e14

Source: Microsoft SQL Server Native Client 10.0

Description: Incorrect syntax near the keyword 'CONVERT'.

SQL State: 42000

Native Error: 156 [Database Vendor Code: 156 ]'

Here is a simple way to reproduce the problem using SQL Server 2008 and Crystal Reports 2008. In your database create this stored procedure:

CREATE PROCEDURE TestProcedure

@param1 as DateTime

AS

BEGIN

SELECT 0 as 'Test'

END

GO

Now open Crystal Reports 2008 and create a new report. In the u201CStandard Report Creation Wizardu201D select u201CCreate New Connectionu201D u2192 u201COLE DB (ADO)u201D u2192 u201CSQL Server Native Client 10.0u201D, then click Next. Enter the database credentials for your server. IMPORTANT: If you have the 2005 native client installed (listed as "SQL Server Native Client"), then the 2008 version will NOT be used even if you select it.

Expand your connection to find the stored procedure named TestProcedure and add it to the "Selected Tables" list. When prompted parameter values, uncheck "Set to Null" and then click the calendar icon. Select any date. Click OK. You will now get the error mentioned above. Profiling SQL Server shows that this stored procedure call is generated when you attempt to bind:

exec "dbname"."dbo"."TestProcedure";1 CONVERT(DATETIME, '2009-09-01 16:33:55', 120)

This call is not valid and will not execute in SQL Server 2008. To make it work you have to either remove the CONVERT call (passing just the date string), or declare a variable, store the CONVERT result in it, and then pass that variable to the sproc call.

If I try the same process with the 2005 Native Client I do not get this bug. This is the SQL that is generated (which is valid):

exec "dbname"."dbo"."TestProcedure";1 {ts '2009-09-10 16:40:53'}

Former Member
Not what you were looking for? View more on this topic or Ask a question