Sql-server – Unable to Generate Scripts for Publications

replicationsql servertransactional-replication

Version is SQL 2008 R2 Enterprise

I am attempting to document our replication configuration.

Replication is running and working fine:

In SSMS I right click on a publication and select Generate Scripts.

I receive the error

SQL Server cannot generate the SQL script.

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

Could not find stored procedure ''. (Microsoft SQL Server. Error: 2812)

The T-SQL statement EXEC sp_helpsubscription 'SubscriptionName'

returns the following:

Msg 2812, Level 16, State 62, Procedure sp_MSrepl_helpsubscription, Line 537

[Batch Start Line 268]

Could not find stored procedure ''.

Any ideas on what I could check?

Best Answer

DECLARE @distributor sysname

DECLARE @distributiondb sysname
DECLARE @distproc NVARCHAR (255)
DECLARE @publisher sysname

SELECT @publisher = @@SERVERNAME

EXEC sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distributiondb OUTPUT

select @distributor, @distributiondb

SELECT @distproc = QUOTENAME (@distributor) + '. ' + QUOTENAME (@distributiondb) + '.dbo.sp_MSenumdistributionagentproperties'

select @distproc ---what do you get here?

---Line 537
EXEC @distproc @publisher = @publisher,
@publisher_db = @dbname,
@publication = @orig_publication,
@show_security = 1

The exec @distproc section is roughly at Line 537 of sp_MSrepl_helpsubscription. It's building the proc call and should be something like [SeverName].distribution.dbo.sp_MSenumdistributionagentproperties

From the error, it appears that @distproc variable is not getting set properly, or at least getting set to an empty string.

Why? That's hard to know without more information or being able to test on your system. Some unusual setup with the distributor, perhaps? But this will hopefully point you in the right direction so you can walk through it. (Run that code on the Database that is being published).

Related Question