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.)orCould not bulk insert because file %s could not be opened. Operating system error code 5(Access is denied.)
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.
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 Properties2) 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 Properties2) 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.
1) Set up a Replication on two different servers. 2) Use the snapshot location as Local path2) Use Windows authentication for Subscriber Connection4) 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.
Support Engineer, Microsoft SQL Server.
Reviewed By, Anurag Sharma
Escalation engineer, Microsoft SQL Server