I am getting an error where DTS complains that the workbook it is importing
data from is already opened exclusively.

Step Error Source: Microsoft JET Database Engine
Step Error Description: The Microsoft Jet database engine cannot open the
file ''. It is already opened exclusively by another user, or you need
permission to view its data.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003051

I have a process where I need to do some number crunching for user input
data (matrix of 100 x 20000 values) utilising data already in the SQL DB. The
size of the DB data means bringing it back to excel for processing will seize
up any of our workstations.

So I put together a DTS package that loads the data in from the open Excel
workbook, does the processing in SQL Server and places the results in a SQL
table. The user then clicks a button in the workbook to retrieve the results
when they are ready.

However when the DTS package is run from the workbook, DTS complains that
the workbook is already opened exclusively. This was surprising as when the
steps are individually run in the DTS UI or the package run as a whole, there
is no access problem when the Excel file is already open.

The package is run in a stored procedure using "EXEC master..xp_cmdshell
@DTSRUNCmd" where @DTSRUNCmd holds the DTSRUN command string : DTSRun /S
"SERVERname" /U "username" /P "password" /N "pacakgename" /G "{guid}" /W "0"
..The stored procedure is called from excel by executing a query which just
runs the stored procedure.

I assume this is to do with permissions on the xp_cmdshell but I am at a
loss as to how to get around this. I already have the share and the excel
file on "everyone -full control", so there should be no permission issue.

Also, the package does fail if the run dts stored procedure is run manually
from within query analyser. I’m running query analyser as ‘sa’ user

I have also tried this by running the package via SQL OLE stored procedure
as per http://www.databasejournal.com/featu...le.php/1459181 but
I get the same excel access error.

Any help or advice gratefully received.

Jinx1966