Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  DATE BETWEEN Exception

Author  Topic  PianoMan Starting Member 1 Post Posted - 2010-02-23 : 11:17:28 SQL 2008 10.0.2531 Access 2007 I wasn't sure whether to post this under SQL topic or Access. When I run a report in Access 2007 which executes a stored procedure Access generates an exception. The cause appears to be DATE BETWEEN elements in a WHERE clause in a table-valued function. The general sequence is: - Access Report -- Stored Procedure --- Table-valued Function ---- The table-valued function does a "INSERT INTO @TableOut SELECT FROM PaidDate BETWEEN StartDate and End Date (simplified)." When I login to an Administrators account on my client's Window server and run the Access report the report stored procedure runs successfully, returns a record set of about 265 records, and the report displays. When I login into a general user account on the same server and run the Access report then Access generates an exception. When I login into a general user account on the same server and run the report stored procedure from SQL Studio the SP completes successfully and returns about 265 records as expected. I used the same input parameters in all cases above. I've narrowed the problem to the DATE BETWEEN elements in a WHERE clause in a SELECT in a table-valued function: Here's the complete WHERE clause: WHERE P.PlanId IN (SELECT PlanId FROM dbo.PlansPlanIdsSelect(@Debug, @DivisionName, @FiscalYear)) AND C.MemberId = @MemberId AND C.PaidDate BETWEEN P.FiscalYearStart AND P.ServiceEndDateMax --AND '20081201' BETWEEN P.FiscalYearStart AND P.ServiceEndDateMax AND C.ServiceBeginDate BETWEEN P.FiscalYearStart AND P.FiscalYearEnd --AND '20081201' BETWEEN P.FiscalYearStart AND P.FiscalYearEnd AND P.Include = 1 AND C.PaidAmt IS NOT NULL If I comment out one or both DATE BETWEEN elements the SELECT runs successfully. If I add one or both of the DATE BETWEEN statements back into the SELECT the exception occurs. If I hard code the date instead of using the table column date the SELECT runs successfully. In summary: If I run the Access report from a general user account and the SELECT WHERE clause uses DATE BETWEEN elements an exception occurs in Access. The system wide exception handling component (Dr. Watson?) pops up and wants to email MS a error report. Any thoughts? My next step is to call MS technical support. Thanks, Ed