SQL Server
Split field on space or other characters
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 [...]
Autofix won’t fix an orphaned user
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 [...]
SSRS Formatting subtotals in a matrix
If you need to make your subtotals look different than your data, it's not very apparent how to do so. I've found 2 ways. You can conditional format the data cell and the results will affect the subtotals. The trick is to use INSCOPE. What you are going to do is check and see if [...]
Allow cleanup for snapshot replication
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 [...]
Custom colors (or defined colors) in charts in SSRS
I needed specific colors for specific values on a pie chart in SSRS. We were charting work requests in a month. Each job had one of four states: completed, missed, pending and due today. I wanted to color code those values to dark green, red, lime and orange respectivly. The existing color palette (and options) was [...]