Transport error encountered when SOME queries run with batch terminator. Behaviour changes depending on text of query. WEIRD!

  • This is one of the weirdest issues I've ever seen. It's so unbelievable that I actually made a video to demonstrate the problem. Briefly, here's what happens:

    I submit a query (create view) without including the SSMS batch terminator ("go"). The view is created successfully.
    I then drop the view and resubmit the exact same statement, but WITH the batch termintor as part of the selection. The query fails with a connection error.
    This is reapeatable, as you will see in the video.

    Not only that, but minor changes to the query text (without any change to the query logic) can change the behaviour. For example, replacing "coalesce" with "isnull" changes the result.

    I started wondering whether the literal byte stream being parsed by SSMS and sent or not sent to the server was causing the problem. Like the packet construction is messing with network switches or something. But given that the batch terminator is NOT sent to the server, I don't see how it can be a network stack issue.

    Video link: https://www.youtube.com/watch?v=0BIXu8UZvus

    Worth noting: This behaviour is the same in SSMS 2012 and SSMS 2017.

  • Always set up a profiler trace when applications exhibit wierd behavior. That will be telling I suspect.

    Oh, did you check the SQL Server error log for anything useful?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Not all that telling unfortunately. The SQL log reported nothing. Profiler indicated a batch starting event with "select @@SPID;" and shows nothing further (when capturing batch/rpc/sp starting events, and pretty much every error or warning event)

    The issue does not happen on all instances. Right now the fact that I've found a way to make a reproducable error at all somewhat astounds me, a few of us in the office have been getting errors like this (either the same error, or semaphore timeout, or similar) over the past few weeks on various different instances, inconsistently. That's what made me start thinking "malfunctioning network hardware". But today I've finally found something that allows me to consistently generate or not generate the error based on minimal changes in a particular query.

    Even trivial changes seem to determine whether or not an error occurs. For example, if I change a column alias from "largetxt" to "t" the error no longer occurs and the view is created, even with the batch terminator included in the script selection. It's crazy.

  • That's pretty messed up. 

    Do the queries that DO complete successfully begin with the same SELECT @@SPID;?

    SSMS is a huge pile of HORRIBLY CODED ENTITY FRAMEWORK CRAP, so it is not at all surprising to run into bugs with it - which is what I think this is. Please post it up on Connect if you haven't already so they can investigate/correct.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Which SP and CU is your 2012 server at?  I ask because there were a shedload of problems before SP3 and even that didn't fix the problems they had and it might not actually be SSMS causing the problem.
    Also and just to be sure, you don't have any crazy settings on the database configuration, do you?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No crazy database settings Jeff. I have a strong suspicion that this is not an SSMS problem - although I share your dislike of EF guru 😉 - nor an instance problem. I keep wanting to go over to the network guys and say... something. But I can't figure out what to tell them. "Hey, your network isn't routing packets properly, depending on the packet non-header content". They'd be like "Hah you're a funny guy Don!".

    Having said that, one of the instances is 2012 SP1. The other is 2016, though.

  • Oh and Kevin: Yep, the execution batch is always preceeded by a select @@SPID batch. This is a separate batch. I don't think it's important, AFAIK this statement is always sent by SSMS 2017. SSMS 2012 doesn't send this statement.

  • Don Halloran - Thursday, October 26, 2017 2:57 PM

    No crazy database settings Jeff. I have a strong suspicion that this is not an SSMS problem - although I share your dislike of EF guru 😉 - nor an instance problem. I keep wanting to go over to the network guys and say... something. But I can't figure out what to tell them. "Hey, your network isn't routing packets properly, depending on the packet non-header content". They'd be like "Hah you're a funny guy Don!".

    Having said that, one of the instances is 2012 SP1. The other is 2016, though.

    You really need to get that 2012 instance up to speed for SP/CUs.  As for the 2016 instance, I don't know there.  Do they happen to live on the same set of hardware switches/routers/physical hardware?  We had a similar problem and it turned out to be a poorly made up Cat-5 connector that became "vibration sensitive".  Man, that was a bugger to find but the hardware guys did find the problem... right down to the pin on the connector.  A pair of cable dikes and a new connector fixed it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • As an update for the curious: I was able to reproduce the error from the command line (via SQLCMD) today, using the same "create view" batch. It's reported slightly differently, which surprises me:

    HResult 0x2745, Level 16, State 1
    TCP Provider: An established connection was aborted by the software in your host machine.
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure.

    So, definitely not an SSMS error as such. Drivers or network seem like the only possible remaining culprits.

  • Don Halloran - Thursday, October 26, 2017 10:21 PM

    As an update for the curious: I was able to reproduce the error from the command line (via SQLCMD) today, using the same "create view" batch. It's reported slightly differently, which surprises me:

    HResult 0x2745, Level 16, State 1
    TCP Provider: An established connection was aborted by the software in your host machine.
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure.

    So, definitely not an SSMS error as such. Drivers or network seem like the only possible remaining culprits.

    I don't see this being a network problem because it is not a) intermittent nor b) continuous. It is dependent on the TSQL, and ALWAYS works with some and NEVER works with others. Definitely not SSMS since you repo'd with SQLCMD. So driver. Which is controlled by PATCHING IIRC. If I had a nickel for every client that left their client tools installs on RTM I would be living on my own island I bought. Like Oahu. :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • If it was working, this message would be a "rolls eyes" emoticon.

  • TheSQLGuru - Thursday, October 26, 2017 1:16 PM

    SSMS is a huge pile of HORRIBLY CODED ENTITY FRAMEWORK CRAP, so it is not at all surprising to run into bugs with it - which is what I think this is. Please post it up on Connect if you haven't already so they can investigate/correct.

    This is the first I recall anybody asserting that SSMS used Entity Framework.  Do you have some inside info?  I can't prove that's not true, but I see little sign of it in looking at the SQL that SSMS uses.  Most of the queries appear (going from memory only) to be the same as I've seen for twenty years -- queries to system tables & calls to system stored procedures & even xp_ extended procedures dating back to the Sybase roots, etc. I was pleasantly surprised to an ANSI joins instead of from table1, table2, table3 where table1.column1 = table2.column2.... though those are still common
    I don't see real parameters converted to @p1, @p2, @p3, ... as is common w/ EF. sys.xp_enumerrorlogs is the only call I see using @p1.  
    Many SSMS queries use system functions like OBJECT_ID, SERVERPROPERTY, etc., & even nest them . (Does entity framework even support functions yet?)

  • Just poke around and do things with various object and actions and lots of EF code will drop out. I'm sure you will notice the nvarchar(4000) parameter declarations. 

    exec sp_executesql N'SELECT
    dtb.collation_name AS [Collation],
    dtb.name AS [DatabaseName2]
    FROM
    master.sys.databases AS dtb
    WHERE
    (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'master'

    exec sp_executesql N'SELECT
    dtb.name AS [Name]
    FROM
    master.sys.databases AS dtb
    WHERE
    (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'SSISDB'

    exec sp_executesql N'SELECT
    CAST(0 AS float) AS [Size],
    dtb.name AS [DatabaseName]
    FROM
    master.sys.databases AS dtb
    WHERE
    (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'Northwind'

    exec sp_executesql N'
        create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime)

    215 line monstrosity of code related to simply opening the "log ship a database dialog"

       drop table #tmp_db_encryption_keys
      
    ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'D',@_msparam_1=N'I',@_msparam_2=N'L',@_msparam_3=N'master'

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply