Skip to Content
avatar image
Former Member

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'}

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Sep 16, 2009 at 10:00 PM

    Hi Michael,

    This is a known issue with that driver. It's a MS OLE DB driver issue and not a CR issue. Please report it to Microsoft.

    The OLE DB provider is telling CR to use the CONVERT function.

    Options are to use a Command Object.

    Thank you

    Don

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mike,

      It does appear this is something we can fix. I just found a current escalation for this issue - Problem_Report: ADAPT01285561

      It's because our crdb_ado.dll does not recognize the new native driver version so we are defaulting to that function syntax.

      It's scheduled to be release with Fp 2.2 which should be out in a month or two, this is the last week to get anything into that patch then QA will start the full regression test with the fix.

      In the mean time your only option will be to use ODBC, or possibly the MDAC version of the driver. If it's not specifically listed in the Platforms.pdf file then we have not tested it's use so we can't say for sure if any issues will show up. Once you apply the Fix Pack then you'll be able to use the updated version.

      Thnaks again

      Don

  • avatar image
    Former Member
    Sep 21, 2009 at 04:02 PM

    Will this fix be made to other versions than Crystal Reports 2008? Specifically, will there be a patch for Crystal XI?

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mike,

      CR XI is past it's end of life cycle so no updates to it and becasue SQL Server 2008 was released after the last patch update it was not added to the list.

      If you go to our download site you can get a free update to CR XI R2, your XI R1 keycode will work with R2, and then SP5. I just checked and it's not fixed in there so I'll create a branch roll this back to XI R2 SP6 which is due out the end of first quarter of next year I believe.

      I Just checked and I may be able to get this in early, possibly FP 5.8 which is due out mid January, 2010.

      Thank you

      Don

      Edited by: Don Williams on Sep 21, 2009 9:41 AM

  • Oct 15, 2009 at 07:29 PM

    Latest update. FP2 for SP2 of CR 2008 and SP 3.

    Will also be released for CR XI R2 SP 6

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Daniel,

      No sorry, CR for VS is version 10. No plans to patch those versions. Only option is to NOT use the Native 10 client and use OLE DB or ODBC.

      Or upgrade to CR 2008 full version. Next option is to try Visual Studio 2010 and CR for VS 2010 when it's released in November this year. It should have the fix in it.

      Thank you

      Don