locked
Select from a table with varbinary(max) column in SSMS fails with transport level error RRS feed

  • Question

  • CREATE TABLE [dbo].[ENO](
    [NR] [int] IDENTITY(1,1) NOT NULL,
    [ENT] [nchar](3) NULL,
    [PRK] [int] NULL,
    [NAS] [nvarchar](300) NOT NULL,
    [USC] [int] NULL,
    [DTC] [datetime2](0) NULL,
    [USM] [int] NULL,
    [DTM] [datetime2](0) NULL,
    [DAT] [varbinary](max) NOT NULL,
    [INA] [bit] NULL,
    [DEL] [bit] NULL,
    [ORI] [bit] NULL,
    [DLS] [bit] NULL,
    [DLF] [bit] NULL,
    [TYP] [tinyint] NULL,
     CONSTRAINT [PK_ENO] PRIMARY KEY CLUSTERED 
    (
    [NR] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


    SELECT * FROM ENO WHERE ENO.ENT='LAY'


    Msg 10054, Level 20, State 0, Line 0
    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - Eine vorhandene Verbindung wurde vom Remotehost geschlossen.)

    The error does not always come up, but just sometimes.

    It comes also when this statement is passed by an application.

    SQL Server Standard 64 Bit Version 13.0.5698.0 running on Windows Server 2012 R2 Datacenter.

    Your help is greatly appreciated. Thanks.


    Friday, April 24, 2020 9:04 AM

All replies

  • Have you looked at the errorlog to see if anything is occurring on the instance, for example there might be corruption. Please take a look at the errorlog.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, April 24, 2020 1:30 PM
    Answerer
  • Hello Sean
    Errorlog has no entries that could relate to this failure.
    Friday, April 24, 2020 2:19 PM
  • The next step would be to get a network trace and check the conversation.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, April 24, 2020 3:03 PM
    Answerer
  • if the fat field is very large, performance from either SSMS or any common data access falls off fast and hard

    look into the DataReader  GetBytes method, for example

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-binary-data

    basically, you need to setup a buffer system to load the data into a stream.

    also note this recommendation: SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

    I believe what happens with the common GetSqlBinary (or GetString) is that there is a pre-allocated max buffer (8K?). when this is exceeded, it stops, allocated a new buffer 2X larger, tries again, and then repeats until it works. So intermediate sizes of 16-32K seem ok, but 1M really sucks. 

    Basically, software does not magically solve problems unless it knows and plans for what you want to do. 


    jchang

    Saturday, April 25, 2020 12:54 AM
  • Hello Sean
    Can you give me a hint how this can be done?
    Just to remember: it is a call from MS SQL Server Management Studio to a remote MS SQL Engine.
    Saturday, April 25, 2020 12:53 PM
  • Hello jchang
    This error comes up with a query in the MS SQL Server Management Studio. So I assume MS has programmed this select the correct way?
    The error of course also shows up when reading this data in our web application.

    But what I found out now, is that is only comes up if the query runs on a remote system. No failire if we use SSMS on the same server as the engine runs.

    Saturday, April 25, 2020 1:00 PM
  • But what I found out now, is that is only comes up if the query runs on a remote system. No failire if we use SSMS on the same server as the engine runs.

    It sounds very much like a network problem. It could be a fauly component, or simply something that throttles and cuts the connection if too much data come back at tone.

    You say that the eror only comes sometimes. Is that "sometimes" also for the same data? Or could be different data?

    Try this in SSMS multiple times and replacing 10000 with different values. If you always get the error at 10000, try lowering it until you don't get the error etc.

    DECLARE @big varchar(MAX) = replicate(convert(varchar(MAX), newid()), 10000)
    SELECT @big

    In the end, you will need to find someone who knows about networking.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, April 25, 2020 1:36 PM
  • Since the error is intermittent and only occurs remotely, it could be related to network infrastructure and/or configuration (e.g. packet size, TCP chimney). Occurrences of the error might be reduced or even eliminated by returning varbinary(MAX) columns in chunks like jchang is suggested but that would only mask the underlying problem. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, April 25, 2020 1:38 PM
  • It does not depend on the data, sometimes it reads 100 records sometimes 300, sometimes 3000 out of the same table...
    Sunday, April 26, 2020 8:27 AM
  • I agree and will ask my hosting provider to track this.

    Thanks to all contributors.

    Reto

    Sunday, April 26, 2020 8:28 AM
  • It does not depend on the data, sometimes it reads 100 records sometimes 300, sometimes 3000 out of the same table...

    I was more thinking of the number of bytes than the number of rows. If these varbinary(MAX) vary a lot in size, the number of rows is not that telling. This is something to keep in mind when you discuss this with your hosting provider.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, April 26, 2020 9:43 AM
  • Hello Erland

    Yes, of course, but I am talking about the same table with the same rows with the same content. The content is also limited to 4MB on any row. With the same order of rows the select fails after 100, 300, 1000 or whatever number of rows, always different.

    Monday, April 27, 2020 8:22 AM