Syntax Validation

  • shanjan.sapra

    Mr or Mrs. 500

    Points: 555

    Comments posted to this topic are about the item Syntax Validation

  • This was removed by the editor as SPAM

  • Yogeshwar Phull

    Default port

    Points: 1434

    Nice question Shanjan. Thanks. +1

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Easy one.[/url]

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • free_mascot

    One Orange Chip

    Points: 27168

    Easy One! thanks.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • TjhomasH6610

    SSCommitted

    Points: 1632

    Easy one. Thanks

    [/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • happycat59

    One Orange Chip

    Points: 29195

    SET NOEXEC might find syntax errors but it doesn't find all errors. I copied a piece of code that included a "RANK... PARTITION BY [invalid column]" - I changed everything except the column I was partitioning by (which was actually a mistake on my part). "SET NOEXEC ON" is happy with this but when you actually execute the code, it will fail.

    So, as far as I am concerned, the only way to actually check the syntax of your code is to run it in a development environment which is how I answered this question.

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    happycat59 (4/7/2014)


    SET NOEXEC might find syntax errors but it doesn't find all errors. I copied a piece of code that included a "RANK... PARTITION BY [invalid column]" - I changed everything except the column I was partitioning by (which was actually a mistake on my part). "SET NOEXEC ON" is happy with this but when you actually execute the code, it will fail.

    So, as far as I am concerned, the only way to actually check the syntax of your code is to run it in a development environment which is how I answered this question.

    I get it right, but I agree with you "run it in a development environment".

    I think that the right answers are both "run it in a development environment" and "SET NOEXEC ON" (fifty/fifty).

    😀

  • kupy

    SSCommitted

    Points: 1613

    Carlo Romagnano (4/7/2014)


    happycat59 (4/7/2014)


    SET NOEXEC might find syntax errors but it doesn't find all errors. I copied a piece of code that included a "RANK... PARTITION BY [invalid column]" - I changed everything except the column I was partitioning by (which was actually a mistake on my part). "SET NOEXEC ON" is happy with this but when you actually execute the code, it will fail.

    So, as far as I am concerned, the only way to actually check the syntax of your code is to run it in a development environment which is how I answered this question.

    I get it right, but I agree with you "run it in a development environment".

    I think that the right answers are both "run it in a development environment" and "SET NOEXEC ON" (fifty/fifty).

    😀

    Dev can be different from prod, just because it's dev.

  • Richard Warr

    SSCertifiable

    Points: 6957

    Despite popular opinion (see above) there's no "easy" answer to this. NOEXEC is good but it won't find everything and there is the risk that you might miss one somewhere.

    I prefer the "test as much as you can" approach. If Dev is vastly different from Prod then that's an issue which should be addressed.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    kupy (4/7/2014)


    Carlo Romagnano (4/7/2014)


    happycat59 (4/7/2014)


    SET NOEXEC might find syntax errors but it doesn't find all errors. I copied a piece of code that included a "RANK... PARTITION BY [invalid column]" - I changed everything except the column I was partitioning by (which was actually a mistake on my part). "SET NOEXEC ON" is happy with this but when you actually execute the code, it will fail.

    So, as far as I am concerned, the only way to actually check the syntax of your code is to run it in a development environment which is how I answered this question.

    I get it right, but I agree with you "run it in a development environment".

    I think that the right answers are both "run it in a development environment" and "SET NOEXEC ON" (fifty/fifty).

    😀

    Dev can be different from prod, just because it's dev.

    Sorry, I want to say "run it in a TEST environment".

  • Nakul Vachhrajani

    SSChampion

    Points: 10221

    The surest way of validating the syntax is always running it on a test server. NOEXEC and other alternatives will not be able to catch all syntax exceptions.

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

    Follow me on
    Twitter: @sqltwins

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    Richard Warr (4/7/2014)


    Despite popular opinion (see above) there's no "easy" answer to this. NOEXEC is good but it won't find everything and there is the risk that you might miss one somewhere.

    I prefer the "test as much as you can" approach. If Dev is vastly different from Prod then that's an issue which should be addressed.

    Agreed.. this was my thinking and hence why I got it wrong.

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    Can someone explain how adding a NOEXEC statement and then having to remember to remove it again afterwards is "easier" than just pressing Ctrl-F5 in SSMS?

    In a question like this "None of the above" covers a lot of ground.

Viewing 15 posts - 1 through 15 (of 35 total)

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