Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The 5 First SQL Errors to Check For


The 5 First SQL Errors to Check For

Author
Message
AndyOwl
AndyOwl
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 182
Comments posted to this topic are about the item The 5 First SQL Errors to Check For

Andy is a director of Wise Owl, 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.
David Hutcheson
David Hutcheson
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 100
"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.
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 2142
This is a good article, and an even better mnemonic - thanks for sharing!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
BenWard
BenWard
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 827
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
----------------------------------------
paul.goldstraw
paul.goldstraw
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 1765
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

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.)

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.
BenWard
BenWard
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 827
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 Smile

Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
David Hutcheson
David Hutcheson
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 100
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.
BenWard
BenWard
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 827
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
----------------------------------------
bjamri
bjamri
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 28
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.
BenWard
BenWard
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 827
we follow the same convention in my organisation. makes life a lot easier!

Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search