SQL Server 2005 Performance Dashboard Report Error ‘Difference of two datetime columns caused overflow at runtime’


Recently I was deploying SQL Server 2005 performance dashboard for a SQL Server 2005 Instance and after I ran Setup.SQL I tried to pull the report using performance_dashboard_man.rdl and instantly got an error ‘Difference of two date time columns caused overflow at runtime’.

It was clear that there is issue with DATE conversion and after some research found that a workaround was already in place for the issue. I would like to share this to all my friends –

Workaround –

Open the Setup.SQL script (which is installed along with the performance dashboard – you’ll find it at C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\). Find line 276, which says:

sum(convert(bigint, datediff(ms, login_time, getdate()))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

and replace this line with

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

This workaround will work for you.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s