Rights and Datetime conversion.

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

Jonathan

I have a report in an Access 2003 SP2 Data Project on a Windows XP SP2 machine.
The ADP connects to a SQL Server 2000 database.

The report has the following InputParameters :-

@StartDate datetime=Forms!frmDateRangeSelector!dtpStartDate, @EndDate
datetime=Forms!frmDateRangeSelector!dtpEndDate

When I run the report as a sysadmin of the SQL Server, the report is fine.
If I run the report as a stanadard user where they only have rights to Select
and Execute the SP I get the following message in Access:-

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Any ideas what SQL rights or setting would stop the conversion?

Thanks
Jonathan.
 
Possibly because the default language associated with the sql login for the
standard user is not the same. Correct this and you should be OK.
Otherwise, transmit the datetime as a varchar and do the conversion inside
the SP.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
This was close, but both accounts had the same language.

I asked a friend and he spotted the issue. I was converting a field of dates
and times to just dates and used CAST to convert from varchar to datetime in
the view powering the SP. When I used CONVERT(datetime, fieldname, 101) that
resolved the issue. Not entirely certain why this would make a difference, as
the standard user was able to run the SP outside of the Access form without a
problem.

Thanks for looking.
Regards
Jonathan.
 
Well, this boil down to how to convert something like '1/2/2009' to a date:
is it january 2th, 2009 or february 1st, 2009? The format of the date to be
used can be specified in many ways and at different location, so it's
sometime difficult to know where the error/difference is.

This is why I suggested to you to do the conversion in the SP where you can
be sure to not have this problem in any way. Of course, this also means
that you'll have to use a language independant conversion such as
CONVERT(datetime, fieldname, 101) instead of a CAST.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Back
Top