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')

Unable to modify table.

I was trying to modify a table of approx 5 million records setting a field on the table to not allow null values. However, while saving changes in table, I got below error:

'Account ' table
- Unable to modify table. 
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

After that I click ok button and I got below error:

User cancelled out of save dialog.

This error is coming because table designer have a default timeout value of 30 secs. And as I am saving a table with huge records, it is taking time more than 30 secs, so above error coming.

So the solution is to increase the timeout value of designer, (the maximum value for timeout is 65535).
Click on tools menu - Options - Expand designers. Now here change the timeout value in "Table and Database designers" on the right side.

Record Retrieval Error Connection Read

You may receive below error while running third party application with backend SQL Server.

Record Retrieval Error
Error: (Connection Read(recv()).(01000)) attempting to access a record from the MERCAN file. Returning to previous window.

The application is showing this error because it lost the network connectivity with the SQL Server instance for a short period of time.

So, restart the application once the network issue is solved.

Attach Database Failed

Sometime you may get below error while attaching a database:

Attach database failed for Server 'SERVERNAME\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)

Unable to open the physical file "filename.mdf". Operating system error 5: "5(error not found)".(Microsoft SQL Server, Error: 5120)

To solve above error, make sure that the SQL Server Service account has modify permissions over the physical file on the hard drive that you are trying to attach to a SQL Server instance, and modify permissions also over the folder that contains the file.