Checking if articles are set according to standards

Question

Checking if articles are set according to standards

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
April 2, 2010 at 8:44 pm

#217378

Hello,
I need to check if the articles for publishers are set according to standards or not and if they aren't then send and email saying that this publication and this article is not set to standard.
By standard I mean the property of articles like@pre_creation_cmd = delete
@schema_option = 0x0000000000000000 @inst_cmd = SQL ...
Any ideas how to approach this. How do I check this for all publications ?
I am planning to do it as a sql agent job checking for a particular server and sending out email.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

I think you'd have to have multiple checks, since each publication/article would have potential separate settings.

What I might do is find the settings in the system, and then copy them to a table you make. Ru a compare each xxx hours, and if you find some differences, send an alert.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

Steve Jones - Editor (4/2/2010)
I think you'd have to have multiple checks, since each publication/article would have potential separate settings.
What I might do is find the settings in the system, and then copy them to a table you make. Ru a compare each xxx hours, and if you find some differences, send an alert.

Thanks Steve !

Actually I am thinking it in the same way. Below is what I have done so far for testing:

DECLARE @publication SYSNAME

SET @publication = N'Adventureworks_pub';

USE AdventureWorks

EXEC sp_helparticle @publication = @publication;

GO

Above query will give me all the details that I want for particular publication.

Now if I have mutiple publications I can use sp_msforeachdb to loop for all publication dbs and then use sp_helparticle to get the results into a temp table.

My concern is that how can I compare the values in temp table to the one which I want to impose as standard .. like if any value is not it is suppose to be is there any way I can compare and if it does not evaluate to what I want, then it should send an email.

Does my approach sound right ?

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

mangeshd Right there with Babe Points: 744 More actions · Answer 3

You can easily check the article settings by querying the <publisher db name>..sysarticles table. Check the values of INS_CMD, UPD_CMD and DEL_CMD. You will have to do this in each database so can write a simple command like this:

sp_msforeachdb 'declare @i int;select @i=category from master..sysdatabases where name=''?'';print @i;if @i=1 select INS_CMD, UPD_CMD, DEL_CMD, * from ?..sysarticles;'

Then compare the first three column values values from each article with your standards and then use DBMaill to send you an email if that does not follow your standards.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

What you need to do is load a real table with the standards you expect. Then do the comparison that way and notify people.

You should have a standard list of settings that you can copy to each server running replication, and include it, along with the procs, jobs, etc., as part of the replication setup.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 5

mangeshd (4/6/2010)
You can easily check the article settings by querying the <publisher db name>..sysarticles table. Check the values of INS_CMD, UPD_CMD and DEL_CMD. You will have to do this in each database so can write a simple command like this:
sp_msforeachdb 'declare @i int;select @i=category from master..sysdatabases where name=''?'';print @i;if @i=1 select INS_CMD, UPD_CMD, DEL_CMD, * from ?..sysarticles;'
Then compare the first three column values values from each article with your standards and then use DBMaill to send you an email if that does not follow your standards.

Hello,

Thanks for your reply. Can you give me some idea of how to comapre and send email. I am novice to writing code and eventually learning.

I want to know how to compare specific columns between two table and send email.

any example will be a good start for me.

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

mangeshd Right there with Babe Points: 744 More actions · Answer 6

I don't have a code readily available but you can easily compare column values using simple LIKE operator since you are majorly doing string comparison. This can be done in queries or for variable values as well.

As far as sending email is concerned, then you will have to configure http://msdn.microsoft.com/en-us/library/ms175951.aspxDBMail (follow MSDN for the same) and then use sp_send_dbmailhttp://msdn.microsoft.com/en-us/library/ms190307.aspx to send the actual emails.