[Code] IF EXISTS for procedures using INFORMATION_SCHEMA

Adam Machanic's code does two things that I love, but don't think to do.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'yourprocname')

      EXEC ('CREATE PROC dbo.yourprocname AS SELECT ''stub version, to be replaced''')

GO

1. It uses the INFORMATION_SCHEMA tables. These are like the system tables, but are more portable (every database vendor has them and they all look the same), and are meant to be human-readable. The downside is that they don't necessarily have all the details you need. I use INFORMATION_SCHEMA.TABLES AND INFORMATION_SCHEMA.COLUMNS all the time, but forget about ROUTINES, which includes functions and stored procedures.
   
2. By creating a stub entry, it ensures permissions remain, as well as other things like the original creation date. If you have an automated environment, you might do a DROP/CREATE, which would break all your explicit permissions (and reset the create_date). By doing a stub entry then an ALTER, you ensure those remain.
   
   Highly recommended code.

[Replication] Better alternative to Replication Monitor

(changed on 10/12 - whoever woulda thunk a UNION wouldn't work right?)
(changed 2013/04/08 - realized the IF cluase at the end can be confusion if you're not thinking about it)

I hate Replication Monitor. Here are a couple of scripts that, embedded in a job, will better help you. I need to look into adding special alerts for replication. The main change is the addition of snapshot detection - we have missed issues because it somehow falls offline and doesn't notify that the replication needs to be snapshotted.

IF OBJECT_ID('tempdb.dbo.##replication_command_count') IS NOT NULL

DROP TABLE ##replication_command_count

SELECT SUM(UndelivCmdsInDistDB) AS UndelivCmdsInDistDB,

MSdistribution_agents.NAME,

MSdistribution_agents.publication,

subscriber_id,

subscriber_db

INTO ##replication_command_count

FROM MSDistribution_Status

INNER JOIN MSdistribution_agents

ON MSDistribution_Status.agent_id = MSdistribution_agents.id

WHERE UndelivCmdsInDistDB > 0 --show only those that are backed up

AND subscriber_id > 0 --negative subscriber IDs are for those that

--always have a snapshot ready

AND MSdistribution_agents.NAME NOT LIKE '%someserverthatthrowserrors%'

GROUP BY MSdistribution_agents.NAME, MSdistribution_agents.publication,

subscriber_id, subscriber_db

ORDER BY MSdistribution_agents.NAME, MSdistribution_agents.publication,

subscriber_id, subscriber_db

--delete from stuff we don't care about; refinement of the IF below.

delete from ##replication_command_count

where publication = 'a_busy_publication'

and UndelivCmdsInDistDB <>

delete from ##replication_command_count
where (publication = 'abusypublication' and UndelivCmdsInDistDB < 100)
--add whatever OR clauses are needed to remove your busy publications

IF (SELECT MAX(UndelivCmdsInDistDB) FROM ##replication_command_count) > 20
--20 is our threshold to send
BEGIN 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA',

@recipients = 'dev@null.com',

@subject = 'Replication is backed up on REPL server'

,@query = 'select left(publication,20), convert(varchar(9),UndelivCmdsInDistDB), left(name,90) from ##replication_command_count where UndelivCmdsInDistDB >20'

,@query_result_header = 0

END 

----------------------------

Script 2 -- looking for errors.

IF OBJECT_ID('tempdb.dbo.##replication_errors') IS NOT NULL

DROP TABLE ##replication_errors

SELECT

errors.agent_id,

errors.last_time,

agentinfo.name,

agentinfo.publication,

agentinfo.subscriber_db,

error_messages.comments AS ERROR

INTO ##replication_errors

FROM

--find our errors; note that a runstatus 3 can be the last message, even if it's actually idle and good

(SELECT agent_id, MAX(TIME) AS last_time FROM distribution.dbo.MSdistribution_history with (nolock)

WHERE (runstatus IN (3,5,6) AND comments NOT LIKE '%were delivered.' AND comments NOT LIKE '

or (runstatus = 4 and comments like 'The initial snapshot%is not yet available.') GROUP BY agent_id) errors

FULL outer JOIN

(SELECT agent_id, MAX(TIME) AS last_time FROM distribution.dbo.MSdistribution_history with (nolock)

WHERE (runstatus IN (1,2,4) and comments not like 'The initial snapshot %is not yet available.')

OR comments LIKE '%were delivered.' GROUP BY agent_id

) clean

ON errors.agent_id = clean.agent_id

--grab the agent information

LEFT OUTER JOIN distribution.dbo.MSdistribution_agents agentinfo

ON agentinfo.id = errors.agent_id

--and the actual message we'd see in the monitor

LEFT OUTER JOIN distribution.dbo.MSdistribution_history error_messages

ON error_messages.agent_id = errors.agent_id AND error_messages.time = errors.last_time

where errors.last_TIME > ISNULL(clean.last_time,'20100101')

AND comments NOT LIKE '%TCP Provider%'

AND comments NOT LIKE '%Delivering replicated transactions%'

AND name NOT LIKE '%suckyservername%'

IF (SELECT COUNT(*) FROM ##replication_errors) > 0

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'dba',

@recipients = 'dev@null.com',

@subject = 'Replication errors on REPL server'

,@query = 'select * from ##replication_errors'

,@query_result_header = 0

DROP TABLE ##replication_errors

[Replication] Simple replication to find an issue

Say you get a message like this:

Cannot insert duplicate key row in object 'dbo.yourtable' with unique index 'yourtable_Index'

Run this:

--run from wherever the table exists

DECLARE @publisher_database_id INT, @article_id int

SELECT @publisher_database_id = id FROM distribution.dbo.MSpublisher_databases where publisher_db = DB_NAME()

SELECT @article_id = artid FROM dbo.sysarticles WHERE dest_table = 'yourtable'

EXEC distribution..sp_browsereplcmds @publisher_database_id = @publisher_database_id, @article_id = @article_id