none
Transport Level Error Occurred RRS feed

  • Question

  • Hi All,

    I am working on a sql project and I keep getting a transport level error 19: Physical Connection is not usable. I looked around and tried a few different solutions and I have not been able to figure out what is causing this issue. The connection makes over 132,000 queries from the connection and is open for a longer period of time. I have tried this on smaller sections of input but when it does the full upload, it tends to have issues. Below is a section of the code that has the issue.

    Full Code: https://pastebin.com/XcshST6j

    Please help with any ideas on what I can do to fix the issue. I can upload the full function if needed.


    • Edited by NHastings25 Friday, November 15, 2019 3:18 PM Added Pastebin
    Thursday, November 14, 2019 6:25 PM

Answers

  • I'm going to try to simplify your code but I have no easy way of testing it. I've deduced the following from your code.

    public class FieldCollection
    { 
        public string Name { get; set; }
    
        public List<Field> Fields { get; } = new List<Field>();
    }
    
    public class Field
    { 
        public string Name { get; set; }
    
        public string Value { get; set; }
    }
    
    public class SearchField
    {
        public string TableName { get; set; }
    
        public string FieldName { get; set; }        
    }

    As far as your error goes, just a cursory glance leads me to believe you have a resource lifetime issue. I notice you are using `Close` instead of `Using` which means if anything fails you leak connections. However I'm going to assume that your code isn't erroring out. However at the end of the loop you do this `AddNewRow.BeginExecuteNonQuery`. This is an async call so your code will start doing the next iteration of the foreach before that call completes. That is a sign something is incorrect to me. Just use `ExecuteNonQuery`. Additionally your use of the static field `DatabaseConnection` doesn't look good to me. For now I might recommend that you correct the async call and see if your problem goes away. While you're doing that I'll see what I can do to simplify your code that might hopefully speed it up and reduce the likelihood of the error you're seeing.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by NHastings25 Friday, November 15, 2019 10:31 PM
    Friday, November 15, 2019 9:39 PM
    Moderator

All replies

  • Seems prudent if this happens to get all data in one shot then close the connection and perform local work after closing the connection.

    public void Example()
    {
        var dt = new DataTable();
    
        using (var cn = new SqlConnection(ConnectionString))
        {
            using (var cmd = new SqlCommand() {Connection = cn})
            {
                cmd.CommandText = "SELECT object_id,name FROM sys.columns GROUP BY object_id, name";
                cn.Open();
                dt.Load(cmd.ExecuteReader());
            }
        }
    
        var view = new DataView(dt);
        var distinctValues = view.ToTable(true, "object_id");
    
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, November 14, 2019 8:48 PM
    Moderator
  • Hi NHastings25, 

    Thank you for posting here.

    According to your description, I make a simple test based on your code, but I get no exception.

    Could you provide more details about your exception with which line of the code thrown the exception? It will help us to analyze your problem.

    Besides, I have found two related reference about the exception ‘physical connection is not usable’.

    1. Cannot get rid of “physical connection is not usable” exception
    2. Physical connection is not usable

    Hope them can help you, and we are waiting for your update.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 15, 2019 6:00 AM
    Moderator
  • So you're opening a connection to your DB and then you're clearing all the pooled connections? How is that going to work? Why are you doing this? Do you understand how ADO.NET even works in this regard? Clearing the pool would have no useful benefit to your code and (most likely) is causing issues. Open the connection, run your command, get the results, run the next command, get the results, rinse and repeat. You don't need to do anything with the connection once it is open. 

    //Using a shared object like a field for a connection is almost always the wrong approach - it causes problems, always make them local
    using (var conn = CreateConnection())
    {
       //Reuse the command so 
       //Let's avoid a SQL injection attack by not string concat a query
       var cmd = new SqlCommand("SELECT name FROM...WHERE object_id = OBJECT_ID(@field)", conn);
       var parmField = cmd.Parameters.AddWithValue("@field", "");
    
       foreach (var  fieldCollection in Parameters.FieldCollections)
       {
          var columnNames = new List<String>();
          
          //If I recall correctly the default is to not close the command, if it does then use CommandBehavior to change the default
          using (var reader = cmd.ExecuteReader())
          {
             while (reader.Read())
                columnNames.Add(reader.GetFieldValue<string>(0));
          }
          //Now what?
       };
    };
    If you have a lot of parameters such that you're running this command multiple times then switch from selecting a single row to all of them at once as Karen recommends. It is faster. 


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, November 15, 2019 3:08 PM
    Moderator
  • Michael,

    Ignore that line. I'm sorry I meant to remove that. One of my other engineers mentioned the idea dnd I forgot to remove it before I uploaded. I will upload the full function for people to be able to look at it in full. The full idea will be shown when I upload the main function.

    Nate

    Friday, November 15, 2019 3:14 PM
  • Xingyu,

    I will upload the full code in an edit so that everyone can take a look at it.

    Nate

    Friday, November 15, 2019 3:16 PM
  • I see your code is a victim of the "let's make a generic query" architecture where somebody thought a single method to do any DB query is the correct way to go. Is there any reason why you really need to do this? Code like this is notoriously bad performing and hard to maintain. Are you truly writing a generic "give me any table and update criteria" type query or can you use more common data access stuff like business objects or EF?

    If you are stuck with the generic, poor performing version then can you at least change the structure of the DatabaseUploadCollectionParameters type? Can you post that type as well? 

    I think you can consolidate most of this code into a sproc such that you would only need 1 command per loop but a step back would be to at least get rid of the multiple commands for the update. Not only does this negatively impact performance (probably part of your error issue) but also it is not atomic so you might have partial updates applied which may or may not be good for your situation.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, November 15, 2019 3:32 PM
    Moderator
  • Michael,

    The idea that was proposed was to create a tool that could be used with our Data warehouse within the government architecture and make it so that the database would dynamically update data no matter what data is being entered. It would add new tables, manage the columns, and dynamically update and add data based on the structure of the class variable. So we were looking into a good solution to make that available as we work to add new extraction tools from data sources to the data warehouse. We were looking at this as a solution but we are open to anything that would do that.

    Nate

    Friday, November 15, 2019 8:50 PM
  • Xingyu,

    After fully reviewing your response, I noted that the links you mentioned are ones i've already seen before and I have not seen any result of what might be an issue with this. I am still working on finding what I need to change to make this issue go away since those links didn't unfortunately make a difference.

    Thanks for the response,

    Nate

    Friday, November 15, 2019 8:53 PM
  • Please post the definition for DatabaseUploadCollectionParameters

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, November 15, 2019 9:02 PM
    Moderator
  • Here is the pastebin for it

    https://pastebin.com/JJQu8XkA

    Nate

    Friday, November 15, 2019 9:05 PM
  • I'm going to try to simplify your code but I have no easy way of testing it. I've deduced the following from your code.

    public class FieldCollection
    { 
        public string Name { get; set; }
    
        public List<Field> Fields { get; } = new List<Field>();
    }
    
    public class Field
    { 
        public string Name { get; set; }
    
        public string Value { get; set; }
    }
    
    public class SearchField
    {
        public string TableName { get; set; }
    
        public string FieldName { get; set; }        
    }

    As far as your error goes, just a cursory glance leads me to believe you have a resource lifetime issue. I notice you are using `Close` instead of `Using` which means if anything fails you leak connections. However I'm going to assume that your code isn't erroring out. However at the end of the loop you do this `AddNewRow.BeginExecuteNonQuery`. This is an async call so your code will start doing the next iteration of the foreach before that call completes. That is a sign something is incorrect to me. Just use `ExecuteNonQuery`. Additionally your use of the static field `DatabaseConnection` doesn't look good to me. For now I might recommend that you correct the async call and see if your problem goes away. While you're doing that I'll see what I can do to simplify your code that might hopefully speed it up and reduce the likelihood of the error you're seeing.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by NHastings25 Friday, November 15, 2019 10:31 PM
    Friday, November 15, 2019 9:39 PM
    Moderator
  • Michael,

    I overlooked the fact that I had a BeginExecuteNonQuery on that line. I replaced the line with the standard 'ExecuteNonQuery' like it was supposed to be and the entire issue went away. It was causing async connections to the SQL server ending up causing a max connection reached. This resolved the issue and it works without a problem now.

    Thanks for seeing what I didn't

    Nate

    Friday, November 15, 2019 10:31 PM