Pre- and Post- Snapshot Replication Scripts Not Executing and No Errors

Question

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).

Answer 1

reposting my comment as an answer

extract below from BoL "You can specify scripts to execute AT THE SUBSCRIBER (my emphasis, aka Server2) before or after the snapshot is applied. Scripts can be used for a variety of reasons, such as creating logins and schemas (object owners) at each Subscriber."

https://learn.microsoft.com/en-us/sql/relational-databases/replication/snapshot-options?view=sql-server-ver15

Answer 2

Just a quick summary of some of the things I would try, when analysing the various options....

---

Have you tried passing the four part object identifier in the scripts?

Instead of using:

... Database.Schema.Table ...

Try using:

... Server.Database.Schema.Table ...

Or in your case where you are using the Linked Server it would be:

... LinkedServerName.Database.Schema.Table ...

Where you define the values as:

LinkedServerName : The name of the linked server as you defined in the Linked Server connection.
Database ........: The name of the database
Schema ..........: The name of the schema
Table ...........: The name of the table

It depends on where the script has to modify data. That's not quite clear in your description, but seeing as you did mention Linked Servers I thought I'd mention it.

---

The script is executed on the Subscriber as is explained in:

You can specify scripts to execute at the Subscriber before or after the snapshot is applied. Scripts can be used for a variety of reasons, such as creating logins and schemas (object owners) at each Subscriber.

And the additional information provided here is pretty important:

The script is run by launching the sqlcmd utility. Before deploying a script, run it with sqlcmd to ensure it executes as expected. The contents of scripts that are executed before and after the snapshot is applied must be repeatable. For example, if you create a table in the script, you should first check for its existence and take appropriate action if it exists. The script must be repeatable because if you need to reinitialize a subscription for which the script has already been applied, the script will be applied again when the new snapshot is applied during reinitialization.

^{Reference: Modify Snapshot Initialization Options for SQL Replication (Microsoft | SQL Docs)}

If the script doesn't work together with the account that is used for the subscription agent and in conjunction with sqlcmd, then it isn't going to work in the subscription.

Hint
Use ; at the end of every line in the scripts.
Add GO in between lines.

---

Then you might want to have a look at the article Troubleshooter: Find errors with SQL Server transactional replication. I'm not saying you havent't done that yet, but you didn't say you had. Who knows, maybe you can find the issue in the replication agent's job history...