CR not recognizing Time object from SQL Server

[Former Member]

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

[former_member11696 · Accepted solution]

Hello,

MS SQL Server has limited supported with MDAC OLE DB and ODBC drivers. Search MSDN for more info.

Install the MS SQL Native 10 client and update your reports to use the same and then it should work.

Don

[Former Member · Accepted solution]

Hi Don,

Thanks for taking the time to look at this.

There always seems to be some information that I leave out of the initial question.  In this case it was the provider.  I am using SQLNCLI10 with a database type of OLE DB (ADO) while experiencing the problem.

Anything else you can think of that might be causing the problem?

David

[abhilash_kumar · Accepted solution]

Hi David,

Could you check if ODBC works.

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Yes, it works correctly with ODBC.

Thanks.

David

[abhilash_kumar · Accepted solution]

Hi David,

Well, the reason I asked you to check if it works with ODBC is because I had seen a similar issue with datetime fields + SQL Server 2008 R2 database + OLEDB.

And, of course, ODBC worked fine and I have no clue why!

I do not have a solution at this point and maybe you'll have to go ahead with ODBC.

Could you see if this is an issue only with Stored Procedures though. So what you can do is write a simple query in the 'Add Command' via OLEDB:

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

Could you test if this works?

-Abhilash

[Former Member · Accepted solution]

Abhilash,

I did what you asked and it does not work properly using the command via OLEDB.  I tried with ODBC as well and that does work properly.

Regards,

David

[former_member11696 · Accepted solution]

Hi David,

This rings a bell now. CR has never supported "Time" only field types in SQL Server. It alway expects it to be in a date-time format.

When it works in ODBC is it shown as a time field or is it seens as a String type in CR? Right click and Browse...

Also, in your database is the original source a data-time field?

Add your request to Idea Place and in the mean time all you can do is either use ODBC or leave the field as data-time in the SP. then you can use the CR functions to get just the time part from the field.

Also, try the first 2 options in the Report Designer Report Options to Convert Nulls to default, not sure it this will affect it but worth a try.

Don

[Former Member · Accepted solution]

Hi Don,

The original source for the data is a time only field in SQL Server.  When using ODBC, they show as time fields in CR.  So in ODBC they work as one would expect.

I have worked around the problem by having my Stored Procedure convert these values to DateTime.

Thanks for the help.

David

[former_member11696 · Accepted solution]

Hi David,

So if it works in ODBC then OLE DB should also so I went and looked in our bug Tracking system and I don't see anyone ever logging any issues against this type. Although there are a few bugs marked as not our issue that got me doing more testing.

I did go into SQL 2008 R2 and added a time field but I also see there are multiple time types you could add.

In your DB what type is that fields defined as?

Also, what happens if you use a tool from Microsoft called OLEDBTest, or any OLE DB test tool and run/call that SP?

I'm currious if this is a CR bug or if it's a bug in MS's OLE DB provider....

Thanks again

Don

[Former Member · Accepted solution]

Hi Don,

The columns in the database are just defined as time(7).  I don't see any other time possibilities, just different datetime types.

I am not much of a database person, just enough to get by.  I downloaded the rowsetviewer from Microsoft (couldn't find OLEDBTest) in the MDAC SDK but it contains things that I don't understand for making the connection.  So I am unable to test this right now.

If you have a simple way you can tell me for how to test this I will be happy to do so.  Otherwise, it will be a while before I can commit the time to learning what I need to know in order to do the test.

David

[former_member11696 · Accepted solution]

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

[Former Member · Accepted solution]

Well, at least we know the answer now.

Thank you for looking into this.

David