Failed to open a rowset - occurs for some users and not others when running the query via a report in Crystal

Question

Post reply

Failed to open a rowset - occurs for some users and not others when running the query via a report in Crystal

clairedownes

Mr or Mrs. 500

Points: 563
More actions
April 18, 2008 at 6:30 am

#206048

Dear All,
I have a simple query with a date that is converted (because I don't want hh:mm:ss).
The query runs prefectly, for me, in SS Management Studio and also from Crystal and Crystal Report Viewier.
However, some users can run the report without any problems and others receive the following error message:
"Crystal Report Viewer
Failed to open a rowset.
Details: ADO Error Code: 0x80040e07
Source: Microsoft OLE DB Provider for SQL Server
Description: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SQL State: 22007
Native Error: 242
OK"
I have used the ISDATE() function to ensure all dates returned are valid and no problems there.
If I remove the convert from the query it is fine for everybody to use.
I have checked the Language/Country setting and other settings on the users machines who get the error thrown and have checked d/b permissions but cannot find anything wrong/different to mine.
I have also spent ages on the Internet trying to find a solution, but no joy.
Please help me!
Best Regards,
Claire

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

Look also at default language for logins these users use.

Piotr

...and your only reply is slàinte mhath

clairedownes Mr or Mrs. 500 Points: 563 More actions · Answer 2

Thanks for the speedy reply.

I forgot to mention in my post that I have looked at that too.

Any other ideas?

Thanks

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 3

What is the format of date returned from database if you use your account? How do you convert it to requested format?

Piotr

...and your only reply is slàinte mhath

clairedownes Mr or Mrs. 500 Points: 563 More actions · Answer 4

What is the format of date returned from database if you use your account?

yyyy-mm-dd hh:mm:ss:ms

e.g. 2008-03-04 10:44:54.000

How do you convert it to requested format?

cast(convert(varchar, createddate, 103) as datetime)

returns: 2008-03-04 00:00:00.000

Have also tried: dateadd(dd, 0, datediff(dd, 0, createddate)) which returns same date as cast/convert.

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 5

Try to use this:

convert(datetime, convert(varchar, createddate, 103), 103)

The problem is in the fact that your conversion is using default connection language for outer conversion and if this language happens to have months and days switched, you can get datetime overflows.

HTH

Piotr

...and your only reply is slàinte mhath

clairedownes Mr or Mrs. 500 Points: 563 More actions · Answer 6

Once again, thanks very much for your help.

Have just picked up your suggestion - shall give it a try on Monday and let you know if it has done the trick.

Best Regards,

Claire

clairedownes Mr or Mrs. 500 Points: 563 More actions · Answer 7

That worked a treat!

Thank you so much for your help.

Claire