QUOTED_IDENTIFIER & ANSI_NULLS

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
I'm affraid you cannot. What is your concern?
"Ted Theo" <tedtheo@.gmail.com> wrote in message
news:4b68c64c-e5ab-4024-878b-dbeec155abee@.d21g2000prf.googlegroups.com...
> 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 (tedtheo@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Dec 2, 5:58 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Ted Theo (tedt...@.gmail.com) writes:
> 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
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||>> 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?
|||Ted Theo (tedtheo@.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.
The best I can suggest is that you file an suggestion to add such an
option on https://connect.microsoft.com/SQLServer/feedback/. If you do,
please post the URL. I may vote for it. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:29789bc2-91b0-4f7a-a593-547d76942965@.b15g2000hsa.googlegroups.com...
> 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.
Just FYI
sql

QUOTED_IDENTIFIER & ANSI_NULLS

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
I'm affraid you cannot. What is your concern?
"Ted Theo" <tedtheo@.gmail.com> wrote in message
news:4b68c64c-e5ab-4024-878b-dbeec155abee@.d21g2000prf.googlegroups.com...
> 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 (tedtheo@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Dec 2, 5:58 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Ted Theo (tedt...@.gmail.com) writes:
> 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
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||>> 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?
|||Ted Theo (tedtheo@.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.
The best I can suggest is that you file an suggestion to add such an
option on https://connect.microsoft.com/SQLServer/feedback/. If you do,
please post the URL. I may vote for it. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:29789bc2-91b0-4f7a-a593-547d76942965@.b15g2000hsa.googlegroups.com...
> 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.
Just FYI

QUOTED_IDENTIFIER & ANSI_NULLS

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 (tedtheo@.gmail.com) writes:

Quote:

Originally Posted by

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.

I repeat the answer I posted in another newsgroup in reponse to the same
question:

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

quote in csv

I'm using the reporting services web service to export reports to csv files.
When I export to a csv, a quote mark is appended at the beginning and end of
each line. I'm using the default device info settings. This is not the
Qualifier, because I tried changing that from ["] to other characters in the
devinfo settings, and the ["] is still inserted.
How can I prevent the ["] from being inserted? It causes problems when I
open the file in Excel, because Excel thinks it means the whole row is one
field, and it gets truncated after 256 characters.
Thanks!
BillTwo different solutions depending on RS 2000 or RS 2005. Note, either you
need to be concerned with a merged cells problem when exporting CSV.
Solution 1:
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Solution 2:
Do the above design change to avoid the merged cell problem with sorting in
Excel. Then modify rsreportserver.config. Reboot after the change. The below
shows commenting out the existing entry and putting in the needed change to
have CSV export as ASCII
. <!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>|||I just realized that the quotes are being added when I save the csv, and
aren't there when it is generated by the reporting service.
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>