SQL Server Errors

  • Hi,

    I'm using windows server 2003 r2 and having sql server 2005 .

    I created a dotnet application framework 2.0 and visual studio env 2005

    My application try to connct to server every 1 sec and retieves data from server via remote connection.

    it's working gud for 20 mins and then started sending the errors mentioned below for one or 2 secs and then again retieve the data promptly and the process continues and for every 10 mins i'm getting any one of these error and if i try to change the interval from 1 sec to 2 sec then the frequency of the error also changes from 10 mins to 20 mins once

    i tried all the surface area configuration and turning off the firewall

    nothing worked out well.the errors are ..

    1)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

    2)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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    3)Error in sql :A transport-level error has occurred when sending results to the server. (provider: TCP Provider, error: 0 - The existing connection was forcibly closed by remote host.)

    regards

  • The error is indicating that the connection cannot be made to the SQL server. This could be caused by the SQL server service being stopped, the server not responding to a connection request, network traffic conflicts, and NIC cable unplugged, a DNS error, or even just a bad packet.

    Connecting once per second will yeild this regularly - networks are not perfect and you will get back packets now and then. Since you are connecting so many times, you are bound to hit this now and then.

    Check your number of open connections. If you have any pooling on or are not cleaning up your connections correctly (very common issue) you will find that you are leaving hangng open connections on the SQL server and this may be making it progressively difficult to connect.

    Now back to what you are doing - connecting once per second is not usually a good idea. First, if you need to be constantly connected, leave your connection open in your application, don't close it and re-open it repeatedly. Connecting is one of the most resource-intesive processes in ADO.

    In addition to that, if you are repeatedly qurying a table to see if something is there or something has changed, you are really trying to act on an event that has happened in a database, but rather than waiting for the event, you are constantly checking if the event has already happened. This would be the same as putting a button on a form and rather than adding code to the button click event, putting a continuous loop in your program that checks if the button has been pressed. We don't live in that world anymore.

    For event-based actions in SQL, you have triggers. Now that you can create an asynchronous trigger that can call a web service, you can simply create a trigger that calls a CLR procedure and acts on whatever you need it to through a web service. I know that sounds daunting, but there is plenty of example code available on the web to make this happen.

  • Hi

    I agree with micheal there. Connecting every 1 sec is not a very good idea.

    What is the purpose of getting data so promptly ? I

    "Keep Trying"

  • The network error could also be the operating system's redirector getting confused, although this usually happens with respect to file shares (I'm assuming your're connecting with TCP/IP and not named pipes).

    As others have said, opening a connection, running a query, and closing a connection that rapidly isn't a good idea. With connection pooling the connection will be re-used (meaning neither side will actually go through and tear down the connection only to re-establish it a second later), but there is probably a better way. If you are polling that often, better to maintain an open connection, check the status of that connection before each query, and then execute the query. When the program closes, then close the connection. This would be akin to running a Windows service that needs to keep connectivity with a SQL Server.

    K. Brian Kelley
    @kbriankelley

  • Thank you for your reply friends

    actually I'm accepting that this is a bad idea,But i tried the samething with sql server 2005 installed in a windows Xp machine and running the same sample applicaiton i tried that applicaiton for more than 4 hours and it;s not at all returning any error .And In my application i'm using a dataset of visual studio 2005 which is mentioned as reliable technology where opening and closing the connection is not needed.So I need some suggestions regarding this to be implemented in windows server 2003 since the client environment is using windows 2003 server,

    regards

    vils

  • Well - trying to compare a local connection to one going through a firewall isn't exactly apples to apples. There are a lot more moving parts in an "over the wire" scenario. Reliable applications can't be more reliable than the network connection they're running on.

    If you're going through a firewall, i'm assuming you have some sort of tunnel set up, which will have an automatic timeout feature (meaning it's going to close connections after a certain amount of time). By hitting it that aggressively - you're probably catching it while it's trying to recycle the connection.

    Assuming that's true - either drop the frequency of trying, or drop the timeout in the tunnel (VPN or PPTP, or whatever else it might be).

    Another thing - if that firewall isn't set up just right, or if there are times when it gets really saturated, there will be some micro-outages like that. Might be worth setting your app up to expect said micro-outages: even if you do check as aggressively, set something up to let it weather through these little self-correcting issues. Remember - this is essentially the same concept that TCP is based on (can't guarantee each specific packet will reliably get there, so there's a mechanism to retransmit those that don't).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Would SQL SP2 for SQL 2005 resolve this? Anyone know?

    transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

    System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed

  • ungmax (7/2/2008)


    Would SQL SP2 for SQL 2005 resolve this? Anyone know?

    transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

    System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed

    This error message means someone/something killed your connection to the server.


    * Noel

  • thank you friends,

    actually that was a problem in the backbone network connectivity to the sql server and now working with no issues.

    and i used sqlDMO object for checking the conenctivity preriodically ,coz in my application if there is any disconnection to sql server it needs to be redirected to stay in some other page until the network is Up and sql server conenctivity is established.Now working well.

    many a thanks.

  • Just had the same thing happen to me - default timeout for remote connections is 600 seconds 10 minutes. I watched a process run for this amount of time then got the errors you got. So i just set the remote connections on SQL Server properites, connections, remot query timeout from 600 to 0 so connections not time out if in use.

    Not got any problems yet but i have to wait until a week when they run their hugh processes.

  • are you using a static or dynamic IP address for the windows 2003 server

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks everyone for the inputs.

    Im getting this error quite often from a web application.

  • ungmax

    it would be helpful for everyone including yourself if you didnt hijack someone else's thread. Best to start your own you probably get more response that way

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry,

    I have no intention of hijacking anyone thread.

    I did post a question and someone respond.

    I was just saying thanks.

    I have no idea you were that defensive.

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

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