cancel
Showing results for 
Search instead for 
Did you mean: 

Server Native Client 10 - DateTime Parameters Fail on Stored Procedures

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

0 Kudos

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

Will also be released for CR XI R2 SP 6

Former Member
0 Kudos

I also am having problems with this datetime thing. I have vs08 pro SP1 with CR Basic on a win 7 64 bit machine. I have fought this for hours and cannot seem to get around the sqlncli10 driver. how do i get the cr sp2 or sp3 you speak of? Is there a patch for this bug?

0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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

0 Kudos

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

0 Kudos

I should also mention that if you use ODBC it works also. Just an issue with that OLE DB provider.

Don

Former Member
0 Kudos

Hi Don,

Thanks for the information.

Using a command object isn't ideal since we are upgrading existing reports that we bind to at runtime. We would have to go back and update our code to treat all of the DateTime parameters as strings and some of the reports rely on those parameters being DateTime objects. (Using a Command with a DateTime parameter has the same problem as a stored procedure)

ODBC is also not an option because of setup issues.

Do you know if there is any problems or drawbacks to using the SQL Server 2005 Native Client between Crystal 2008 and SQL Server 2008?

0 Kudos

Hi Michael,

Your tests definitely confirms it is a MS OLE DB driver issue. CR simply passes the SQL directly to the client so the client is convert them to a string value.

I recall that MS SQL Server 2008 does not support the MDAC version of their OLE DB Provider. You could try it but it may fail once MS fixes their driver. It may allow you to ship but expect an update once MS pushes the fix out to all PC's. Likely be a big call generator for your support team. You would have to update your code so it uses the Native 10 driver once they fix it, possibly use an INI or XML file to specify the driver to use whcih then it would be a simple fix to push out to your customers rather than ahving to make the change in code and sending out an updated runtime.

Without updating/changing your report sources all I can suggest is you log this issue with MS and hopefully they'll fix the driver. You'll also find the same problem with their new Time(7) field type, it also returns the type as string to CR.

We follow the ANSII92 standards so we will not put hack code in our driver to make this work. It would mean we would have to un-do the cahnges once MS fixes their driver issues.

Thanks again

Don

Former Member
0 Kudos

I am guessing the MDAC version you refer to is the driver listed as "Microsoft OLD DB Provider for SQL Server" (SQLOLEDB). This however, does not answer the question of the 2005 Native Client (SQLNCLI). It has not produced any problems in our testing, but it is a supported combination?

thanks,

Mike

0 Kudos

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