0

We are trying to test the success of Transactional replications. by Googling , i found that i need to execute in our publisher this stored procedure :

sp_publication_validation

However , When i execute :

exec sys.sp_publication_validation 
@publication=ReplicationFaouzi,
@rowcount_only=0,
@full_or_fast=0,
@shutdown_agent=0

I get this error :

Msg 20026, Level 16, State 1, Procedure sp_MSpublication_validation, Line 57 The publication 'ReplicationFaouzi' doesn't exist ?

When i run this sql :

SELECT publication
FROM MSpublications
ORDER BY publication 

I get in Result :

ReplicationFaouzi

what is wrong ?

Update

By checking the code of this stored procedure i found that it calls un-found stored procedure sys.sp_MSreplcheck_publish and sys.sp_MSrepl_getpublisherinfo :

exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
    return (1)
end

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo  @publisher      = @publisher,
                                                @publisher_type = @publisher_type OUTPUT,
                                                @rpcheader      = @cmd OUTPUT

sys.sp_MSreplcheck_publish exist under a database named model ,and i can't find sys.sp_MSrepl_getpublisherinfo!!

Update 2

My Team re-created the replication database and now this sql command:

 SELECT [name] FROM sys.databases WHERE is_published = 1;

dosen't return any value , but this :

 SELECT [name] FROM sys.databases WHERE is_merge_published = 1;

returns 'Guichet'

Now, when i run :

USE [Guichet];
GO
EXEC sp_helppublication;

I get nothing !

2

You need to run that procedure in the context of the publication database (the database that is the source of the data you wish to replicate). You can do that with a USE statement:

USE [YourDatabaseName];
GO

EXEC sys.sp_publication_validation 
    @publication=ReplicationFaouzi,
    @rowcount_only=0,
    @full_or_fast=0,
    @shutdown_agent=0

If you're unsure of which database is the publication database, you can run this query to find which databases are configured as publishers:

SELECT [name] FROM sys.databases WHERE is_published = 1;

Once you have the database name from that query, you can run this to see the name of the actual publications that should be passed to sp_publication_validation. In your case, the publisher is named Guichet, so you'd run:

USE [Guichet];
GO
EXEC sp_helppublication;

This will give you the value that needs to be passed to the @publication parameter.


Note that all of the above is written in the context of transaction replication (which is what the original question was about). Different steps are required for different types of replication.

There is a great "information gathering" script on the Microsoft Docs website that walks through getting info on distributors and publishers when using different types of replication:

Distributor and Publisher Information Script

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.