Tech Off Thread

10 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Vagueness with a SQL Exception

Back to Forum: Tech Off
  • User profile image
    qwert231

    I separated our SQL server from our web server, which is good. However, occasionally, I get a SQL Exception. How can I troubleshoot this?

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

  • User profile image
    Matthew van Eerde

    Do you have a connection limit on the server?

    Things to try:

    Run a SQL Profiler trace against the server to see what's happening when the error occurs

    Run a Performance trace against the server to see if the error coincides with high CPU utilization

  • User profile image
    Red5

    qwert231 wrote:
    I separated our SQL server from our web server, which is good. However, occasionally, I get a SQL Exception. How can I troubleshoot this?

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


    Just a thought.  Under SQL 2000 isn't there a setting to use TCP/IP or Named Pipes (or both).
    Maybe a similar setting in SQL 2005 needs adjusting.

  • User profile image
    Rossj

    qwert231 wrote:
    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


    Make sure you are not using a trusted connection (but have no permission to access it) or as mentioned previously enable TCP/IP access (you can do this in SQL Manager in 2005).

  • User profile image
    littleguru

    Try to add the name of the instance to the connection string. I had this exception also a few times (when trying to connect to SQL Express). I fixed it by adding the name of the instance (sqlexpress in my case) into the connection string:

    "user id=sa;server=waldemar\\sqlexpress;database=foo;password=sa"

    Btw. waldemar is my computer's name Wink

    This exception message is so vague and could be everything, but in my cases it always was the missing name of the instance!

  • User profile image
    qwert231

    This will happen maybe every 100-500 interactions. Then for 5 minutes, connections will be blocked. After 5 or so minutes, connections can be made again.

    TCP/IP connections ARE enabled. This works 80% of the time. But the 20% is usually during peak use.

    Connection limit is 0 (Unlimited). All we use this server for is SQL. The problem happens on several machines that try to connect.

  • User profile image
    littleguru

    Is it a network problem? Perhaps no SQL Server problem?

  • User profile image
    qwert231

    Again, if it is, how do I debug, diagnose, or resolve? I have focused more on coding than running and maintaining the SQL server. (Even though that job seems to rest on my shoulders.)

  • User profile image
    Red5

    We once had a SQL Server 2000 installation handling ASP.NET Session state.  I noticed that periodically, the database would block some connections.  In the SQL Agent Job arena, there was a job that periodically ran to clean up sessions.  I'm not sure if this was the culprit, but after we turned this box off to handle ASP.NET session, the problems disappeared.
    So, you may or may not have ASP.NET session stuff handled, but do you have any other SQL JOBS that might be running on a schedule that would interfere with your activity?

  • User profile image
    phreaks

    Use Alchemy to monitor the network connectivity to Sql Server.
    http://www.mishelpers.com/network_monitor/index.html

    And try this?
    Here's a quick summary of the actions you need to take. The first three actions have a separate page which provides more details and some screen shots of the procedure.

    1. [Link] Enable the TCP/IP protocol using the Surface Area Configuration Utility
    2. [Link] Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility
    3. [Link] Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address - but this is non-standard for named instances. See sqlexpress's WebLog for details.
    4. Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.
    5. Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports (thanks P.C.) that starting and stopping the SQL Server and Browser software is not enough.

    http://www.datamasker.com/SSE2005_NetworkCfg.htm

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.