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.

Relevant Pages

  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
  • Re: SCAN.EXE - McAfee AntiVirus Software
    ... > | of network computers and have only report summaries sent to be via ... > | Virus Scan Report File ... > | application RemoteProcessLaunch. ...
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
  • Re: Problems adding "All" to combo box
    ... SQL for each combo box is in a previous post). ... You have a report defined that is named AI Thru Hole Work Log_Operator ... of the report or form you want to filter ...
  • Re: error: The operation has timed-out (executionTimeout?)
    ... Actually, there was no SQL timeout issue in this case, and when I said ... fixing the performance of this particular report. ... I had to set executionTimeout in both my web site and the web ... web service timing out, then my web site timing out it's own request to the ...