does anyone know how to keep QA from adding the lines setting these two options on and off along with blank lines at the beginning and end of every object you edit? i have searched quite a bit on this but haven't been able to come up with anything.
> does anyone know how to keep QA from adding the lines setting these > two options on and off along with blank lines at the beginning and end > of every object you edit? i have searched quite a bit on this but > haven't been able to come up with anything.
Ted Theo (tedt...@gmail.com) writes: > does anyone know how to keep QA from adding the lines setting these > two options on and off along with blank lines at the beginning and end > of every object you edit? i have searched quite a bit on this but > haven't been able to come up with anything.
There does not seem to be an option for this.
The reason they are there, is that these to set options are saved with the procedure. I can understand that it is a bit of a nuisance. But since Enterprise Manager incorrectly has these two off by default, it's probably a good thing that QA includes them with the right setting. (But it's not good that there is a SET OFF for one of them at the end.)
Personally, I don't find this a hassle, since I keep my code under source control, and rarely have reason to script it from the database.
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
On Dec 2, 5:58 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Ted Theo (tedt...@gmail.com) writes: > > does anyone know how to keep QA from adding the lines setting these > > two options on and off along with blank lines at the beginning and end > > of every object you edit? i have searched quite a bit on this but > > haven't been able to come up with anything.
> There does not seem to be an option for this.
it's just a bit of a nuisance like you said. i have more projects that don't use source control (single dev projects) than ones that do so i encounter it frequently. i have a high level understanding of what both options accomplish and i haven't found a case where setting them at the individual object level has been advantageous. maybe i'm just missing that part.
it seems sql server management studio just turns these settings on when scripting an object and doesn't turn them off. is there a way to turn this behavior off in mgmt studio? is there a reason i wouldn't want to do this?
> The reason they are there, is that these to set options are saved with > the procedure. I can understand that it is a bit of a nuisance. But since > Enterprise Manager incorrectly has these two off by default, it's > probably a good thing that QA includes them with the right setting. (But > it's not good that there is a SET OFF for one of them at the end.)
> Personally, I don't find this a hassle, since I keep my code under source > control, and rarely have reason to script it from the database.
> -- > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>> is there a reason I wouldn't want to do this? <<
Conformance to ANSI/ISO Standards should be a goal in any shop, so you would not turn off options that bring you to that goal. Why would you want to write your own database language?
On Dec 2, 11:10 am, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> is there a reason I wouldn't want to do this? <<
> Conformance to ANSI/ISO Standards should be a goal in any shop,
Unfortunately, currently the goal in most places is making money, and unfortunately adherence to suboptimal standards may prevent businesses from making money. How about improving the standard first?
Ted Theo (tedt...@gmail.com) writes: > it's just a bit of a nuisance like you said. i have more projects > that don't use source control (single dev projects) than ones that do > so i encounter it frequently. i have a high level understanding of > what both options accomplish and i haven't found a case where setting > them at the individual object level has been advantageous. maybe i'm > just missing that part.
Like it or not, the settings of the two *are* saved with each procedure. This is in difference from, say, ANSI_WARNINGS, where the run-time setting of the two apply.
As for which of the two settings to use, keep in mind that there are features in SQL Server that are not available if any of ANSI_NULLS or QUOTED_IDENTIFIER are off:
o Indexed views and index on computed columns. o Xquery. o Queries involving linked servers (ANSI_NULLS only).
Of course, if you use default settings etc, there should never be any reason to include these in the script, because it should be a rare exception that you deliberately would create a procedure with any of them off. (The only half-good reason I can think of is that you work with dynamic SQL in several layers and nesting quotes is driving you crazy. Turning off QUOTED_IDENTIFIERS permits you to use " as a string delimiter as well to save your sanity.)
> it seems sql server management studio just turns these settings on > when scripting an object and doesn't turn them off. is there a way to > turn this behavior off in mgmt studio? is there a reason i wouldn't > want to do this?
The fact that SSMS do not set them OFF, is probably my fault. I bitched about that during the beta of SQL 2005.
No, neither SSMS appears to have an option for this, just like QA there is only an option for controlling whether ANSI_PADDING should be scripted tables.
>>> is there a reason I wouldn't want to do this? <<
> Conformance to ANSI/ISO Standards should be a goal in any shop, so you > would not turn off options that bring you to that goal. Why would you > want to write your own database language?
The goal here is helping people with their queries, and NOT telling them they should be a "BY THE BOOK" kind of STIFF like yourself.
|
