måndag 18 januari 2010

Ctrl-n No Longer Opens A New Query in SSMS 2008

Just found the answer to one of those little annoyances, you know. As of version 2008 Sql Server Management Studio does not respond to ctrl-n anymore. It has really bugged me but now I know how to fix it.

Change the keyboard layout to "SQL Server 2000" and then back to "Standard" and that will restore the default keyboard mappings.

http://anotherlab.rajapet.net/2009/03/getting-back-ctrl-n-shortcut-with-sql.html

fredag 15 januari 2010

Delayed Windows Service start, a workaround for a Reporting Services 2008 startup bug.

After a clean installation of MS SQL 2008 with Reporting Services running on a Windows server 2003 machine, you may get the following errors in the event log

Event Type: Error
Event Source: Report Server Windows Service (MSSQLSERVER)
Event Category: Management
Event ID: 107
Date: 2010-01-15
Time: 13:07:15
User: N/A
Computer: VIRTUALWSS
Description:
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.



What happens is that the SSRS service gets started to early and tries to connect to the SQL database, which not has entered a state ready for handling connections. However the reporting service retries and starts successfully, despite the errors that have been logged.

The bug is reported to Microsoft SQL team, see ID 377627, but will not be fixed. The supposed solution to the problem is to use Service dependences ; however that does not work for me.

My solution is to create a batch file that after a specified delay starts the reporting services service.

  1. Create a directory named "C:\Scripts".
  2. Run "notepad C:\Scripts\DelayedReportServer.bat " and copy-paste the following into the file:

    @echo off
    ping -n 60 127.0.0.1 >NUL
    net start ReportServer

    This will start the ReportServer service after an 60 seconds delay (ping is used only to generate the delay).
  3. Start a new CMD session.
  4. Now you should create a service named "SQLDelayedReportServer" using the instsrv.exe and srvany.exe tools. Run the following command:
    Instsrv.exe SQLDelayedReportServer "C:\Program Files\Windows Resource Kits\Tools\srvany.exe"
  5. Using regedit.exe, open the key:
    "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLDelayedReportServer"
  6. Create a new key named "Parameters"
  7. Under the "Parameter" key, create a new String value named "Application". Set its value the batch file you created (C:\Scripts\DelayedReportServer.bat). You should now have something like this:
  8. Now start the Service manager (Start->Administrative tools->Services).
  9. Set the startup type of "SQL Server Reporting Service" to Manual.
  10. Make sure that the startup type of SQLDelayedReportServer is set to Automatic.
  11. Reboot and hopefully will your Report Server start without any errors. If you still get the errors, try increase the delay in "C:\Scripts\DelayedReportServer.bat".

Reference:

How To Create a User-Defined Service