Web Development Blog

Network-related or instance-specific error occurred while establishing a connection to SQL Server

I have a website getting occasional database disconnections throwing the error - A network-related or instance-specific error occurred while establishing a connection to SQL Server. Its been difficult locating the cause of the error as there appears to be nothing wrong. I have the same code running multiple websites and only one of the sites is plagued by the error so I figure it must be some configuration problem either with IIS or the MS SQL database.

The Error

QueryString: aspxerrorpath=%2fshop%2fflightcell-pro-portable-voice-audio.aspx
Server Name: www.flightcell.net
Type: System.Data.SqlClient.SqlException
Message: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source: .Net SqlClient Data Provider
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.Impl.SessionFactoryImpl.OpenConnection()

Proposed Solution

I have found a point of difference, database was set to auto shrink itself after a certain about of time I believe this very briefly disconnects any clients i.e. the website. The website uses connection pooling which increases performance by persisting the connection between requests. So what I think happens is the database shrinks and disconnects its connection pool and client thinking the pool is alive is sending queries which error causing other errors. If I wasn't using connection pooling a new connection would be created and you wouldn't notice.

I have changed the setting and need to wait to see if the error occurs again.


DotNetPanel goes open source

I was surprised to find that DotNetPanel hosting control panel had basically shutdown for business. I have been using DNP control panel for around 3 years, originally starting with HostingController and then Helm and finally DNP. Over all DNP seemed the most robust and easy to use, but most importantly it worked well with servers spread over different networks. That's not to say I love every part of it there is certainly parts I would change given half a chance. So now, as it turns out, I can. DotNetPanel has been released on sourceforge as an open source project called WebsitePanel.

My first question was why?

Well I still don't know, I visit the DNP website regularly particularly when updates become available, I saw no news releases to explain the move to open source. This seems an odd move for such an established company.

I can just see it now, you get home the powers off, you ring the power company only to get the receptionist who informs you, sorry were now open source, but we are providing for free the instructions to make yourself a power station, good luck.

I a bit dramatic, perhaps slightly unrealistic you may say.

Don't get me wrong I do like open source projects, but usually projects grow round a community of developers so it will be interesting to see how this project develops. This really is the first decent open source control panel for windows and I do hope it continues to have support and move forward. It certainly will make setting up windows hosting more competitive. I think its good news for everyone.




Blogger, Html scraping, JQuery and YQL

For a recent project I was working on I wanted to display some sports results on my local football club's website. The association publishes the  results weekly on its website, we also wanted to post them on our website. It would be a waste of time to type them out each week so what can we do?

First solution I thought of was HTML data scrapping the results and redisplaying them, better than using an iframe as the results were not in a format that would suit our site. It would be fairly straight forward using ASP.NET on the server but this website is a Blogger site with limited server side functionality.

Earlier I had come across Yahoo Query Language or YQL, I thought it may be possible to use YQL with jQuery on Blogger to achieve the HTML data  scrapping.

Yahoo Query Language Developer Network explains YQL or the Yahoo! Query Language is an expressive SQL-like language that lets you query, filter, and join data across Web services. Yahoo also provide a web based console that allows you build and test your YQL statements. I was pleasantly surprised at how easy it was to use. There is plenty of documentation and examples to get your on track.



Using YQL you can retrieve a HTML page from a website extract a section of the HTML using an XPath query and supply the result as either JSON or XML. I started with one of the examples in the documentation.

select * from html where url="http://www.example.com/results.html" and xpath="//table[@class=\'results\']"

I selected the JSON format as I wanted to use the resulting data with jQuery and write out the bits I wanted. In the YQL console you can test and view the results of your query. From the console you can copy the YQL url, I have replaced the domain in the example.

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20html%20where%20url%3D%22http%3A%2F%2Fwww.example.com%2Fresults.html%22%20and%20xpath%3D%22%2F%2Ftable%5B%40class%3D%5C'results%5C'%5D%22&format=json&callback=?

The url returns a page containing JSON that represents the table of results  was selecting in my query. I can use jQuery to obtain the JSON and write to my page.

<div class="results"
</div> 
<script type="text/javascript"
function renderResults(data){ 
    var $table=$("<table width='80%'/>"); 
    var table; 
    if(data.query.results.table.length){ 
        table=data.query.results.table[0]; 
    } 
    else if(data.query.results.table){ 
        table=data.query.results.table; 
    } 
    else{ 
        return; 
    } 
     
    for (i=0;i<table.tr.length;i++) 
    { 
        var tr=table.tr[i]; 
        var $tr=$("<tr class='ResultRow'/>"); 
        if(tr.th){ 
            for (j=0;j<tr.th.length;j++){ 
                if(tr.th[j].p=="TEAM"){ 
                $tr.append("<th class='ResultCell' width='60%'>"+tr.th[j].p+"</th>"); 
                } 
                else{ 
                $tr.append("<th class='ResultCell'>"+tr.th[j].p+"</th>"); 
                } 
            } 
        } 
        else{ 
            for (j=0;j<tr.td.length;j++){ 
                if(tr.td[j].p){ 
                    $tr.append("<td class='ResultCell'>"+tr.td[j].p+"</td>"); 
                } 
                else if(tr.td[j].a){ 
                    $tr.append("<td class='ResultCell'>"+tr.td[j].a.content+"</td>"); 
                } 
                else{ 
                    $tr.append("<td class='ResultCell'/>") 
                } 
            } 
        } 
        $tr.appendTo($table); 
    } 
    $table.appendTo('.results'); 
var url = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20html%20where%20url%3D%22http%3A%2F%2Fwww.example.com%2Fresults.html%22%20and%20xpath%3D%22%2F%2Ftable%5B%40class%3D%5C'results%5C'%5D%22&format=json&callback=?"
$.getJSON(url,renderResults); 
</script> 

The result produces a nice table of results which is up to date without any additional typing. This example is really only the tip of the ice berg as they say, I look forward to finding out about some of the more advance functionality.