SQL Server database went to Recovery Pending State

My SQL server went to "Recovery Pending" state after I mistakenly enabled folder compression for the folder where the database files are residing.
To recover,

1. Stop SQL services
2. Disable folder compression in the folder where SQL database files are stored
3. Start SQL services
4. Execute below recovery steps if still having trouble to use the DB. in case the DB went to single user mode, just the last step from below is needed

ALTER DATABASE [SQL-DB-Name] SET EMERGENCY;
GO

ALTER DATABASE [SQL-DB-Name] set single_user
GO

DBCC CHECKDB ([SQL-DB-Name], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO

ALTER DATABASE [SQL-DB-Name] set multi_user
GO

How to return value of the identity column after an insert in SQL server

Table MyTable with Id as an IDENTITY column (auto insert records)

CREATE TABLE [dbo].[MyTable] (
[Id] int IDENTITY(1, 1) NOT NULL,
[Component] varchar(50));

A Sample procedure that inserts a record and returns the id of the record inserted

CREATE PROCEDURE [dbo].[InsertRetId] 
@Component [VARCHAR](50),
@Id int output
AS
BEGIN

INSERT INTO [dbo].[MyTable]
           ([Component]
     VALUES
           (@Component);

select @Id = Scope_Identity();

END

SQL Server Reporting Services (SSRS) and HTTP 400 Bad Request (Invalid Hostname)

If iis (Internet Information Services) is stopped and SSRS (SQL Server Reporting Services) is kept running, you will receive HTTP 400 error with message Bad Request (Invalid Hostname) for any sites that was accessible under the same iis host.

This is bad if you are running under a LB (load balancer) and users accessing the site will start getting the 400 errors.

Only solution is to be sure you stop or start iis and ssrs at the same time. You cannot have one stopped while the other is kept running.

Provider load failure on SQL Server 2008 R2 reporting services (SSRS) installation

Below was the error on the log:

2014-03-09 03:08:36 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Setup
2014-03-09 03:08:36 Slp: Sco: Attempting to get registry value SQLPath
2014-03-09 03:08:36 RS: Setting InstanceId.
2014-03-09 03:08:36 RS: Setting Authentication Type.
2014-03-09 03:08:36 Slp: Sco: Attempting to get account sid for user account NT AUTHORITY\NETWORK SERVICE
2014-03-09 03:08:36 Slp: Sco: Attempting to get sid for user account NT AUTHORITY\NETWORK SERVICE
2014-03-09 03:08:36 Slp: Sco: GetSidForAccount normalized accountName NT AUTHORITY\NETWORK SERVICE parameter to NT AUTHORITY\NETWORK SERVICE
2014-03-09 03:08:36 RS: Setting AsOleDb folder.
2014-03-09 03:08:36 RS: Persisting configurations.
2014-03-09 03:08:36 Slp: Configuration action failed for feature RS_Server_Adv during timing ConfigRC and scenario ConfigRC.
2014-03-09 03:08:36 Slp: Provider load failure
2014-03-09 03:08:37 Slp: The configuration failure category of current exception is ConfigurationFailure
2014-03-09 03:08:37 Slp: Configuration action failed for feature RS_Server_Adv during timing ConfigRC and scenario ConfigRC.
2014-03-09 03:08:37 Slp: System.Management.ManagementException: Provider load failure
2014-03-09 03:08:37 Slp:    at System.Management.ManagementException.ThrowWithExtendedInfo(ManagementStatus errorCode)
2014-03-09 03:08:37 Slp:    at System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
2014-03-09 03:08:37 Slp:    at Microsoft.ReportingServices.WmiProvider.WmiProviders.GetInstance(String machineName, String instanceName, RSWmiNamespaces rsNamespace, ConnectionOptions connOptions, ObjectGetOptions getOptions)
2014-03-09 03:08:37 Slp:    at Microsoft.ReportingServices.WmiProvider.WmiProviders.GetReportServerAdmin(String machineName, String instanceName, ConnectionOptions connOptions, ObjectGetOptions getOptions)
2014-03-09 03:08:37 Slp:    at Microsoft.ReportingServices.WmiProvider.WmiProviders.GetReportServerAdmin(String machineName, String instanceName)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.RSExtension.Configuration.ConfigurationWMI.Connect(String instanceName)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.RSExtension.Configuration.ConfigurationAPI..ctor(String configFilePath, String instanceName)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.RSExtension.Configuration.ReportServerConfiguration.GetConfigurationAPI(String instanceName)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.RSExtension.Configuration.ReportServerConfiguration.Persist(String instanceID, String instanceName)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.RSExtension.SQLRSConfigurationPrivate.Install_ConfigRC(Dictionary`2 actionData, PublicConfigurationBase spcb)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.RSExtension.SQLRSConfigurationPrivate.InstallImpl(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.RSExtension.SQLRSConfigurationPrivate.Install(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.PrivateConfigurationBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, ConfigBaseAction action, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SqlFeatureConfigBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, ConfigBaseAction action, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
2014-03-09 03:08:37 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
2014-03-09 03:08:37 Slp: Exception: System.Management.ManagementException.
2014-03-09 03:08:37 Slp: Source: System.Management.
2014-03-09 03:08:37 Slp: Message: Provider load failure .

Turns out http://support.microsoft.com/kb/980142 describes the same error.

Solved by 

1. running SQL Server R2 installation, do not select Reporting services at this time, select something else (eg: client utilities), complete install
2. Install SQL Server R2 Service Pack 2 (SP2)
3. Go back to R2 install, this time select Reporting Services, complete install (no error this time - yay)
4. Install SP2 again and complete install