|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL connection timeout -- is it pretty useless or what?I'm trying to code a routine that will test the connection to a server and I
would like to specific how long (aka timeout) the .Open() should wait before a timeout error is triggered. I've tried "connection timeout=10;" in the connect string, but that still doesn't timeout til 30 seconds have passed. I can't set the Connection.ConnectionTimeout as that is a read only property. So am I out of luck? Is there no way to quickly test a SQL connection in .NET 2.0? Operative being quickly as in I can define how long to wait? Please tell me I don't have to initiate a thread for the SQL connection and have the thread stop after xx seconds. There must be a more simple approach no?? VS 2005 is supposed to be RAD (rapid application development) not WAAD (work around application development). Thanks, Rob. Can you show us the whole connection string? Perhaps there is a typo?
And, at least according to the MSDN documentation, the default is 15 seconds, not 30. Show quote "Rob R. Ainscough" <roba***@pacbell.net> wrote in message news:Of95s0foGHA.2464@TK2MSFTNGP03.phx.gbl... > I'm trying to code a routine that will test the connection to a server and > I would like to specific how long (aka timeout) the .Open() should wait > before a timeout error is triggered. > > I've tried "connection timeout=10;" in the connect string, but that still > doesn't timeout til 30 seconds have passed. I can't set the > Connection.ConnectionTimeout as that is a read only property. So am I out > of luck? Is there no way to quickly test a SQL connection in .NET 2.0? > Operative being quickly as in I can define how long to wait? > > Please tell me I don't have to initiate a thread for the SQL connection > and have the thread stop after xx seconds. There must be a more simple > approach no?? VS 2005 is supposed to be RAD (rapid application > development) not WAAD (work around application development). > > Thanks, Rob. > > Dim Connection As New SqlConnection("Server=" & ServerName & ";" _
& "uid=" & UserID & ";" _ & "pwd=" & Password & ";" _ & "database=" & DataBaseName & ";" _ & "Max Pool Size=200;" _ & "connection timeout=15;") http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout(d=ide).aspx My MSDN doc says: "Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error." "The time (in seconds) to wait for a connection to open. The default value is 15 seconds." When I execute the Connection.Open() it takes 43.9 seconds before an error is triggered. I check the Connection.ConnectionTimeOut value and it is set to 15. Show quote "Marina Levit [MVP]" <someone@nospam.com> wrote in message news:ezRc54foGHA.4240@TK2MSFTNGP05.phx.gbl... > Can you show us the whole connection string? Perhaps there is a typo? > > And, at least according to the MSDN documentation, the default is 15 > seconds, not 30. > > "Rob R. Ainscough" <roba***@pacbell.net> wrote in message > news:Of95s0foGHA.2464@TK2MSFTNGP03.phx.gbl... >> I'm trying to code a routine that will test the connection to a server >> and I would like to specific how long (aka timeout) the .Open() should >> wait before a timeout error is triggered. >> >> I've tried "connection timeout=10;" in the connect string, but that still >> doesn't timeout til 30 seconds have passed. I can't set the >> Connection.ConnectionTimeout as that is a read only property. So am I >> out of luck? Is there no way to quickly test a SQL connection in .NET >> 2.0? Operative being quickly as in I can define how long to wait? >> >> Please tell me I don't have to initiate a thread for the SQL connection >> and have the thread stop after xx seconds. There must be a more simple >> approach no?? VS 2005 is supposed to be RAD (rapid application >> development) not WAAD (work around application development). >> >> Thanks, Rob. >> >> > > The issue is usually that the timeout counter does not start until network
connectivity has been established. If the NIC card can't see the wire, it's programmed to wait for quite some time before complaining. This is by design and due to the way the TCP protocol works--it permits you to move wires around or use a switch/router/hub without worrying that it will disturb the network. We've talked about this issue for decades--there is no easy way to resolve it. hth -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "Rob R. Ainscough" <roba***@pacbell.net> wrote in message news:Of95s0foGHA.2464@TK2MSFTNGP03.phx.gbl... > I'm trying to code a routine that will test the connection to a server and > I would like to specific how long (aka timeout) the .Open() should wait > before a timeout error is triggered. > > I've tried "connection timeout=10;" in the connect string, but that still > doesn't timeout til 30 seconds have passed. I can't set the > Connection.ConnectionTimeout as that is a read only property. So am I out > of luck? Is there no way to quickly test a SQL connection in .NET 2.0? > Operative being quickly as in I can define how long to wait? > > Please tell me I don't have to initiate a thread for the SQL connection > and have the thread stop after xx seconds. There must be a more simple > approach no?? VS 2005 is supposed to be RAD (rapid application > development) not WAAD (work around application development). > > Thanks, Rob. > > I was looking it going thru SQL instances but realized that will only work
with local networks. It seems to me that building in this timeout to permit "move wires around" is building a solution (with an large negative impact) for a problem that might happen .0001% of the time? Just about any shop I know, usually provides "down time" for this activity, so why introduce a 45 second timeout. Does that make sense to you? So what is the point of having a TimeOut property? I set it to 90 seconds and it timed out in 130 seconds, I set it to 2 seconds and it timed out in 45 seconds, I set it to 60 seconds and it timed out in 120 seconds -- just seems to be to random and very useless to me? Any way to quick test for a response (with a timeout) from an IP address and SQL listening port -- hackers seem to be able to do it so it must be pretty easy. Rob. Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:eM2c9lgoGHA.4996@TK2MSFTNGP05.phx.gbl... > The issue is usually that the timeout counter does not start until network > connectivity has been established. If the NIC card can't see the wire, > it's programmed to wait for quite some time before complaining. This is by > design and due to the way the TCP protocol works--it permits you to move > wires around or use a switch/router/hub without worrying that it will > disturb the network. We've talked about this issue for decades--there is > no easy way to resolve it. > > hth > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no > rights. > __________________________________ > > "Rob R. Ainscough" <roba***@pacbell.net> wrote in message > news:Of95s0foGHA.2464@TK2MSFTNGP03.phx.gbl... >> I'm trying to code a routine that will test the connection to a server >> and I would like to specific how long (aka timeout) the .Open() should >> wait before a timeout error is triggered. >> >> I've tried "connection timeout=10;" in the connect string, but that still >> doesn't timeout til 30 seconds have passed. I can't set the >> Connection.ConnectionTimeout as that is a read only property. So am I >> out of luck? Is there no way to quickly test a SQL connection in .NET >> 2.0? Operative being quickly as in I can define how long to wait? >> >> Please tell me I don't have to initiate a thread for the SQL connection >> and have the thread stop after xx seconds. There must be a more simple >> approach no?? VS 2005 is supposed to be RAD (rapid application >> development) not WAAD (work around application development). >> >> Thanks, Rob. >> >> > > It's more than just "moving wires around"--it's a function of the
fundamental design of the TCP protocol at the hardware layer. Yes, there are Framework APIs that can test for network availability but they too are dependent on the NIC to respond--which it won't if there is no wire attached. Connection timeout was more useful (and more necessary) in the early days when a server had far less CPU horsepower to run it and the server or network could be too busy to respond in time. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "Rob R. Ainscough" <roba***@pacbell.net> wrote in message news:u20E0xgoGHA.4152@TK2MSFTNGP04.phx.gbl... >I was looking it going thru SQL instances but realized that will only work >with local networks. > > It seems to me that building in this timeout to permit "move wires around" > is building a solution (with an large negative impact) for a problem that > might happen .0001% of the time? Just about any shop I know, usually > provides "down time" for this activity, so why introduce a 45 second > timeout. Does that make sense to you? > > So what is the point of having a TimeOut property? I set it to 90 seconds > and it timed out in 130 seconds, I set it to 2 seconds and it timed out in > 45 seconds, I set it to 60 seconds and it timed out in 120 seconds -- just > seems to be to random and very useless to me? > > Any way to quick test for a response (with a timeout) from an IP address > and SQL listening port -- hackers seem to be able to do it so it must be > pretty easy. > > Rob. > > "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message > news:eM2c9lgoGHA.4996@TK2MSFTNGP05.phx.gbl... >> The issue is usually that the timeout counter does not start until >> network connectivity has been established. If the NIC card can't see the >> wire, it's programmed to wait for quite some time before complaining. >> This is by design and due to the way the TCP protocol works--it permits >> you to move wires around or use a switch/router/hub without worrying that >> it will disturb the network. We've talked about this issue for >> decades--there is no easy way to resolve it. >> >> hth >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> INETA Speaker >> www.betav.com/blog/billva >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> >> "Rob R. Ainscough" <roba***@pacbell.net> wrote in message >> news:Of95s0foGHA.2464@TK2MSFTNGP03.phx.gbl... >>> I'm trying to code a routine that will test the connection to a server >>> and I would like to specific how long (aka timeout) the .Open() should >>> wait before a timeout error is triggered. >>> >>> I've tried "connection timeout=10;" in the connect string, but that >>> still doesn't timeout til 30 seconds have passed. I can't set the >>> Connection.ConnectionTimeout as that is a read only property. So am I >>> out of luck? Is there no way to quickly test a SQL connection in .NET >>> 2.0? Operative being quickly as in I can define how long to wait? >>> >>> Please tell me I don't have to initiate a thread for the SQL connection >>> and have the thread stop after xx seconds. There must be a more simple >>> approach no?? VS 2005 is supposed to be RAD (rapid application >>> development) not WAAD (work around application development). >>> >>> Thanks, Rob. >>> >>> >> >> > > Bill,
This is VS 2005 -- maybe in the "old days" this was an issue, but again, this is VS 2005? What is sounds like your suggesting, it that the OS DDK for network drivers is very much legacy code and there is not way to "adjust" the timeout response from a NIC. Regardless, assuming the NIC is fine (which it is) the timeout interval is still a useless setting in all but the most extreme cases -- isn't it? If I issue a open on a connection and don't receive a response within the amount of time specified, it should trigger an exception and let me deal with it, the framework API is NOT dealing with the issue correctly, if it did, timeout would work as advertised. This is just another case of more features in .NET framework 2.0 that work half ass and not reliable. Who the hell wants to write .NET applications when much of the functionality just doesn't work as explained in their own freakin' documentaiton?? Sorry Bill, I've read your books and they are good, but VS 2005 and .NET 2.0 really is the worst pile of crap I've seen from Redmond yet. If this is the best M$ can do, then they've got some SERIOUS problems. Before you say move to Linux or some other OS .... Not sure going from one user intensive OS to another user intensive OS is going to help solve anything beyond my immediate problems. The real issue: 1. Developers don't think outside their box (hence 80% of the population do use what we produce) 2. The complexity of the OS is preventing complete QA cycles (more security holes, more bugs) 3. Security should NOT be IN THE WAY and should NOT be the responsibility of the user 4. Solid, reliable, well documented dev tools are the key to success There are solutions to all three of these, yet nobody wants to spend the time, money and resources on it -- and this is exactly how one dooms ones fate to a flat 20% market share. Are the light bulbs going on? Not at Microsoft and certainly not open source Linux or any other flavor. Going with an alternative just because it is an alternative still doesn't address the three issues above. My hope is that a well funded group will see the light (and business/profit potential) and come out with an OS (and no the Mac OS is no better) that is truely for everyone. Rob. Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:OoIeT8ioGHA.4396@TK2MSFTNGP05.phx.gbl... > It's more than just "moving wires around"--it's a function of the > fundamental design of the TCP protocol at the hardware layer. Yes, there > are Framework APIs that can test for network availability but they too are > dependent on the NIC to respond--which it won't if there is no wire > attached. Connection timeout was more useful (and more necessary) in the > early days when a server had far less CPU horsepower to run it and the > server or network could be too busy to respond in time. > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no > rights. > __________________________________ > > "Rob R. Ainscough" <roba***@pacbell.net> wrote in message > news:u20E0xgoGHA.4152@TK2MSFTNGP04.phx.gbl... >>I was looking it going thru SQL instances but realized that will only work >>with local networks. >> >> It seems to me that building in this timeout to permit "move wires >> around" is building a solution (with an large negative impact) for a >> problem that might happen .0001% of the time? Just about any shop I >> know, usually provides "down time" for this activity, so why introduce a >> 45 second timeout. Does that make sense to you? >> >> So what is the point of having a TimeOut property? I set it to 90 >> seconds and it timed out in 130 seconds, I set it to 2 seconds and it >> timed out in 45 seconds, I set it to 60 seconds and it timed out in 120 >> seconds -- just seems to be to random and very useless to me? >> >> Any way to quick test for a response (with a timeout) from an IP address >> and SQL listening port -- hackers seem to be able to do it so it must be >> pretty easy. >> >> Rob. >> >> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message >> news:eM2c9lgoGHA.4996@TK2MSFTNGP05.phx.gbl... >>> The issue is usually that the timeout counter does not start until >>> network connectivity has been established. If the NIC card can't see the >>> wire, it's programmed to wait for quite some time before complaining. >>> This is by design and due to the way the TCP protocol works--it permits >>> you to move wires around or use a switch/router/hub without worrying >>> that it will disturb the network. We've talked about this issue for >>> decades--there is no easy way to resolve it. >>> >>> hth >>> >>> -- >>> ____________________________________ >>> William (Bill) Vaughn >>> Author, Mentor, Consultant >>> Microsoft MVP >>> INETA Speaker >>> www.betav.com/blog/billva >>> www.betav.com >>> Please reply only to the newsgroup so that others can benefit. >>> This posting is provided "AS IS" with no warranties, and confers no >>> rights. >>> __________________________________ >>> >>> "Rob R. Ainscough" <roba***@pacbell.net> wrote in message >>> news:Of95s0foGHA.2464@TK2MSFTNGP03.phx.gbl... >>>> I'm trying to code a routine that will test the connection to a server >>>> and I would like to specific how long (aka timeout) the .Open() should >>>> wait before a timeout error is triggered. >>>> >>>> I've tried "connection timeout=10;" in the connect string, but that >>>> still doesn't timeout til 30 seconds have passed. I can't set the >>>> Connection.ConnectionTimeout as that is a read only property. So am I >>>> out of luck? Is there no way to quickly test a SQL connection in .NET >>>> 2.0? Operative being quickly as in I can define how long to wait? >>>> >>>> Please tell me I don't have to initiate a thread for the SQL connection >>>> and have the thread stop after xx seconds. There must be a more simple >>>> approach no?? VS 2005 is supposed to be RAD (rapid application >>>> development) not WAAD (work around application development). >>>> >>>> Thanks, Rob. >>>> >>>> >>> >>> >> >> > > Ah, in some respects I agree. We've raised this issue from the earliest
Windows 1.0 days (when I was in the Developer Liaison group at MS). We raised it at MSU when OS/2 arrived and again as the first NT systems were built. I expect the problem is still out of MS's control--it's defined by the way that the Ethernet protocols work which MS did not invent or define and the protocol that everyone has to deal with. As far as an OS that everyone would like, I think that's a pipe dream. There is no universal solution and there should not be. What's right for games is not right for business clients, what's right for hosting services is not right for client applications.I think MS has a very hard job trying to please everyone and the world's governments as well. And no, it's not an ideal situation but it's what the market has adopted. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "Rob R. Ainscough" <roba***@pacbell.net> wrote in message news:O6z$2vPpGHA.4192@TK2MSFTNGP03.phx.gbl... > Bill, > > This is VS 2005 -- maybe in the "old days" this was an issue, but again, > this is VS 2005? > > What is sounds like your suggesting, it that the OS DDK for network > drivers is very much legacy code and there is not way to "adjust" the > timeout response from a NIC. Regardless, assuming the NIC is fine (which > it is) the timeout interval is still a useless setting in all but the most > extreme cases -- isn't it? If I issue a open on a connection and don't > receive a response within the amount of time specified, it should trigger > an exception and let me deal with it, the framework API is NOT dealing > with the issue correctly, if it did, timeout would work as advertised. > > This is just another case of more features in .NET framework 2.0 that work > half ass and not reliable. Who the hell wants to write .NET applications > when much of the functionality just doesn't work as explained in their own > freakin' documentaiton?? > > Sorry Bill, I've read your books and they are good, but VS 2005 and .NET > 2.0 really is the worst pile of crap I've seen from Redmond yet. If this > is the best M$ can do, then they've got some SERIOUS problems. Before you > say move to Linux or some other OS .... > > Not sure going from one user intensive OS to another user intensive OS is > going to help solve anything beyond my immediate problems. The real > issue: > > 1. Developers don't think outside their box (hence 80% of the population > do use what we produce) > 2. The complexity of the OS is preventing complete QA cycles (more > security holes, more bugs) > 3. Security should NOT be IN THE WAY and should NOT be the responsibility > of the user > 4. Solid, reliable, well documented dev tools are the key to success > > There are solutions to all three of these, yet nobody wants to spend the > time, money and resources on it -- and this is exactly how one dooms ones > fate to a flat 20% market share. Are the light bulbs going on? Not at > Microsoft and certainly not open source Linux or any other flavor. Going > with an alternative just because it is an alternative still doesn't > address the three issues above. > > My hope is that a well funded group will see the light (and > business/profit potential) and come out with an OS (and no the Mac OS is > no better) that is truely for everyone. > > Rob. > > "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message > news:OoIeT8ioGHA.4396@TK2MSFTNGP05.phx.gbl... >> It's more than just "moving wires around"--it's a function of the >> fundamental design of the TCP protocol at the hardware layer. Yes, there >> are Framework APIs that can test for network availability but they too >> are dependent on the NIC to respond--which it won't if there is no wire >> attached. Connection timeout was more useful (and more necessary) in the >> early days when a server had far less CPU horsepower to run it and the >> server or network could be too busy to respond in time. >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> INETA Speaker >> www.betav.com/blog/billva >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> >> "Rob R. Ainscough" <roba***@pacbell.net> wrote in message >> news:u20E0xgoGHA.4152@TK2MSFTNGP04.phx.gbl... >>>I was looking it going thru SQL instances but realized that will only >>>work with local networks. >>> >>> It seems to me that building in this timeout to permit "move wires >>> around" is building a solution (with an large negative impact) for a >>> problem that might happen .0001% of the time? Just about any shop I >>> know, usually provides "down time" for this activity, so why introduce a >>> 45 second timeout. Does that make sense to you? >>> >>> So what is the point of having a TimeOut property? I set it to 90 >>> seconds and it timed out in 130 seconds, I set it to 2 seconds and it >>> timed out in 45 seconds, I set it to 60 seconds and it timed out in 120 >>> seconds -- just seems to be to random and very useless to me? >>> >>> Any way to quick test for a response (with a timeout) from an IP address >>> and SQL listening port -- hackers seem to be able to do it so it must be >>> pretty easy. >>> >>> Rob. >>> >>> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message >>> news:eM2c9lgoGHA.4996@TK2MSFTNGP05.phx.gbl... >>>> The issue is usually that the timeout counter does not start until >>>> network connectivity has been established. If the NIC card can't see >>>> the wire, it's programmed to wait for quite some time before >>>> complaining. This is by design and due to the way the TCP protocol >>>> works--it permits you to move wires around or use a switch/router/hub >>>> without worrying that it will disturb the network. We've talked about >>>> this issue for decades--there is no easy way to resolve it. >>>> >>>> hth >>>> >>>> -- >>>> ____________________________________ >>>> William (Bill) Vaughn >>>> Author, Mentor, Consultant >>>> Microsoft MVP >>>> INETA Speaker >>>> www.betav.com/blog/billva >>>> www.betav.com >>>> Please reply only to the newsgroup so that others can benefit. >>>> This posting is provided "AS IS" with no warranties, and confers no >>>> rights. >>>> __________________________________ >>>> >>>> "Rob R. Ainscough" <roba***@pacbell.net> wrote in message >>>> news:Of95s0foGHA.2464@TK2MSFTNGP03.phx.gbl... >>>>> I'm trying to code a routine that will test the connection to a server >>>>> and I would like to specific how long (aka timeout) the .Open() should >>>>> wait before a timeout error is triggered. >>>>> >>>>> I've tried "connection timeout=10;" in the connect string, but that >>>>> still doesn't timeout til 30 seconds have passed. I can't set the >>>>> Connection.ConnectionTimeout as that is a read only property. So am I >>>>> out of luck? Is there no way to quickly test a SQL connection in .NET >>>>> 2.0? Operative being quickly as in I can define how long to wait? >>>>> >>>>> Please tell me I don't have to initiate a thread for the SQL >>>>> connection and have the thread stop after xx seconds. There must be a >>>>> more simple approach no?? VS 2005 is supposed to be RAD (rapid >>>>> application development) not WAAD (work around application >>>>> development). >>>>> >>>>> Thanks, Rob. >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics