Useful transactional replication stored procedure

The scenario is when your publication settings has the option
immediate_sync configured as false (means if you run the Snapshot Agent, the snapshot files will be created for the new articles only and also a new subscriber to the existing publication, the snapshot files only get created for the new subscription)

This option immediate_sync will prevent to generate all snapshot files again when add a new article in the replication, but it still need schema lock to check all articles modification.

If you have a large number of articles and a bunch of subscribers, you might face the situation you have to add a new article in your transactional replication and after run the snapshot the article is not propagating to the subscribers.

First, check your publication settings.

exec sp_helppublication 'PublicationName'
GO

If you are using the settings immediate_sync and allow_anonymous false you can run the procedure below to solve the issue that your new article didn’t propagate to your subscribers.

exec sp_refreshsubscriptions 'PublicationName'
GO



Structured Query Language

By a “set” we mean any collection M into a whole of definite, distinct objects m (which are called the “elements” of M) of our perception or of our thought.

—George Cantor, in “Georg Cantorby Joseph W. Dauben (Princeton University Press, 1990)

Standard SQL is based on the relational model, which is a mathematical model for data management and manipulation. The relational model was initially created and proposed by Edgar F. Codd in 1969. Since then, it has been explained and developed by Chris Date, Hugh Darwen, and others.

Transact-SQL (T-SQL) is the main language used to manage and manipulate data in Microsoft SQL Server.

Microsoft SQL Server Management Studio showing...
Microsoft SQL Server Management Studio showing a query, the results of the query, and the Object Explorer pane while connected to a SQL Server database engine instance. (Photo credit: Wikipedia)

In fact, T-SQL is based more on multiset theory than on set theory. A multiset (also known as a bag or a superset) in many respects is

similar to a set, but can have duplicates. The T-SQL language does give you enough tools so that if you want to follow relational theory, you can do so. For example, the language provides you with a DISTINCT clause to remove duplic

ates.

It then moves on to describe one of the most important concepts you need to know about the language—logical query processing.