Error: 64, connection was successfully established with the server, but then an error occurred during the pre-login handshake.

I had got below error in my friend's system:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

I googled this error and got mixed results and solutions but none solved my problem.

After some more googling I found below solution that worked for me.

The solution is:

Go to the MSSQL Configuration Manager and expand the SQL Network Configuration and click on the PROTOCOLS node. Right click on TCP/IP and open up the PROPERTIES panel and Select the IP ADDRESS tab.

Check the IP ADDRESS field's value are correct and match the system it is running on.

Restart the service, and the problem is solved.

Make sure you fill in the TCP PORT, even if you are using the default 1433.

Error occurred during database creation

Sometime when you create a new database by using CREATE Database statement in SSMS (SQL Server Management Studio), you may got below error message and you are not able to create Database:

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '' may be incorrect.

Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors. 

The main reason of above error is path of folder store in the registry values does not exist. When the path of folder is incorrect then MS SQL server throws above error messages.

You can fix the error by using SSMS. SSMS is able to set the default directory value for Data and log Files. Follow below steps to change the default value: 

• Start SQL server Management Studio.
• In Management Studio, right click on your instance and select properties
• In properties, click on the database setting option.
• Now go to the new database default location section
• Correct folder path from default directory box and default log directory box
• Press OK
• Stop the instance of MS SQL database
• Restart the instance of MS SQL server database.

List database objects modified in the last ‘X’ days

Use below query to get the list of databases modified in last 'X' days:

USE <database_Name>; 
GO 
SELECT Name AS object_Name,  
  Create_Date, Modify_Date, 
  Type_Desc,
  SCHEMA_Name(Schema_ID) AS Schema_Name 
FROM sys.Objects 
WHERE Modify_Date > GETDate() - <X_Days> 
ORDER BY Modify_Date; 
GO 

Notes: Replace <database_Name> with Database Name and <X_Days>  with No of days.

Weekend count between two dates

Use below query to find the weekend count between two dates:

DECLARE @DateFrom DateTime, @DateTo DateTime, @Total int, @Number int, @Counter int
SELECT @DateTo = GetDate(), @DateFrom = GetDate() - 22, @Total = DateDiff(dd, @DateFrom, @DateTo), @Number = 1, @Counter = 0
WHILE (@Number <= @Total)
BEGIN
IF DatePart(dw, @DateFrom) = 1 OR DatePart(dw, @DateFrom) = 7
BEGIN
SET @Counter = @Counter +1
END
SET @DateFrom = @DateFrom+1
SET @Number = @Number + 1
END
PRINT @Counter

Find LOB Columns Script

Sometime you want to identify the tables which could not reindex online. For this you had to scan all tables in SQL Server database and list the columns which are large objects (VarChar(MAX), NVarChar(MAX), XML, VarBinary, Text, NText, Image).

There are various method to get above information:

Method 1

SELECT * FROM Information_Schema.Columns

WHERE Table_Name IN 

    (SELECT Table_Name 

     FROM Information_Schema.Tables 

     WHERE Table_Type = 'Base Table')

AND DATA_TYPE IN ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')

AND Character_Maximum_Length = -1

ORDER BY Table_Name

Method 2

SELECT 

    C.Object_ID, 

    Object_Name(C.Object_ID) AS [Object Name], 

    C.Name AS [Column Name], 

    T.Name AS [Column Type]

FROM Sys.Columns C

INNER JOIN Sys.Types T ON C.System_Type_ID = T.System_Type_ID 

WHERE C.Object_ID IN (SELECT Object_ID FROM Sys.Objects WHERE Type_Desc = 'User_Table')

AND C.max_length = -1

AND T.Name IN ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')