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.

SQL Service failed to start during 2005 install – Multicore Processor Issue


Recently came across an interesting situation where I was installing SQL Server 2005 Developer(RTM) and Enterprise on QA and PRODUCTION environments respectively.

During install I got an error, which said “The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually.”

You are prompted to Retry or Cancel the setup.

 I ensured that proper service accounts where used and permissions where set properly, still the issue was not resolved. I was sure that the setup is not corrupted, as the same was used enormous amount of times.

Was going through System configuration and strange fact attracted me, ie number of processors. Both servers had 24 processors. A quick check on Microsoft KB list proved that this was a known issue.

This issue happens when The number of physical cores is not a power of 2.

Fix :

http://support.microsoft.com/kb/954835/en-us

The workaround I used for both PROD and QA was :

Copied sqlservr.exe and sqlos.ddl from a different server which was having the same build which I was expecting for the new servers and pasted the same at Bin directory soon after I get the error message, Retry option was selected and the installation was completed.

Note: You need to patch sql server once again to ensure that it’s in supported state.

Microsoft has ensured that this glitch is resolved in 2008 and SP’s of 2005.Unfortunately 2005 doesn’t support slipstreaming, hence this KB needs to be referred.

I am interested to understand if anyone of you have faced this before.

SQL Server setup takes more time and is in hung state during ‘Setting File Security’


You might have encountered an issue where SQL Server setup will be in hung state during ‘Setting File Security’ phase.This is a known issue and is documented by Microsoft :

http://support.microsoft.com/kb/910070/en-us

The best workaround is to unplug network cable or just keep on waiting for atleast 2-5 hours!

Slipstreaming SQL Install


Recently I had to Install SQL Server 2008 Standard on a cluster and our setup was giving error like  ‘Value cannot be null;Parameter=Connection’

This issue was fixed when I tried Slipstreaming. What is slipstreaming? This is a combination of all the patches to the initial install (RTM). When you copy newer files over older files a slipstream is formed.

Below are the steps, which I followed to fix the issue:

Steps :

1 . Copy original SQL Server 2008 source setup to c:\SQLServer2008_FullSP1

2. Extract service pack to SQLServer2008SP1-KB968369-x64-ENU.exe /x:c:\SQLServer2008_FullSP1\PCU

3. Copy Setup.exe and Setup.rll from the PCU extracted location to original source setup location

4. Copy all files not the folders, except the Microsoft.SQL.Chainer.PackageData.dll, in c:\SQLServer2008_FullSP1\PCU\<architecture> to C:\SQLServer2008_FullSP1 \<architecture> to update the original files.

5. Modify the defaultsetup.ini by adding PCUSOURCE=”{Full path}\PCU”,ie PCUSOURCE=”C:\SQLServer2008_FullSP1 \PCU”

6. Ran the setup

Cluster was build and there was no more errors.

File format is not valid! Error during SQL 2008 R2 Developer Edition Install


Recently I received an error while installing SQL Server 2008 R2 Developer Edition.The error was saying about ‘File format is invalid’.

Resolution : This erorr comes up when you have a corrupt sql setup.Solution is to download the setup once again from MSDN or VLSC site.

You also need to ensure that the ISO file which is downloaded from the site is properly extracted using a tool like Winrar(recommended).

I love to hear if you have faced this error anytime before and was there a different fix which was applied.