I have a Snapshot Replication Publication setup on Server 1. Server 2 subscribes to the publication on Server 1. Data is replicating successfully.
The Distributor Agent and Agent job for the Subscriber of Server 2 runs on Server 1 (where the Distributor database lives).
I created a stored procedure on Server 2 to drop (pre replication) and create (post replication) schemabound entities on Server 2's subscriber database. (I've done this successfully in the past because Snapshot Replication can stall if there's entities in the subscriber database that are schema bound to the replicated entities.)
I also created a script that executes this stored procedure from Server 1 via a linked server to Server 2. (I've verified the linked server is setup correctly and I can query data from and manually execute the stored procedure via the linked server from Server 1 to Server 2.) I've added this script to the Snapshot Publication's "Snapshot" Properties:
Initially I received an error in the Snapshot Agent job because my Snapshot Agent didn't have file share access to the path in the scripts, but I added that account and then the error went away and the Snapshot job finishes successfully now.
But the entities in the SQL script don't get dropped or created, and I don't see an errors anywhere. I've also tried moving the before and after script file to a folder share local to Server 1 and referencing it with a UNC path (as opposed to the network path in the screenshot) and still no change in behavior.
I've updated the TEST.sql script with a really simple test with the following code:
INSERT INTO DatabaseA.dbo.Table1 (Field1) -- DatabaseA is on Server1, so it should insert to the local table
SELECT 'Test' AS Field1
When I re-run the Snapshot Agent, it still runs successfully, but the table doesn't get inserted into (on Server 1).
Why could my pre- and post- scripts not be running but I'm not receiving any errors either?
(I also have another Publication for Transactional Replication on Server 1 that is syncing data just fine to Server 2 in the same database as the Snapshot Replication. But even when I set the pre- and post- scripts on the "Snapshot" Properties of the Transactional Publication on Server 1, I still get no errors and the SQL doesn't execute.)
Edit: I also just tried moving the script file to a local folder of Server 1, giving the Snapshot Agent read access to that folder and script file, and re-running the Snapshot Agent, and still nothing. No execution of the script, and no errors.
BUT interestingly, if I manually modify the Subscription Agent job itself and add a step to execute the same exact SQL query, it DOES work.
I also noticed the SQL Agent job runs under a different SQL account "sqlmin" then I expected for certain steps (new steps I created, or the first step of the job called "Snapshot Agent startup message" - I guess because these steps aren't of type "REPL-Distributor"). But the other pre-existing steps execute as the SQL Agent I designated, which is "DBReplication". The "sqlmin" account I know has very minimal SQL permissions on both Server 1 and Server 2, could this be why the pre- and post- replication scripts aren't executing (but I'm also not receiving an error anywhere) when I set the scripts on the pre- and post- fields of the Snapshot Publication - Snapshot properties?
Also additional details about the distributor account (who is also my Snapshot Agent account): The distributor is a Windows AD account that has a SQL LOGIN on both Server 1 and Server 2 and with the SQL role of db_owner in both the source and target replicated databases as well as db_owner on the distribution database (which lives on Server 1).