Publishing databases in SQLserver

Question

Publishing databases in SQLserver

Avyayah 1,291

Sql server requires that all objects referenced by published stored procedures such as tables and user defined functions be available as the subscriber. If the refence objects are not published as articles in this publication, they must be created at the subscriber manually.

How can I overcome this issue when publishing databases from one server to another while setting up transactional replication.

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-07T20:55:26.8666667+00:00

The obvious counter-question is: What you are trying to achieve? You are replicating some tables, and then you are replicating some stored procedures that refers to tables you are not replicating? What is the idea here?
Avyayah 1,291 Reputation points

2023-08-07T20:58:47.53+00:00

I see stored procedures were published but it created about 18 objects in stored procedure, with name starting like dbo.sp_MSdel* .....and dbo.sp_MSins_dbo.... and dbo.sp_MSupd. How did these objects get created?
Avyayah 1,291 Reputation points

2023-08-07T20:59:55.76+00:00

I am replicating Table, Views and Stored procedure so replicating entire database
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-07T21:04:13.88+00:00

I am replicating Table, Views and Stored procedure so replicating entire database

Yes, from your previous thread, I was thinking so. But that only makes your question even more mysterious.

And maybe at some point, you should review whether Replication is the solution to your problem.
Avyayah 1,291 Reputation points

2023-08-07T21:12:10.2133333+00:00

Looks like transactional replication is working as I was able to make changes to the tables in database although it showed this message during replication "Objects refenced by stored procedure are required"
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-07T21:14:33.52+00:00

If you are replicating the entire database, how can objects be missing? Or are these procedures referring to non-existing objects? If so, maybe they should be dropped...
Avyayah 1,291 Reputation points

2023-08-07T21:27:30.0233333+00:00

Well as of now I see it during publication the message "Object referenced by stored procedure and required"

but all objects are present on the database

Answer accepted by question author

0 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-07T20:55:26.8666667+00:00

The obvious counter-question is: What you are trying to achieve? You are replicating some tables, and then you are replicating some stored procedures that refers to tables you are not replicating? What is the idea here?
Avyayah 1,291 Reputation points

2023-08-07T20:58:47.53+00:00

I see stored procedures were published but it created about 18 objects in stored procedure, with name starting like dbo.sp_MSdel* .....and dbo.sp_MSins_dbo.... and dbo.sp_MSupd. How did these objects get created?
Avyayah 1,291 Reputation points

2023-08-07T20:59:55.76+00:00

I am replicating Table, Views and Stored procedure so replicating entire database
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-07T21:04:13.88+00:00

I am replicating Table, Views and Stored procedure so replicating entire database

Yes, from your previous thread, I was thinking so. But that only makes your question even more mysterious.

And maybe at some point, you should review whether Replication is the solution to your problem.
Avyayah 1,291 Reputation points

2023-08-07T21:12:10.2133333+00:00

Looks like transactional replication is working as I was able to make changes to the tables in database although it showed this message during replication "Objects refenced by stored procedure are required"
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-08-07T21:14:33.52+00:00

If you are replicating the entire database, how can objects be missing? Or are these procedures referring to non-existing objects? If so, maybe they should be dropped...
Avyayah 1,291 Reputation points

2023-08-07T21:27:30.0233333+00:00

Well as of now I see it during publication the message "Object referenced by stored procedure and required"

but all objects are present on the database

Answer 1

Anonymous

Hi @Avyayah

I saw your problem in this documentation: https://learn.microsoft.com/en-us/sql/relational-databases/replication/article-issues?view=sql-server-ver16.

The required action it presents is to navigate to the Articles page and add all the objects needed. This is the documentation for the Articles page: https://learn.microsoft.com/en-us/sql/relational-databases/replication/articles?view=sql-server-ver16.

Best regards,

Aniya

0 comments

Share via

Publishing databases in SQLserver

0 additional answers

Your answer