Finding text in a Stored Procedure SP

Discovered this handy bit of code on the web.


SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%foobar%' 
    AND ROUTINE_TYPE='PROCEDURE'

Viewing SQL Job history via TSQL

This SQL Server script will display job history. The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly. You will need to replace with the job you want to see history for.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
WHERE job_name = ''
order by job_name, run_datetime

Replication errors we have encountered
SQL Server Replication requires the actual Server name
This is can arise in our dev and test environment when trying to configure replication on a cloned server. The problem is that the servername becomes unregistered at some point in the process.

To check whether this is the problem run

SELECT @@SERVERNAME
This should be the name of the SQL Server Instance e.g. MSSQLSERVER\PROD1

Resolution:
Run the following SQL

-- This first bit will not be needed if @@SERVERNAME IS NULL
Use Master
go
Sp_DropServer '[wrongname]'
GO

-- This is the important bit
Use Master
go
Sp_Addserver 'MSSQLSERVER\PROD1', 'local'
GO
Then restart the SQL Services

Profile not valid
Troubleshooting Database Mail: Profile not valid

Caused due to DDL Trigger on stored procedures on the Hub database which attempts to send a notification email if it is triggered.

Resolution: ensure that the database mail profile specified in the DDL trigger exists on the server and works.

INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
Caused due to DDL Trigger on stored procedures on the Hub database - only affected one publication for some reason.

Resolution: only way round this found so far is to drop the DDL trigger on stored procedures.

Server: Msg 14117, Level 16, State 1, Procedure sp_dropdistributiondb, Line 39 'Distribution' is not configured as a distribution database
MSSQL_ENG014117

Related stuff
Add links to related pages or external web sites here

Msg 20581, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 22
Cannot drop server 'repl_distributor' because it is used as a Distributor in replication.

Run the following:

EXEC master.dbo.sp_serveroption @server=N'MSSQLSERVER\DEV1', @optname=N'dist', @optvalue=N'false'
GO
Could not execute 'sp_replcmds'
Error messages:
The process could not execute 'sp_replcmds' on 'GP10'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
Get help: http://help/15517

etc.

Run the following on the database being published:

ALTER AUTHORIZATION ON DATABASE::Hub_staging to sql_deploy
Where 'sql_deploy' is the database owner

Error: The process could not execute 'sp_repldone/sp_replcounters' on 'GP10'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Run the following job on the publication server.

sp_replrestart

The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074)
Run the following script on the publisher database:


EXEC sp_reinitsubscription @publication =  'Pub Master.Product', @subscriber = 'MSSQLSERVER'


Cannot delete the publication because there is a subscription
I had a problem where there was no subscrition to the publication and i still could not delete it. The distributor database was stopping the deletion, i managed to delete it and recreate it in the end but to delete it i used this command to ignore the distributor

exec sp_droppublication @publication = N'Pub uvwProduct',@ignore_distributor = 1
Unable to shrink transaction log
Run the following:

SELECT name, log_reuse_wait_desc
FROM sys.databases
If log_resuse_wait_desc says REPLICATION then there could be some problems with replication not being marked as done so run the following:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1