none
C#, SQL Server. Provider: TCP Provider, error: 0 - No such host is known RRS feed

  • Question

  • My connection string looks like this:

    Data Source=sql88;Initial Catalog=Osm;User ID=foouser;Password=foopsw

    The code that creates connection string looks like this:

    var con = new SqlConnectionStringBuilder();
    if (string.IsNullOrEmpty(model.Username) && string.IsNullOrEmpty(model.Password))
        con.IntegratedSecurity = true;
    else
    {
        con.UserID = model.Username;
        con.Password = model.Password;
    }
    con.DataSource = model.Host ?? string.Empty;
    con.InitialCatalog = model.Database ?? string.Empty;
    return con;

    My application creates database structure at SQL Server, then inserting data. So sometimes the applcation works approximately 14 hours. The code that executes SQL at SQL Server looks like this:

    using (var cmd = con.CreateCommand())
    {
        cmd.CommandTimeout = int.MaxValue;
        cmd.CommandText = sql;
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            var test = ex.Message;
            throw;
        }       
    }

    Sometimes application works well, but sometimes application throws an error:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

    provider: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)

    I've read this page, however, in my view this is not my case because sometimes my application works well.

    Friday, December 13, 2019 9:16 AM

Answers

  • Hello,

    Rather than use one connection which it looks like you are doing, create the connection each time work needs to be done.

    Like or similar to this.

    using System;
    using System.Data.SqlClient;
    
    namespace Basics
    {
        public class Operations
        {
            private string ConnectionString = "TODO";
    
            public Operations()
            {
                // construct connection string here
                // or in another location
            }
            public void Demo(string sql)
            {
                using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand() {Connection = cn, CommandText = sql})
                    {
                        cmd.CommandTimeout = int.MaxValue;
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            var test = ex.Message;
                            throw;
                        }
                    }
                }
            }
        }
    }
    


    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

    • Marked as answer by NiceStepUp Saturday, December 14, 2019 7:31 PM
    Friday, December 13, 2019 10:24 AM
    Moderator
  • If it's sporadic exception being thrown becuase the database cannot be found on the network, then maybe it's a networking issue as the computer hosting the database is dropping off the network due to possible bad cable, bad network card, bad switch, etc. and etc.
    • Marked as answer by NiceStepUp Saturday, December 14, 2019 7:31 PM
    Friday, December 13, 2019 11:08 AM

All replies

  • Hello,

    Rather than use one connection which it looks like you are doing, create the connection each time work needs to be done.

    Like or similar to this.

    using System;
    using System.Data.SqlClient;
    
    namespace Basics
    {
        public class Operations
        {
            private string ConnectionString = "TODO";
    
            public Operations()
            {
                // construct connection string here
                // or in another location
            }
            public void Demo(string sql)
            {
                using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand() {Connection = cn, CommandText = sql})
                    {
                        cmd.CommandTimeout = int.MaxValue;
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            var test = ex.Message;
                            throw;
                        }
                    }
                }
            }
        }
    }
    


    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

    • Marked as answer by NiceStepUp Saturday, December 14, 2019 7:31 PM
    Friday, December 13, 2019 10:24 AM
    Moderator
  • If it's sporadic exception being thrown becuase the database cannot be found on the network, then maybe it's a networking issue as the computer hosting the database is dropping off the network due to possible bad cable, bad network card, bad switch, etc. and etc.
    • Marked as answer by NiceStepUp Saturday, December 14, 2019 7:31 PM
    Friday, December 13, 2019 11:08 AM