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

Dealing with orphaned users in SQL

SQL Server logins access individual databases using a database user that is mapped to the SQL Server login. There are two exceptions to this rule:

• The guest account. This is an account that, when enabled in the database, enables SQL Server logins that are not mapped to a database user to enter the database as the guest user.

• Microsoft Windows group memberships. A SQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

Information about the mapping of a SQL Server login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization in the database.
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Finding Orphaned Users

USE <database_name>;
GO
sp_change_users_login @Action='Report';

Outputs a list of users in the selected database that are not linked to any SQL Server login.

N.B.	sp_change_users_login cannot be used with SQL Server logins that are created from Windows.

Fixing orphaned users

The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';

• See books online Troubleshooting Orphaned Users which is where the above information was copied from.

Firing MDS Business Rules on dependent entities

I posted a question on the MSSQL MDS forum about

I have come across a few issues around business rules in MDS that look like they could be bugs but haven't been able to find anyone else online with similar problems...

I have a Customer Entity which is the upper level of my Customer hierarchy. At the lower level I have a Customer Store Location Entity which has domain based attributes of the Customer and Store Location. I've configured a business rule to set the Customer Store Location Name to be a concatenated string of the 'Customer Name' + ' - ' + 'Store Location' attributes which is set to trigger when the Customer Name attribute changes.

When editing the Customer entity at the top level, the Customer Store location Entity shows this change but even when running the business rules at both levels, the Customer Store Location Name does not update.

My two questions are:

1. Why is the change to Customer not picked up by the Customer Store Location entity's business rule even though I can see the attribute has changed?
2. Is there any way of triggering business rules to run for multiple entities if a change is made?

Suzanne Selhorn posted the following response which has done the trick

I think I have this figured out-- you need two rules.

Step 1: Set Customer Name attribute to be in a change tracking group (1).

Step 2: For CustomerStoreLocation entity, the first rule is: If has changed in group 1, then Customer = dba.Customer.Name.

Step 3: For CustomerStoreLocation entity, the second rule is: concatenate CustomerStoreLocation Name by using your two values. This doesn't need a "has changed" aspect to it, because you always want it to be true. So this rule is an action only, no condition.

Let me know if you need more details. If you run these two rules in order you should be good I think.