June 13, 2011 at 10:05 pm
Comments posted to this topic are about the item The 5 First SQL Errors to Check For
Andy is a director of Wise Owl[/url], a UK company providing training courses (and occasional consultancy) in SQL, Reporting Services, Integration Services and Analysis Services, as well as in many other Microsoft software applications. You can see more about Wise Owl's SQL Server training courses here[/url].
June 14, 2011 at 12:33 am
"SSMS does - just occasionally - report an error where none exists."
Really? This is the fifth most common SQL Server error? After using SQL Server pretty much every day for the best part of ten years I have never seen this.
June 14, 2011 at 1:34 am
This is a good article, and an even better mnemonic - thanks for sharing!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
June 14, 2011 at 2:12 am
David Hutcheson (6/14/2011)
"SSMS does - just occasionally - report an error where none exists."Really? This is the fifth most common SQL Server error? After using SQL Server pretty much every day for the best part of ten years I have never seen this.
A transport level error has occurred......
This comes up if the server has disconnected the client's session, maybe the server/service was rebooted or someone killed the connection in activity monitor.
so yes, you do occasionally get 'random' errors that go away if you hit execute a second time. I get it regularly because I leave ssms open on my pc and my pc switched on pretty much 24/7
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 14, 2011 at 2:14 am
David Hutcheson (6/14/2011)
"SSMS does - just occasionally - report an error where none exists."Really? This is the fifth most common SQL Server error? After using SQL Server pretty much every day for the best part of ten years I have never seen this.
I know when the connection to the database from SSMS is forcibly closed, the first run of the query run will generate the following error
[font="Courier New"]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 by the remote host.)[/font]
If you run the query again the connection is re-established and the query succeeds. That is the only circumstance i can think of where this can happen, and he's right, it's not a coding error but it is still an error.
Does the error message itself not provide enough information on errors such as this though? Also double-clicking the error will often point you to the exact line where the problem is.
June 14, 2011 at 2:30 am
yea the error messages are pretty good. but when you're training the greenest of the green you can't necessarily expect them to understand what converting a char value to datetime value resulted in an out of range datetime value on line 17 means!
I attended an SQL training course and one of the candidates after a whole week still couldnt understand why you would use SQL Server and not Excel.
Another candidate failed to comprehend the difference between char and nchar.
Any capable/experienced programmer can (in my opinion) pick up an error message from any compiler/optimiser and work out from reading it what the error is likely to be related to even if they have never used that language before.
Day 1 newbies to programming etc cannot be expected to work out anything for themselves so I recon this article could be very helpful to anyone who has to train a room full of newbies all crowing that the query doesnt work.
My 2p 🙂
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 14, 2011 at 2:30 am
so yes, you do occasionally get 'random' errors that go away if you hit execute a second time. I get it regularly because I leave ssms open on my pc and my pc switched on pretty much 24/7
Is a transport error not a valid error? The article said that it reported errors where non existed. A closed connection to the database seems like a pretty valid error to me.
June 14, 2011 at 2:33 am
David Hutcheson (6/14/2011)
so yes, you do occasionally get 'random' errors that go away if you hit execute a second time. I get it regularly because I leave ssms open on my pc and my pc switched on pretty much 24/7
Is a transport error not a valid error? The article said that it reported errors where non existed. A closed connection to the database seems like a pretty valid error to me.
semantics.
its not an error with the query typed on the screen. so to all intents and purposes to a completely new person it's not something that they have done wrong.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 14, 2011 at 2:39 am
What works for me in preventing common error 1 is putting the first item right behind the keyword and moving the comma's to the beginning of the lines:
SELECT FilmName
, FilmReleaseDate
FROM
tblFilm
This applies to every section:
ORDER BY Director
, FilmReleasedate
It decreased my number of errors that are introduced on deleting lines. I find it also logical to add a column to any section including its own separating comma.
June 14, 2011 at 2:41 am
we follow the same convention in my organisation. makes life a lot easier!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 14, 2011 at 2:57 am
Thanks for sharing the mnemonic, I had never heard about it before.
M&M
June 14, 2011 at 4:13 am
You can get this where there has been a disconnection. You push F5 (or whatever) against your previously executed code, it tries, passes back an error. The next time you push F5 it will remake the connection and execute.
June 14, 2011 at 4:39 am
Surely too few or too many parentheses should be in the list somewhere, or parentheses in the worong place bracketing off an innappropriate criteria.
And I agree that SQL has only ever worked on the second time of asking after a transport layer error due to networking issues and server downtime.
June 14, 2011 at 6:02 am
Good article -- I've made everyone of those mistakes many times. 🙂
I also agree that a missing parens is another common typo/error.
June 14, 2011 at 6:27 am
David Hutcheson (6/14/2011)
"SSMS does - just occasionally - report an error where none exists."Really? This is the fifth most common SQL Server error? After using SQL Server pretty much every day for the best part of ten years I have never seen this.
Ditto. I've never seen an error yet that didn't have a definite cause. Indicating the wrong line number for the error, yeah, I see that all the time (usually parentheses or comma issues), but an error that "just goes away if you run it again"? Only in cases where text was selected and isn't now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply