Scheduled sql cannot access the network drives?

I have a stored procedure performing several tsql operations with MS
Access and Excel files stored into the network file system, and
therefore using OpenDataSouce with these files in the network,
accessible through mapped network drives.

My user has a profile that maps the necessary network drives at boot.
SQL Server runs on the server with an account created for that. This
accont has also a profile mapping the necessary network drives.

The stored procedure runs fine only if I launch it with SQL Server
Management Studio on the server, logging in directly on the server (at
this point the network drives are mapped correctly).

If I launch the Stored Procedure with SQL Server Management Studio on
my client, it doesn't find the network drives and fails.

The same happens when I schedule the stored procedure on the server
with SQL Server Agent, which runs with an account performing the
mapping in its profile.

Any ideas how to make it work?

A suggestion in common, don′t use mapped network driver, use UNC paths !

If you want to use ressources within the network you will need appropiate permissions either for the service account SQL Server Service is running with or the service account SQL Server Agent is running with (if you do that within a scheduled job). An alternative would be to setup a proxy account for the service [1]

[1] http://www.databasejournal.com/features/mssql/article.php/1580041

In SQL Server 2005 proxy accounts can also be configured through the Configuration interface which you will find in the program collection.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de