The 5 First SQL Errors to Check For

  • AndyOwl

    Mr or Mrs. 500

    Points: 578

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

  • David Hutcheson

    SSC Enthusiast

    Points: 148

    "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

    SSChampion

    Points: 10187

    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

  • BenWard

    SSCertifiable

    Points: 5903

    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

    SSCrazy

    Points: 2566

    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.

  • BenWard

    SSCertifiable

    Points: 5903

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

  • David Hutcheson

    SSC Enthusiast

    Points: 148

    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

    SSCertifiable

    Points: 5903

    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

    Grasshopper

    Points: 17

    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

    SSCertifiable

    Points: 5903

    we follow the same convention in my organisation. makes life a lot easier!

    Ben

    ^ Thats me!

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

  • M&M

    SSC-Insane

    Points: 21697

    Thanks for sharing the mnemonic, I had never heard about it before.

    M&M

  • Michael.Beeby

    Ten Centuries

    Points: 1024

    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.

  • RobCarter

    SSC Enthusiast

    Points: 176

    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.

  • rgtft

    Hall of Fame

    Points: 3517

    Good article -- I've made everyone of those mistakes many times. 🙂

    I also agree that a missing parens is another common typo/error.

  • GSquared

    SSC Guru

    Points: 260824

    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 48 total)

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