Msg 10054, Level 20, State 0, Line 0

Error Message:

.Net SqlClient Data Provider: Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

This error Occurs when the connection is forcibly closed by SQL Server.

Possible Reasons:
– When the connection was IDLE for a long time
– SQL Server Service is restarted / Stopped
– The database to which the connection is connected to is restored
– KILL Command is issued by any other user.

Resolution/fix:
Try to reconnect to the server

Msg 141, Level 15, State 1, Line 3 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Error Message:

Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Sample Query:
[sql]
DECLARE @I INT
SELECT @I = ContactID,* FROM Person.Contact WHERE LastName = ‘Achong’
[/sql]

Resolution:
This error occurs when Column names and variable assignements occur a single select statement.
Move variable assignments and data-retrieval operations into seperate select statements
[sql]
DECLARE @I INT
SELECT @I = ContactID FROM Person.Contact WHERE LastName = ‘Achong’
SELECT * FROM Person.Contact WHERE LastName = ‘Achong’
[/sql]

SQL Server Misconception – NVARCHAR(MAX) Cannot Store More Than 4000 Characters

In this post Lets discuss about the SQL Server Misconception – NVARCHAR(MAX) Cannot Store More Than 4000 Characters.

Lets analyse this first
Take the below query.
[sql]
DECLARE @String NVARCHAR(MAX)
DECLARE @i INT
SELECT @i = 5000,@String=”

WHILE @i>0
BEGIN
SELECT @String = @String + ‘A’
SET @i = @i – 1
END

PRINT @String
[/sql]

When you run this query you will get the following output.
Output:

Print Output

Find the length of the output:
When you move the cursor to the end of the output in the Messages window you can notice that the column number (as shown in above image) to be 4001(So length is 4000 characters).

This leads to the misconception
– NVARCHAR(MAX) Cannot Store More Than 8000 Characters

Actuality:
– Print statement has a limit of 4000 characters (unicode) hence the output is truncated.
– The nvarchar(max) statement can store up to 2gb of data(2^31-1 bytes).

Take the below SQL Code
[sql]
DECLARE @String NVARCHAR(MAX)
DECLARE @i INT
SELECT @i = 5000,@String=”

WHILE @i>0
BEGIN
SELECT @String = @String + ‘A’
SET @i = @i – 1
END

SELECT LEN (@String) AS Length
[/sql]

Output:
Length
——————–
5000


(1 row(s) affected)

LEN statement returns 5000 and it proves that nvarchar(max) can store more than 4000 characters.

SQL Server Misconception – VARCHAR(MAX) Cannot Store More Than 8000 Characters

This post deals with one of the SQL Server Misconception – VARCHAR(MAX) Cannot Store More Than 8000 Characters.

Lets analyse this first
Take the below query.
[sql]
DECLARE @String VARCHAR(MAX)
DECLARE @i INT
SELECT @i = 10000,@String=”

WHILE @i>0
BEGIN
SELECT @String = @String + ‘A’
SET @i = @i – 1
END

PRINT @String
[/sql]

When you run this query you will get the following output.
Output:

Print Output Length

Find the length of the output:
When you move the cursor to the end of the output in the Messages window you can notice that the column number (as shown in above image) to be 8001(So length is 8000 characters).

This leads to the misconception
– VARCHAR(MAX) Cannot Store More Than 8000 Characters

Actuality:
– Print statement has a limit of 8000 characters(non-unicode) hence the output is truncated.
– The varchar(max) statement can store up to 2gb of data(2^31-1 bytes).

Take the below SQL Code
[sql]
DECLARE @String nVARCHAR(MAX)
DECLARE @i INT
SELECT @i = 10000,@String=”

WHILE @i>0
BEGIN
SELECT @String = @String + ‘A’
SET @i = @i – 1
END

SELECT LEN(@String) as Length
[/sql]

Output:
Length
——————–
10000


(1 row(s) affected)

LEN statement returns 10000 and it proves that varchar(max) can store more than 8000 characters.

sql server 2008 filtered index

Filtered index is an index that is filtered.
Filtered index contains a where clause, so that the index is only created on the set of rows filtered by the where clause.

Example:
[sql]
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX VendorURLNotNull
ON Purchasing.Vendor (AccountNumber,Name)
WHERE PurchasingWebServiceURL IS NOT NULL ;
[/sql]

Msg 15122 Level 16 State 1 Line 1 The CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF.

Error Message:

Msg 15122, Level 16, State 1, Line 1
The CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF.

Fix/Resolution:
Turn On CHECK_POLICY in the script or remove CHECK_EXPIRATION from the script

Before:
[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’ , CHECK_EXPIRATION=ON, CHECK_POLICY=off
[/sql]

After:
[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’ , CHECK_EXPIRATION=ON, CHECK_POLICY=ON
[/sql]

OR

[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’
[/sql]

Msg 15099 Level 16 State 1 Line 1 The MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.

Error Message:

Msg 15099, Level 16, State 1, Line 1
The MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.

Fix/resolution:
Add CHECK_EXPIRATION=ON TO the script or remove MUST_CHANGE from the script
Before:
[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’ MUST_CHANGE
[/sql]

After:[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’ MUST_CHANGE, CHECK_EXPIRATION=ON
[/sql]

OR

[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’ MUST_CHANGE
[/sql]

SQL Server error 15195 – The MUST_CHANGE option is not supported by this version of Microsoft Windows.

Error Message:
The MUST_CHANGE option is not supported by this version of Microsoft Windows.

Fix/Resolution:
If you are creating user using the UI then Uncheck User Must Change Password at Next Login option as schown below.

Uncheck User Must Change Password at Next Login

If you are using the script to create user then remove the word MUST_CHANGE from the syntax.
[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’ MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
[/sql]

After:
[sql]
CREATE LOGIN [username] WITH PASSWORD=N’pa@Ssword’ , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
[/sql]

sql server error 15405

Error Message:
Cannot use the special principal sa
Cannot use the special principal dbo

Fix/resolution:
Try to change the db owner.
Sample Script:
[sql]
exec sp_changedbowner ‘sa’,’true’
[/sql]