1

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?

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

| improve this answer | |
  • Thank You - This pointed me to the configuration for the linked server --- Msg 7411, Level 16, State 1, Line 29 Server 'repl_distributor' is not configured for RPC. – MillhouseD Jan 20 '17 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.