Some SQL Server 2005 services are not listed in SQL Server Configuration Manager – Also Red Cross shown in IIS – Server 2003

PROBLEM

  1. Some SQL Server 2005 services are not listed in SQL Server Configuration Manager
  2. Red Cross shown in IIS – Server 2003

SOLVED QUICK

BITS – Background Intelligent Transfer Service may not be registered and may have a startup type of Manual and be Stopped.

regsvr32.exe qmgr.dll
regsvr32.exe qmgrprxy.dll

Then reboot.

SOLVED DETAIL

by external support company. Asked them how they did it. Answer copied here:

It looks like a windows update was responsible for the failure, Steve correctly spotted the fundamental issue which was that the default web site was stopped in IIS.  By attempting to start IIS, this registered an event in the windows logs and I could diagnose the issue from there.

The earlier versions of SQL reporting prior to 2008 relied on IIS to operate and the failure of IIS to start resulted in the missing items in SQL.

Windows update installed a hot fix which altered a file responsible for BITS (the service that enables Windows Update to function), the update applied an updated version of a file for IIS and the file failed to register when it was installed.  IIS was effectively looking for a file which wasn’t present and stopped itself.

By re-registering the file and rebooting, IIS was able to recognise the updated file and the default web site started successfully.  As the default website hosts information referred to by SQL, this caused SQL reporting to re-appear.

An odd one, but then Microsoft updates are probably responsible for a lot of strange behaviour!

Incidentally, while I was on the server, I spotted a string of Windows Desktop Search errors in the log – Windows Desktop Search was given as an update by Microsoft and it is my personal view that the product is poorly designed and disables perfectly good search facilities included within Windows, it looks like it has been clogging your event log with errors for some time now and with both of these things in mind,  I have disabled the Windows Desktop Search.

Anyway, glad SQL & IIS are now working, please let me know if you have any further issues.

Advertisements

SSRS Subscriptions – Email – Windows File Share

So easy does not need much detail here.
My publish on a schedule to either a file share or by email.
To use Email settings need to set up in SSRS configuration of server.

PROBLEM
Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.

SOLUTION
The report credentails were Windows Authentication. For my scenario it was feasible to save low read-only credentials with the report.

SQL Server Report Manager takes a long time to start up

See post below
May need to go to Programs > turn windows features on or off for IIS Manager

http://www.developmentnow.com/g/115_2005_11_0_0_641257/Report-Manager-Start-Up.htm

I believe the setting you are looking for is the “Shutdown worker processes
after being idle for…” on the Performance tab of the application pool that
the Reports virtual directory is assigned to.
See old post below for details:
[quoted text, click to view]

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue – I’ll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default – On the recycling tab
there is a checkbox for recycling worker processes – it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab – which is the one you are
interested in changing….
See the “Idle Timeout” section and increase the number of minutes to be 8
hours a typical working day – 8*60 = 480 minutes.
Next, to be sure the “morning person” that runs the first report doesn’t get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.

SSRS Configuration – “user … does not have required permissions. Verify that .. and … UAC …

SOLVED

User ‘<server>\<username>’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

“To get this resolved you need to start you Internet Explorer as Administrator -> Run as Administrator then open the url http://<Server name>/Reports this will take you to the “SQL Server Reporting Services” Home page
Click on the “Folder Settings” button.
Click on the “New Role Assignment” button.
Add the Group or user name: DOMAIN\USER_NAME, and permissions you have logged in as.
Click on Ok button.
This should resolve the issue.”

SQL Server Shrink – Maintenance

Does not always work straight off bat:

  1. Need to backup both database and transaction log regularly and possibly before shrink
  2. Try shrinking Database
  3. Try shrinking Database File and Transaction Log File
  4. Repeat above sequences until files shrink

There may be something blocking the shrink. Try the following query. Should return NOTHING in the second column.
If LOG_BACKUP then the space may not be released until a log backup is done.

SELECT name,log_reuse_wait_desc FROM sys.databases ORDER BY name

SSRS – Configure for local network access

 
See next blog entry.
This entry was written when trying to use VS-TFS reports on Windows 7. This is not possible, so do not try. However configuration below does work over home network.
 
Found a TFS report cofiguration help website that said use Local System. This is for 2008 but still works
then on this page look for “To verify or correct the configuration of SQL Server Reporting Services”
 
On same page it says .. howeve the real reason in 2010 at least is because W7 does not support this.

Documents and Reports are Unavailable in Team Explorer

When you connect to Team Foundation Server using Team Explorer, the Documents and Reports nodes of a project may be unavailable. This is indicated by a red x on the nodes. This may indicate that the firewall on the computer that hosts Team Foundation Server does not have an exception configured for port 80. You must add this port to the exception list for your firewall application. For more information, see How to: Verify or Correct Port Assignments.

 
 

 
This works – other configurations may also work
Normal configuration
Service account = Local Service
Database credentials windows account
 
On other computer
Internet Explorer > Tools Internet Options > Security > Trusted Sites > Sites > http://nameofserver
 
On report server computer
Go to report manager url > Home > Folder settings > New Role Assignment > Include Content Manager

SSRS Configuration

 
> Internet > Tools > Options > Security > Local Intranet > Sites > Advanced. Add address of local server.
 
For me only – see files in vs\projects\ssrs\configuration