Skip to Content

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

CR not recognizing Time object from SQL Server

I am using CR 2008 version 12.3.6.1006 and connecting to SQL Server 2008 R2.  My data source is a stored procedure and a few of the fields returned are typed in SQL as time.  When checking the available data fields in CR, these time objects do not show up on the list.  Furthermore, the presence of the time values in the returned data causes problems with other data.  For example, here is a sample stored procedure:

CREATE PROCEDURE MyTest

AS

BEGIN

   SET NOCOUNT ON;

   SELECT

      CONVERT(varchar(100), 'My first string') AS TextString1,

      CONVERT(varchar(100), 'My second string') AS TextString2,

      CONVERT(time, '09:34:25') AS TimeValue1,

      CONVERT(time, '10:34:25') AS TimeValue2,

      CONVERT(varchar(100), 'My third string') AS TextString3,

      CONVERT(varchar(100), 'My fourth string') AS TextString4,

      CONVERT(varchar(100), 'My fifth string') AS TextString5,

      CONVERT(varchar(100), 'My sixth string') AS TextString6

END

If you connect a report to this procedure, the available data fields in the designer are TextString1 through TextString6 but not TimeValue1 or TimeValue2.  If you include all 6 text strings in the report, what prints is:

My first string

My second string

My third string

My fourth string

My fourth string

My fourth string

The number of fields that gets messed up is related to the number of time values in the returned data.

Is this something that has been addressed in CR somewhere?

Thanks,

David

replied

Hi David,

Thanks for trying... You are correct, after taking a closer look I do see there is only one time type. I found the cause:

1366333 - MS SQL 2008 TimeType ( Time(7) ) issues/solutions in Crystal Reports Designer

According to the Kbase, and I wrote it, my research back then discovered from our Developers the issue is Microsoft's OLE DB Provider returns an invalid field type in their OLE DB Provider. This also explains why CR can use ODBC. The ODBC driver does correct the field type. But it also doesn't explian why when I tested it back then it at least returned as string field type. Now it doesn't even show up in the designer.

MS' client for SQL 2008 is the OLE DB driver so they did fix something because back then ODBC did not work either. Or we simply ignore the unknown types which explains why I don't even see the field any more.

Other option is to ask MS to fix their OLE DB provider. I'll ping our Developers and see if we can track the issue with Microsoft also.

Don

0 View this answer in context

Helpful Answer

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