Quoted Identifiers wrong while scripting

Hi all,
I am scripting out my database and it is setting Quoted Identifiers to ON in
the script just before my stored procs. I have Quoted Identifiers set to
off at the database level.
Is there some Stored Proc level script I have to run to have them turned off
so the script turns out correct?
Can't find anything in help.
?From the doc
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
ANSI_NULLS settings are captured and used for subsequent invocations of that
stored procedure.
Apparently it was created with the setting ON
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O$2%23p%23CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am scripting out my database and it is setting Quoted Identifiers to ON
in
> the script just before my stored procs. I have Quoted Identifiers set to
> off at the database level.
> Is there some Stored Proc level script I have to run to have them turned
off
> so the script turns out correct?
> Can't find anything in help.
> ?
>|||Grab the code from the proc, drop it, and re-create it manually adding SET
QUOTED_IDENTIFIER OFF to the beginning. Then when you script it, it should
be correct.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O$2#p#CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am scripting out my database and it is setting Quoted Identifiers to ON
in
> the script just before my stored procs. I have Quoted Identifiers set to
> off at the database level.
> Is there some Stored Proc level script I have to run to have them turned
off
> so the script turns out correct?
> Can't find anything in help.
> ?
>|||Thanks,
Unfortunately, my stored procs were created two years ago, I routinely
script them out for use in an MSDE database and they seemed to have changed
somehow to use that setting and so I have no clue how they were recreated
with a setting that was never on. I just tried to recreate then to no avail
either.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uBpibIDuDHA.2132@.TK2MSFTNGP10.phx.gbl...
> From the doc
> When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
> ANSI_NULLS settings are captured and used for subsequent invocations of
that
> stored procedure.
> Apparently it was created with the setting ON
> "A" <agarrettbNOSPAM@.hotmail.com> wrote in message
> news:O$2%23p%23CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > I am scripting out my database and it is setting Quoted Identifiers to
ON
> in
> > the script just before my stored procs. I have Quoted Identifiers set
to
> > off at the database level.
> >
> > Is there some Stored Proc level script I have to run to have them turned
> off
> > so the script turns out correct?
> >
> > Can't find anything in help.
> >
> > ?
> >
> >
>

Quotation marks

Hi
Can someone tell me what i am doing wrong below:
--
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe'+"'"+'s')
select * from Tb1
Drop table Tb1
--
I would like the 2nd column to appear as Joe's in the resultset.
Thank you in advanceTry this:
INSERT INTO Tb1 VALUES('7', 'Joe''s';
HTH
Vern
"MittyKom" wrote:

> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Oops, forgot the closing parenthesis:
INSERT INTO Tb1 VALUES('7', 'Joe''s');
"Vern Rabe" wrote:
> Try this:
> INSERT INTO Tb1 VALUES('7', 'Joe''s';
> HTH
> Vern
> "MittyKom" wrote:
>|||escape single quote with a single quote
like
'joe''s'
(P.S: that not a double quote, its 2 single quotes :)|||Hi MittyKom
There is no need for any concatenation of strings. If you use two single
quotes inside outer single quotes, it is interpreted as one single quote in
the string.
So your use of concatenation is unnecessary but your use of the double
quotes (") is incorrect. Most interfaces have a setting called
QUOTED_IDENTIFIER set to on, which means that double quotes are used only to
delimit identifiers, and not user data. So the message you are receiving
refers to the fact that your single quote inside the double quotes is being
interpreted as an identifer, and it makes no sense.
So the cleanest solution is to just make it all one string to insert into
the second column, with the two adjacent single quotes getting interpreted
as one single quote in the string.
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe''s')
select * from Tb1
Drop table Tb1
The other solution is to SET QUOTED_IDENTIFIER OFF, and then your original
solution will work (but if you leave it on, other things might break)
HTH
Kalen Delaney, SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Thank you so much Vern and Omnibuzz.
"Omnibuzz" wrote:

> escape single quote with a single quote
> like
> 'joe''s'
> (P.S: that not a double quote, its 2 single quotes :)
>|||I use char(39) I think..
Insert Into Emp (LastName) Values ("O" + char(39) + "clock") -- O'clock
something like that.
I know there are quotes/inside other quotes methods, but those sometimes
come back to haunt me, since I deal with client's databases that I don't
have full control over.
..
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance

quick casting problem...

Can anyone please telkl me what is wrong with this portion of a SQL statement. I have been racking my brain over this and can't seem to get it right...

(CASE playerstats.fgm WHEN 0 THEN 0 ELSE (cast(100.00 * ((cast(SUM(playerstats.fgm)) as Decimal(8,2))/(cast(SUM(playerstats.fga)) as Decimal(8,2)))) as decimal(8,1))) AS fgp

I had it working fine, but when playerstats.fgm was a 0 then I got a divide by 0 error. This was the code when it was working ok as long as no one entered a 0 for fgm

(cast(100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) as decimal(8,1))) AS fgp

All I am trying to do is find a percentage... when playerstats.fgm = 0 then the percentage will be 0.

Any help will be much appreciated!!! :confused:one thing i notice is that you're mixing a scalar value in the outer CASE and an aggregate SUM value inside the CAST

and then you're setting 0 (an integer) as the THEN result, but CASTing the ELSE to 1 decimal place

plus, you're testing the wrong column for 0 divisor :) ;)

finally, if "fgm" and "fga" are field goals made/attempted, then you don't have to cast them in the calculation

try this -- cast( case when sum(playerstats.fga) = 0
then 0
else 100.00
* sum(playerstats.fgm)
/ sum(playerstats.fga)
end
as decimal(8,1) ) as fgp