I spent many hours over the past few days trying to get Reporting Services up and running on my new box. I had set up Reporting Services many times before without difficulty, but for some reason, it just didn’t want to cooperate this time. The setup succeeded, but when I tried to access Report Manager, Report Manager reported 404s – that it could not find the root folder. (The Report Manager default page appeared. So it wasn’t an ASP.NET issue.) When I tried to deploy reports to the server, Visual Studio .NET 2003 reported that the web service did not exist. Strangely when I asked for the WSDL using a browser pointed at http://localhost/ReportServer/ReportingService.asmx?wsdl, I received back the expected WSDL. Here’s what I found in c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles\ReportServerWebApp__03_28_2005_11_06_50.log:
— various bits of startup information
aspnet_wp!ui!ce4!3/28/2005-11:06:52:: e ERROR: The request failed with HTTP status 404: Object Not Found.
aspnet_wp!ui!ce4!3/28/2005-11:06:52:: e ERROR: HTTP status code –> 500
——-Details——–
System.Net.WebException: The request failed with HTTP status 404: Object Not Found.
— remainder of stack trace omitted for brevity
aspnet_wp!ui!ce4!3/28/2005-11:06:52:: e ERROR: HTTP status code –> 500
——-Details——–
System.Net.WebException: The request failed with HTTP status 404: Object Not Found.
— remainder of stack trace omitted for brevity
So I started poking around the Reporting Service installation troubleshooting guide as well as Google without success. So I put my Reporting Services woes on the backburner for awhile and went on to other tasks. While working on one of these unrelated tasks, I fired up SQL Profiler to capture a trace for an application I was working on. I noticed that SQL Server was reporting the wrong hostname. The folks who had prepped my new box had built it from a Ghost image. They had changed the hostname in the OS, but had neglected to update the hostname in SQL Server’s system tables. I changed the hostname in the system tables to match the hostname in the OS and suddenly Reporting Services started working!
The moral of this story: When SQL Server is setup, it records its hostname in the system tables. If you have any Ghost images including SQL Server, you must change SQL Server’s hostname after changing the OS’s hostname. You will experience all kinds of woe if there is a name mismatch between what SQL Server thinks the hostname is and the actual hostname.
Steps to Resolve the Problem
First verify that this is in fact your problem. Launch SQL Query Analyzer and run the following script:
— Start script
sp_helpserver
GO
— End script
Note the hostname that SQL Server reports. If this doesn’t match your computer’s hostname, you need to change it. (I’ll assume that you know your computer’s hostname or how to find it in My Computer… Properties.) If the hostnames don’t match, here’s a script to correct the problem. You’ll have to substitute appropriate values for OLDNAME and NEWNAME in the script below. N.B. The parameter ‘local’ in sp_addserver is important as it tells SQL Server that this is its hostname rather than the hostname of a linked server.
— Start script
sp_dropserver ‘OLDNAME’
GO
sp_addserver ‘NEWNAME’, ‘local’
GO
sp_helpserver
GO
— End script
sp_helpserver should now report the correct hostname. Since SQL Server only reads its hostname information at startup, you’ll have to restart SQL Server for these changes to take effect. Once you’ve restarted SQL Server, Report Manager should be working again.
