SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
clairedownes
clairedownes
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 142
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
Piotr.Rodak
Piotr.Rodak
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8582 Visits: 1761
Look also at default language for logins these users use.

Piotr

...and your only reply is slàinte mhath
clairedownes
clairedownes
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 142
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
Piotr.Rodak
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8582 Visits: 1761
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
clairedownes
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 142
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
Piotr.Rodak
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8582 Visits: 1761
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
clairedownes
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 142
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
clairedownes
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 142
That worked a treat!
Thank you so much for your help.

Claire
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search