A transport-level error has occurred when sending the request to the server

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


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

Generally this error occurs when connection to SQL server is forcible closed due to some reasons. Some possible reasons are:

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

To solve this error reconnect the SQL Server

Error: 7139, Severity: 16, State: 1

Today I was getting error while uploading documents to the table. Whenever I was uploading documents with size greater than 64 KB, I got the below error:

Server: Msg 7139, Level 16, State 1, Line 1
Length of text, ntext, or image data (200) to be replicated exceeds configured maximum 10.
The statement has been terminated.

So I got some clue that there might be some settings related to size of the documents. Finally I squared replication configuration and found that one of the server level replication setting is set to small value. max text repl size is the server level setting set to a small value of 64 KB. Then I increased this value to 1073741824 (1 GB) and it started working fine. The query I ran is below:

EXEC SP_CONFIGURE 'max text repl size', 1073741824
GO
RECONFIGURE
GO

SET NOCOUNT ON

SET NOCOUNT ON gives a performance boost to action queries by suppressing the "(n row(s) affected) message that results from running a query.

The performance boost is due to the few bytes of information that make up the "(1 row(s) affected)" message not being transmitted to the client application.

Communication between the database and the client application on a stand-alone machine will be as fast as it is possible to get.

For queries that retrieve data the performance boost will be less simply because the size of the "(1 row(s) affected)" message is small compared to the volume of data being returned.

In .NET applications an ExecuteNonQuery command returns the number of records affected by the operation. Set NOCOUNT ON means that the value that this call returns is always zero.

Script to remove Tab, Line Feed and Carriage Return from a string

The below TSQL script removes the Tab, Line feed, Carriage Return characters in a string:


SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = 'ARUN    KUMAR 
LADHA'

PRINT @String

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),'')
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),'')
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@String, CHAR(9),'')
PRINT @CorrectedString

Convert Delimited String to Table

Sometime we may need to convert delimited string to table. From below function we can do this conversion:


CREATE FUNCTION Func_String_To_Table
(@strTemp VarChar(1000))
Returns @Table Table (VAL int)
AS
BEGIN
    SET @strTemp = @strTemp + ','
    DECLARE @curr_char VarChar(1)
    DECLARE @char VarChar(6)
    DECLARE @len int
    DECLARE @num int
    DECLARE @counter int
    SET @char = ''
    SET @curr_char = ''
    SET @counter = 1
    SET @len = len(@strTemp) 

    WHILE @counter <= @len 
    BEGIN
        SET @curr_char = substring(@strTemp, @counter, 1)
        IF (@curr_char <> ',')
        BEGIN
            SET @char = @char + @curr_char         
        END
        ELSE
        BEGIN  
            SET @num = convert(int, @char)         
            INSERT INTO @Table VALUES(@num)
            SET @char = ''
        END
       SET @counter = @counter + 1
    END
    RETURN
END