December 7, 2021 at 5:39 pm
I have replication set up and for a variety of reasons I don't want to have the schema updated automatically when a field is added on the publishers. We have many databases feeding into a single master database and if I have the schema updated automatically, it will attempt to update the field for each database, causing an error after the 1st one is executed.
What I do want to do is to have a script that will check the boxes for the added fields in the article definition. Is there way to access the checkbox in TSQL code? I already have a data-driven way to connect to each of the publisher databases in turn and execute code on each.
Thanks
December 7, 2021 at 7:58 pm
I am not certain what you mean by "access the checkbox" in TSQL code, but I am 99% sure that all configuration options within SQL Server for all available features are configurable from within TSQL.
Most, if not all, configuration windows have a "script" button which will generate the script for the change you are making. So you could check the checkbox, then click on the script button and you should have the code for that checkbox.
Mind you, the above could be me misunderstanding your requirements...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 7, 2021 at 8:58 pm
I included a screenshot of where I would need to add checks in the checkboxes. I only included a portion of the fields in the table, but there are others below that are unchecked that I would like to check via a script. There s a script icon, but it is greyed out and cannot be selected.
December 7, 2021 at 9:20 pm
Yep - you see that greyed out "script" button? Click on a checkbox then click on the script button then cancel out of that window.
That script button will tell you how to make your changes in TSQL!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 7, 2021 at 10:10 pm
Unfortunately, the script button is not enabled in that window. That was the first thing I looked at.
December 8, 2021 at 12:22 pm
You can disable the replication of DDL changes via the GUI, right-click on publication, click properties, select subscriber options and disable 'Replicate schema changes'
You can script out your replication, right-click on the publication and select Generate Scripts. You will then get the commands to add tables and columns into your replication, which I think is what you are after. The commands are sp_addarticle and sp_addarticlecolumn
If you just want to know what tables and columns are already in replication, you can query sysarticles and sysarticlecolumns in the database or run sp_helparticle and sp_helparticlecolumns
December 8, 2021 at 9:59 pm
Thanks for your help. That's exactly what I was looking for.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply