Syntax Validation

Stewart "Arturius" Campbell (4/6/2014)
Nice, simple question, thanks.
However, always bear in mind to remove the NOEXEC before deploying to a production environment...

+1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

Never used this feature NOEXEC before, didn`t find the need to.

Interesting question, thanx 4 sharing 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

sestell1 SSChampion Points: 10230 More actions · Answer 6

Ugh, I knew I was going to get this wrong as soon as I read the answers. :rolleyes:

The only thing NOEXEC tells you is that your code compiles. It doesn't catch all syntax errors, and it certainly doesn't tell you if your code syntax is logically correct. I certainly don't think it is the 'best' way to check your code.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 7

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.

+1 They are all relatively good answers. I agree with Richard in that I wouldn't trust NOEXEC 100% of the time; it's always good to test somewhere other than production and be thorough. A staging, development, test...something; you can never have too many tests. 😀

Everything is awesome!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Carlo Romagnano (4/7/2014)
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".

I completely agree. Checking in in test, otherwise known as something other than production, is the only way to make sure. NOEXEC doesn't catch everything and the only way to be 100% sure is to run it. As a bonus, it also helps you validate and check your logic to make it's behaving properly.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

I was expecting to use an explicit transaction that will roll back at the end of the code within a try...catch block. This might not be the best option all the time, but it can be safe if done properly.

And of course, use a test environment.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

OCTom SSChampion Points: 11785 More actions · Answer 10

If all you are looking for is syntax checking, intellisense can help. Syntax checking does not catch logic errors.

Of course, testing in a non-production environment is always the best way; IMO, anyway.

Tom

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 11

PChiragS

SSCarpal Tunnel

Points: 4965

April 7, 2014 at 1:30 pm

#1703932

nice question shanjan.

thanks.

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

I figured the author was looking for NOEXEC but I disagree with the opinion that it is the best way.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ManicStar SSCoach Points: 17992 More actions · Answer 13

Better idea is to run the script on a staging environment that is a clone of production.

Marcia J SSCertifiable Points: 5649 More actions · Answer 14

Manic Star (4/8/2014)
Better idea is to run the script on a staging environment that is a clone of production.

Agree wholeheartedly. I would want to test more than just the syntax before running code in production.

karthik babu Hall of Fame Points: 3335 More actions · Answer 15

how is it different from Ctrl+F5 which does the same parsing as NOEXEC does?? pls correct me if am wrong.

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂