Error 233

Sometime you may get the below error while connecting to SQL Server.

A Connection was successfully established with the server, but than an error occurred during the login process, (Provider: Shared Memory Provider, error: 0 - No process is on the other end end of pipe.)
(Microsoft SQL Server Error: 233)

The above error clearly states that maximum value for user connection is already reached so you can't login to SQL Server. If you are the system administrator than you can increase the maximum value by using the SP sp_configure.

Use the below steps to increase the value. 

Open the SQL Server Management Studio.
Write down the below queries:

sp_configure 'show advanced options', 1
Go
reconfigure
Go
sp_configure 'user connections', 0
Go
reconfigure
Go

Date time formatting in SQL Server

Below is a small script that shows you the date output in different format:

It just outputs the current date in the various formats so you can quickly find the one your after


DECLARE @date datetime
DECLARE @i int
SET @date = getdate()
SET @i = 1
WHILE(@i <= 14)
  BEGIN
 PRINT CONVERT(VarChar, @i) + ': ' + CONVERT(VarChar, @date, @i)        
 PRINT CONVERT(VarChar, @i+100) + ': ' + CONVERT(VarChar, @date, @i+100)    
 SET @i = @i + 1
  END 
PRINT CONVERT(VarChar, 20) + ': ' + CONVERT(VarChar, @date, 20)        
PRINT CONVERT(VarChar, 120) + ': ' + CONVERT(VarChar, @date, 120)    
PRINT CONVERT(VarChar, 21) + ': ' + CONVERT(VarChar, @date, 21)        
PRINT CONVERT(VarChar, 121) + ': ' + CONVERT(VarChar, @date, 121)    
PRINT CONVERT(VarChar, 126) + ': ' + CONVERT(VarChar, @date, 126)    
PRINT CONVERT(VarChar, 127) + ': ' + CONVERT(VarChar, @date, 127)    
PRINT CONVERT(VarChar, 130) + ': ' + CONVERT(VarChar, @date, 130)    
PRINT CONVERT(VarChar, 131) + ': ' + CONVERT(VarChar, @date, 131)    

Query to create a Table of dates between date range specified

Use below query to create a table of date for given date range:

DECLARE @FromDt AS DateTime = '2013-09-01', @ToDt AS DateTime = '2013-12-31'

CREATE TABLE #TempDtTable(VchDt VARCHAR(10));
WHILE @FromDt <= @ToDt
BEGIN
INSERT INTO #TempDtTable VALUES (CONVERT(VARCHAR(10), @FromDt , 121))
SET @FromDt = DATEADD(DAY, 1, @FromDt)
END
SELECT * FROM #TempDtTable
DROP TABLE #TempDtTable

Restart Computer Failure While uninstalling SQL Server 2008

You may get the below error while uninstalling SQL server: 

A computer restart is required. You must restart this computer before installing SQL Server.

So, now you restart the system. and try again but you got the same error. Now it seems that there are some bad registry entries: Now you had to to do the following

Leave the Setup Support Rules UI open if you are already in there
Open Regedit (Registry Editor)

Go to the following registry key "HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager"

Delete the "PendingFileRenameOperations"

Click “Re-Run” in Setup Support Rules launch the uninstall again

So now you will have passed the restart computer check and you can continue and get rid of that pesky old version of SQL Server.

Msg 1934, Level 16, State 1, Line 2

Today I was running below query to rebuild all indexes:

EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'

But it failed and shown below error:

Msg 1934, Level 16, State 1, Line 2
ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

I searched for this on net and got some below solution:

EXEC sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'

It means you need the SET QUOTED_IDENTIFIER ON in the sp_msForEachTable as well, because sp_msForEachTable does not have the right setting.