I needed to scrub a column of power meters.  I needed to remove certain characters that had found their way into the system.

SELECT  dbo.site.power_meter

      , REPLACE(REPLACE(REPLACE(REPLACE(dbo.node.power_meter, ' ', ''), '-',

                                ''), '/', ''), '.', '') AS better_power_meter

FROM    dbo.node

This was fast enough on a under a hundred thousand rows. You might want to try using CLR with RegEx if you have billions to clean.

How do you prepare for a master rebuild?  This tutorial will make sure you have what you need to get yours back online easier: Part 1, Part 2, Part 3

I needed to split a field that looks like this: 'MRLMCADE---01CAB101A' into 2 fields at the '---' mark.  I could not count on the '---' always starting at the 9th character so I used the following:

LEFT( system_id , CHARINDEX( '---', system_id ) - 1 ) as Part_1

RIGHT( system_id , CHARINDEX( '---', REVERSE( system_id ) ) - 1 ) as Part_2

Now the hyphens could appear anywhere and the split would still work.

One of my logins would not repair after a restoration in my development environment.

When the SQL login (mismatched user) exists and you use autofix parameter then EXEC sp_change_users_login 'Auto_Fix', 'phone' procedure will fix it without errors.

But consider the situation when a user "phone" exists in the database but the login "phone" doesn't exist on the server. You need to provide two more parameters (loginname and password) for the procedure to work. You need to use null for loginname and you need to provide a password. As follows

EXEC sp_change_users_login 'auto_fix','phone',null,'Pa$$w0rd'

Now the procedure will search for the login 'phone'. If it exists it will map with the 'phone' login.  If not, it will create a login with the password specified and maps the user to the login.

When you setup SQL Server replication via snapshots, a new job is created on the distributor: 'Distribution clean up: distribution'

This calls a job dbo.sp_MSdistribution_cleanup that will clean up the records from the MSrepl_commands and MSrepl_transactions tables. What I didn't know was that it  also deletes any expired snapshots stored in the Replication folder.

For this process to work 3 things must be setup properly.  

• xp_cmdshell must be enabled
• SQLAgent account must have proper permissions to run xp_cmdshell
• Proper permissions must be set to delete the specified folder on the share.

Only members of the sysadmin role has permission to run xp_cmdshell, if your SQLAgent account is not a member of sysadmin role, the job could be given permission to run xp_cmdshell by using a proxy account.