DATE BETWEEN causes exception

[EdSwartz]

SQL 2008 10.0.2531
Access 2007

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

 

[SQLBill]

Check to see what login the application (Access) is passing to SQL Server when the 'general login' is being used. Some applications take a user login, but then connects to SQL Server with an application login. Does that login have execute rights on the function?

If you don't know, try setting up a trace on SQL Server to capture logins and then login to Access and see what login is being used.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[NiceArms]

Try using:
WHERE DateField >= @Date1
and DateField <= @Date2

It could be that simple.

The BETWEEN function can be "fun". I suggest reading the following as it helped me:

http://www.sql-server-performance.com/articles/dev/datetime_datatype_p2.aspx

/Nice

 

[EdSwartz]

NiceArms,

Per your suggestion I converted the DATE BETWEEN style to using the >= <= operators. I still get the exception.

Ed

 

[EdSwartz]

SQLBill,

In Access I displayed the Data Link Properties.

I selected the second login option and provided a SQL username and password.

Also, as a test I selected Windows NT Integrated security login type.

I logged in as the general user and then attempted to run the report. Here's the errors I get:

The record source 'PlansDivisionNameSelect' specified on this form or report does not exist.

The name of the recordsource may be misspelled, the recordsource was deleted or renamed, or the recordsource exists in a different database.


So I reverted to the SQL login.

Not sure if that answered your question.

Ed

 

[SQLBill]

Yes it did. What is the record source "PlansDivisionNameSelect"? Is that a column in the database? Is it an external file? It appears that the general login does not have access to that source.

If it a column in the database, make sure that the login does have access to it. If it is an external file, is it a share that the login doesn't have permission to access?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875