How to use -UseInProcLoader in Distribution agent and get it working

  • Article

When we use the option of -UseInProcLoader in Distribution agent profile, the distribution agent might fail with the following messages

Cannot bulk load because the file %s could not be opened. Operating system error code 3(The system cannot find the path specified.) or
Could not bulk insert because file %s could not be opened. Operating system error code 5(Access is denied.)

Cause

The first message occurs if the the subsciber is not the machine where the Snapshot files are located. So whatever is the absolute location of the Snapshot folder, the Subscriber Spid applying the BCP scripts, would look for that location in the Subscriber server and would fail.

The Second message occurs due to a design specification and is part of it is documented in SQL Books online.

Resolution

Message:
Cannot bulk load because the file %s could not be opened. Operating system error code 3(The system cannot find the path specified.)
Workaround:
To get rid of the first message we need to change the snapshot location to point to an UNC path accessible by Subscriber as well.

Steps:
1) Right Click on Publication and click on Properties
2) Click on the Option "Snapshot"
3) Most likely the snapshot location is specified as the default Snapshot location (Absolute location)
4) Change that / Use Alternate location and provide an UNC path which is accessible from Subscriber.

Note: The SQL Server service account at subscriber should have read permission on the share at all times.

Message:
Could not bulk insert because file %s could not be opened. Operating system error code 5(Access is denied.)

Workaround:
Use SQL Authentication instead of Windows Authentication for Subscriber Connection.

Steps: 1) Right click on the Subscription and click on Properties
2) Under General and Property "Security", if we have Windows Authentication, we have to change it to use SQL authentication and provide a SQL account (present on Subscriber) that has the privileges to write to the subscriber database.

Steps to reproduce.

1) Set up a Replication on two different servers.
2) Use the snapshot location as Local path
2) Use Windows authentication for Subscriber Connection
4) Run the Distribution agent and it would fail with the first message mentioned above

For Step 2, use UNC path and it would fail with Second message mentioned above.

Regards,

Gourav Das

Support Engineer, Microsoft SQL Server.

Reviewed By,
Anurag Sharma

Escalation engineer, Microsoft SQL Server