Why is this stored procedure for linked server job failing in SQL Server Agent?

Question

I have this stored procedure that runs this code:

This piece of code uses the MERGE Statement to update Local Tables form remote tables in an Access Database that is linked by LINKED Server in SQL Server.

However when I try to create an SSIS Package to run a schedule, the job fails.

Here is the error message.

Code: 0x00000000
Source: Execute SQL Task
Description: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "AccessDataSource" returned message "'N:\Database_Tools\AccessDB\delphi.accdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". End Error

Error: 2012-01-17 20:29:33.19
Code: 0xC002F210
Source: Execute SQL Task Execute SQL Task
Description: Executing the query "EXEC [dbo].[sp_UPDATE_RECORDS]" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "AccessDataSource".". Possible failure reasons: Problems with the query "ResultSet" property not set correctly parameters not set correctly or connection not established correctly. End Error

DTExec: The package execution returned DTSER_FAILURE (1).
Started: 8:29:32 PM Finished: 8:29:33 PM Elapsed: 0.297 seconds.
The package execution failed. The step failed.,00:00:01,0,0,,,,0

What confuses me is the stored procedure works when I run it locally without errors but fails in a JOB.

I than placed the stored procedure as job type as a Transact-SQL Script (T-SQL).

I select the database, then place this code in the command window:

EXEC [dbo].[sp_UPDATE_RECORDS];

This is the code for the stored procedure:

CREATE PROCEDURE [dbo].[sp_UPDATE_RECORDS]
AS 
BEGIN
MERGE dbo.CUSTOMER AS Target
USING (SELECT MEMBER_NBR,FIRST_NAME,LAST_NAME, ADDRESS1, ADDRESS2, 
CITY, [STATE], ZIP, dbo.FMT_PHONE_NBR(CELL_PHONE) AS CPHONE, 
dbo.FMT_PHONE_NBR(HOME_PHONE) AS HPHONE

FROM dbo.ACE_CUSTOMER) AS Source

ON (Target.MEMBER_NBR = Source.MEMBER_NBR)
WHEN MATCHED THEN
    UPDATE SET Target.FIRST_NAME = Source.FIRST_NAME,Target.LAST_NAME = Source.LAST_NAME,
    Target.ADDRESS1 = Source.ADDRESS1, Target.ADDRESS2 = Source.ADDRESS2,
    Target.CITY = Source.CITY, Target.ZIP = Source.ZIP, Target.CPHONE = Source.CPHONE,
    Target.HPHONE = Source.HPHONE

WHEN NOT MATCHED BY TARGET THEN
    INSERT (MEMBER_NBR,FIRST_NAME,LAST_NAME, ADDRESS1, ADDRESS2, CITY, [STATE], ZIP, CPHONE, HPHONE)
    VALUES (Source.MEMBER_NBR, Source.FIRST_NAME, Source.LAST_NAME, Source.ADDRESS1, Source.ADDRESS2,
    Source.CITY, Source.[STATE], Source.ZIP, Source.CPHONE, Source.HPHONE) 
OUTPUT $action AS 'STATUS',INSERTED.*;
END

I know this is a long issue but I am hoping someone can throw me a bone(idea)!

Thanks everyone!

EDIT

Just wanted to provide additional details. The Access Database is Located on a Network Drive. Also, I rand the Stored Procedure first using only SQL Server Agent, meaning I did not use BID and the Job Failed.

Now that permission and Networked Drive are mentioned, I think this may be the problem. However, I am still confused as to why I am able to manually execute the Stored Procedure from within SQL Server with the Access DB on a Network Drive?

Can you provide additional detail as to how I applied this suggestion?

If this is the case, try changing the drive letter N: to use the full network path instead: \\servername\Database_Tools\AccessDB\delphi.accdb and see if that works.

Finally, how can I apply this suggestion? Meaning where in SQL Server do I configure this feature:

change the permissions of the SQL Agent service account to allow access to the target location, or copy the access db to a location that the account can access (possibly the local disk) before running the package.

---

Just wanted to provide additional details. The Access Database is Located on a Network Drive. Also, I rand the Stored Procedure first using only SQL Server Agent, meaning I did not use BID and the Job Failed.

Now that permission and Networked Drive are mentioned, I think this may be the problem. However, I am still confused as to why I am able to manually execute the Stored Procedure from within SQL Server with the Access DB on a Network Drive?

Can you provide additional detail as to how I applied this suggestion?

If this is the case, try changing the drive letter N: to use the full network path instead: \\servername\Database_Tools\AccessDB\delphi.accdb and see if that works.

Finally, how can I apply this suggestion? Meaning where in SQL Server do I configure this feature:

change the permissions of the SQL Agent service account to allow access to the target location, or copy the access db to a location that the account can access (possibly the local disk) before running the package.

Thanks everyone for your time, effort and patience!

Guy

---

UPDATE:

The Stored Procedure Runs perfectly in SSMS but not in a Schedule Package or Job. Based on one of the suggestions about Network Drives. I removed the Access Database to my Local Job and the Schedule Job containing works!

So we know the problem is with the External located on the Network Drive.

So how can I configure the Network Location to work?

I followed the Instructions outline below but really not sure how to switch to domain account.

change the account under which the SQL Agent service is running in the SQL configuration manager (should be found under Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools. Find "SQL Server Agent" in the list of SQL Server Services and see the "Log On As" column). You may need to switch this to a domain account to get access to the share.

I am lost: You may need to switch this to a domain account to get access to the share.

Or this solution, what username?

If this is the case, try changing the drive letter N: to use the full network path instead: \\servername\Database_Tools\AccessDB\delphi.accdb and see if that works.

Thanks everyone - almost there.

Guy

Answer 1

Although you don't mention it in the question, I assume that the reason you're using an SSIS package rather than simply executing the stored proc from a SQL Agent job is that there are other steps in the process. My other assumption is that you schedule the SSIS package using a SQL Agent job.

The first error message implies that you are creating an OLEDB connection to your Access database but that the database could not be found. It's possible that this is a security problem, and that the Windows account under which the SSIS package is running does not have permissions to the file system location where the Access database is stored.
The second error message seems to back this up - when executing the SP, the linked server connection could not be opened.

To confirm this as the issue, you need to try running the SP in SSMS using the same account under which the SQL Agent service runs.

If this is the problem, you will need either to change the permissions of the SQL Agent service account to allow access to the target location, or copy the access db to a location that the account can access (possibly the local disk) before running the package.

EDIT - in response to additional details:

Now that permission and Networked Drive are mentioned, I think this may be the problem. However, I am still confused as to why I am able to manually execute the Stored Procedure from within SQL Server with the Access DB on a Network Drive?

The Acess linked server is configured to use the security credentials of the account executing the query. When you execute the procedure from SQL Server Management Studio, you are doing so in the context of your Windows account, which must have permissions to the network share.
When the same query is executed by the SQL Agent service, it uses the security context of the service account under which the service is running, which does not have access to the share.

Finally, how can I apply this suggestion? Meaning where in SQL Server do I configure this feature:

change the permissions of the SQL Agent service account to allow access to the target location

You can find out and change the account under which the SQL Agent service is running in the SQL configuration manager (should be found under Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools. Find "SQL Server Agent" in the list of SQL Server Services and see the "Log On As" column). You may need to switch this to a domain account to get access to the share.

Answer 2

Just on the off chance, is drive N:\, where the Access database is located, a network location? I ran into a similar issue a year or so ago where someone had set up a proc to reference a drive letter location that they had mapped using their own login on the server. It ran fine when they were logged in and running it themselves, but it wouldn't work when running under SQL Agent. Drive letter mappings for network locations are user specific and are not generally tied to the server's other logins (such as that used by SQL Agent or an SSIS package).

If this is the case, try changing the drive letter N: to use the full network path instead: \\servername\Database_Tools\AccessDB\delphi.accdb and see if that works.