RE: Linked Server to Access 2007 file that is linked to SharePoint



I have some additional information about this problem.
I took SQL 2005 Standard Edition which has Reporting Services with
possibility to use ODBC or Access file as data source (Reporting Services in
Express and Workgroup Editions can use only local SQL DB).
I created new data source to Access file linked to SharePoint and report in
SQL Server Business Intelligence Development Studio (Visual Studio 2005). I
ran the report in Visual Studio Preview and everything worked perfectly -
data were returned! Then I deployed data source and report to Reporting
Services web site and again - the same problem as with linked servers -
report is executing and nothing happens... Again only restart of SQL server
helps. So it seems that the problem is caused exactly by SQL - because in
Visual Studio report works.

Thinking that this might be problem that Access locks its file, I checked
whether file is locked (file with extension laccdb created) only when running
report from Reporting Services web site (where report does not work) or is it
locked also if running report from Visual Studio (where report works). File
is locked in both cases. So I don't know whether it is a lock problem. If so
then exactly SQL cannot deal with the lock, Visual Studio can.

I also tried running query using WITH(NOLOCK) and had a look at Access
locking files, but so far no luck..

Another thing - I tried setting Connection Timeout and Query Timeout for
linked server to 5 seconds. This changes nothing - query is still executing
all the time and is not stopped.

And one more thing. If I try to select data using linked server from table
that does not exist (SELECT * FROM ACCESS...ThisTableDoesNotExist), I get
error that such table does not exist or I don't have permissions to access
it. So my query gets at least to Access file and can see the tables.
.