SSRS Divide by Zero

In SSRS you often have calculated fields that do division. To avoid having divide by zero errors, you may need to use IIF statements in the expression:
=IIF(Fields!column2.Value = 0, 0, Fields!column1.Value/Fields!column2.Value)
which means if the denominator is 0, show 0, otherwise show the calculated value.

Now for reasons unknown this only works if you are using integers. If you are using decimal values, then you will need to do some more work. The best approach I’ve seen is here, where Robert Bruckner provides a nice solution.

You would go to Report -> Report Properties -> Code and insert the following:

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Now you can do the following in your expression:
=IIF(Fields!column2.Value = 0, 0, Code.Divide(Fields!column1.Value, Fields!column2.Value))

SSRS Formatting

SSRS has some standard formatting options predefined, yet they seemed to have left out a couple of commonly used ones.
1. Formatting currency with no cents. To do this, in the column expression field put in =FormatCurrency(Fields!column1.Value, 0). The 0 means we want no decimal places to be shown.
2. Show a number with commas, no decimal places, and a zero if the value is zero. One way to do this is to set the formatting properties to #,### but if the numbers value is zero it will show nothing. So we would want to use #,##0 to show a zero if neccesary.

Property Owner is not available for Database

If a database becomes orphaned and has no database owner, you will get the following error message when you try to view the database properties in SSMS:

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)

You can use the following code to see orphaned databases.
SELECT databases.NAME AS DB_Name, server_Principals.NAME AS User_Name
FROM sys.[databases]
LEFT OUTER JOIN sys.[server_principals]
ON [databases].owner_sid = [server_principals].sid

To assign a new owner:
EXEC sp_changedbowner 'newuser'

If you get the following error message:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database
.

Open up the user’s Login Properties window under Security\Logins and uncheck the Map checkbox for the database and click OK. Basically the code won’t assign them as the new owner because it thinks they are already associated with that database. Now run the sp_changedbowner command again and it will work.

Failed Job Steps That Didn’t Notify An Operator

I often use jobs that have numerous steps. One example would be a job that has some initial prep work, multiple steps that run similar code on separate databases for multiple stores, and then finally some cleanup steps. I break the job down to individual steps per store so that if one of the steps fails, they all don’t fail. To do this, go to the advanced tab of the job step properties and set the ‘On failure action:‘ to ‘Go to the next step’. This allows the job to continue processing if there is an error on one of the steps. However, now it raises the question of how do we know if a step failed within a job? Unfortunately Microsoft doesn’t have built in failure notification for steps like they do with the overall job. You can view the job’s history and see if a step failed by the yellow icon, but that is not practical to check every day especially if you have multiple jobs setup this way. A better solution is to use the code below which shows jobs that recently had failed steps and did not notify an operator. It can be handy to setup in a SSRS report to keep an eye on all of your jobs that had failed steps.

-- FAILED JOB STEPS THAT DIDN'T NOTIFY AN OPERATOR VIA EMAIL
USE msdb
GO

DECLARE @DateStringToday VARCHAR(8);
DECLARE @DateStringYesterday VARCHAR(8);

SET @DateStringToday = convert(varchar, getdate(), 112);
SET @DateStringYesterday = convert(varchar, getdate()-1, 112);

SELECT
job_name = sj.name,
sj.enabled,
sjh.step_id,
sjh.step_name,
sjh.sql_message_id,
sjh.sql_severity,
sjh.message,
sjh.run_status,
sjh.run_date,
sjh.run_time,
sjh.run_duration,
operator_emailed = so.name

FROM msdb.dbo.sysjobhistory as sjh
INNER JOIN msdb.dbo.sysjobs_view sj ON sj.job_id = sjh.job_id
LEFT OUTER JOIN msdb.dbo.sysoperators so ON (sjh.operator_id_emailed = so.id)

WHERE sjh.run_status = 0
AND sjh.run_date IN(@DateStringToday, @DateStringYesterday) -- show today and yesterday
AND sj.enabled = 1 -- make sure it's enabled
AND sj.category_id != '101' -- remove SSRS report process jobs
AND so.name IS NULL -- show jobs that didn't already email an operator

ORDER BY sjh.run_date DESC, sjh.run_time DESC

Moving SQL 2000 Logins to SQL 2005

If you backup a database on one server and restore it to another, you can have the problem where the database has logins associated to it, but the instance does not. One problem, is that the database has a unique SID associated with the login name. If you create a new instance login with the same name, it will generate a different SID than the database one. I’ve read that if you are using 2005 and above, you simply create the new login for the instance and run the following to sync up the SID’s.
USE YourDatabaseName
GO
EXEC sp_change_users_login 'Update_One', 'UserName', 'UserName'
EXEC sp_change_users_login 'Auto_Fix', 'UserName'

However, in my case I was moving a database from SQL 2000 to SQL 2005 and these did not work.
The Update_One produced:
Msg 15063, Level 16, State 1, Procedure sp_change_users_login, Line 143
The login already has an account under a different user name.

What I had to do was:
1. Restore the database to the new server (SQL 2005)
2. Do NOT create a new login yet.
3. Run the following to find the unique SID associated to the database user account. Next we create a
new instance login with the same SID as the database account.

-- Look up the SID from the database
USE YourDatabaseName
GO

SELECT D.name AS [DB_LoginName], D.sid AS [DB_SID], S.name AS [Server_LoginName], S.sid AS [Server_SID]
FROM sys.database_principals AS D LEFT OUTER JOIN sys.server_principals AS S ON D.name = S.name

-- Next, take that SID and put it in here to create the login account
CREATE LOGIN UserName WITH PASSWORD = 'Password', SID = 0xB0A2667BAEDE1B4AB93EAA0F9525DD21

You can re-run the SELECT query to verify that they are indeed the same.

Date Formatting Functions

Different date formats are often neccesary on many occasions. Here are some Scalar-valued Functions you can use to help. Simply call your function and give it the full datetime field and it will return the formatted value depending on which funtion you call. For example; dbo.fn_dateYM(fullDateField) would return YYYY-MM.


-- Enter in full date. Return Year and Month: 2009-10
CREATE FUNCTION [dbo].[fn_dateYM]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(7)
AS
BEGIN
DECLARE @dateYM varchar(7)
SELECT @dateYM = CAST(YEAR(@dateMDYTime) AS CHAR(4)) + N'-' + RIGHT('00' + LTRIM(RTRIM(CAST(MONTH(@dateMDYTime) AS CHAR(2)))),2)
RETURN @dateYM
END

-- Enter in full date. Return Year: 2009
CREATE FUNCTION [dbo].[fn_dateY]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(4)
AS
BEGIN
DECLARE @dateY varchar(4)
SELECT @dateY = CAST(YEAR(@dateMDYTime) AS CHAR(4))
RETURN @dateY
END

-- Enter in full date. Return Month, Day, and Year: 10-14-2009
CREATE FUNCTION [dbo].[fn_dateMDY]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(25)
AS
BEGIN
DECLARE @dateMDY varchar(25)
SELECT @dateMDY = convert(varchar(25), cast(@dateMDYTime as smalldatetime), 101)
RETURN @dateMDY
END

-- Enter in full date. Return Month: 10
CREATE FUNCTION [dbo].[fn_dateM]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(2)
AS
BEGIN
DECLARE @dateM varchar(2)
SELECT @dateM = CAST(MONTH(@dateMDYTime) AS CHAR(2))
RETURN @dateM
END

-- Enter in full date. Return Day: 14
CREATE FUNCTION [dbo].[fn_dateD]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(2)
AS
BEGIN
DECLARE @dateD varchar(2)
SELECT @dateD = CAST(DAY(@dateMDYTime) AS CHAR(2))
RETURN @dateD
END

Renaming SQL Server

When you setup a server for replication you may find unexpectedly that SQL complains about your server name. The most likely reason is that the SQL Server name doesn’t match your machine name.
Run the following command to take a look:
SELECT @@SERVERNAME, SERVERPROPERTY(‘ServerName’)

The results should be identical. If they are different, you will need to rename your SQL Server name to match the machine name. To do this use the following:

use master
go
-- Remove old Server Name
exec sp_dropserver ‘OLDNAME’
GO
-- Add New Server Name
exec sp_addserver ‘NEWNAME’,’LOCAL’

You then need to restart SQL Server. Now open a new query and run the following again:
SELECT @@SERVERNAME, SERVERPROPERTY(‘ServerName’)
They should now be the same.