Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.The setting is a sticky option, so setting it before execution or inside the proc doesn't change
anything. You have to set this in the connection where you *create* the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>
Showing posts with label line. Show all posts
Showing posts with label line. Show all posts
Wednesday, March 21, 2012
quoted_identifier
Hi
I am getting this strange error :
Server: Msg 1934, Level 16, State 1, Procedure
dataIntegration_mergeCompanyData_prc, Line 90
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'
the problem seem to be with a table in an update statement in the proc where
it references a table in an index view. if I change the left outer join of
the update statement to an inner join, the error goes away, if I uncomment
the schema_binding in the index view, the error also goes away.
anyone know what is going on?
thanks
PAny time you access an indexed view the connection must have certain SET
settings such as QUOTED_IDENTIFIER set a certain way. Look up Indexed Views
and then Set options that affect results underneath that for details.
Andrew J. Kelly SQL MVP
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:79578F94-688A-4A17-B8BA-D6418C437AEA@.microsoft.com...
> Hi
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where
> it references a table in an index view. if I change the left outer join
> of
> the update statement to an inner join, the error goes away, if I uncomment
> the schema_binding in the index view, the error also goes away.
> anyone know what is going on?
> thanks
> P
>|||alfred (alfred@.discussions.microsoft.com) writes:
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where it references a table in an index view. if I change the left
> outer join of the update statement to an inner join, the error goes
> away, if I uncomment the schema_binding in the index view, the error
> also goes away.
> anyone know what is going on?
As Andrew said, you are performing an update that affects an indexed
view. Whenever the you work with an indexed view, these settings must
be on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDNING,
CONCAT_NULL_YIELDS_NULL and ARITHABORT. All but the last option are
on by default when you connect with any API but DB-Library.
However, for the first two settings, what applies when you run a stored
procedure is not the setting for the connection, as these settings are
saved with the stored procedure.
And very unfortunate, there are two tools for which QUOTED_IDENTIFIER
is off by default: OSQL and Enterprise Manager (the latter also has
ANSI_NULLS off by default). Therefore, if you use these tools, you
must take precautions to make sure that this setting is on. If you
use OSQL, use the -I option to turn on QUOTED_IDENFIER. If you use
Enterprise Manager to edit your procedures, simply stop doing that
and use Query Analyzer instead. QA does not have this issue, and is
better editor anyway.
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.mspxsql
I am getting this strange error :
Server: Msg 1934, Level 16, State 1, Procedure
dataIntegration_mergeCompanyData_prc, Line 90
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'
the problem seem to be with a table in an update statement in the proc where
it references a table in an index view. if I change the left outer join of
the update statement to an inner join, the error goes away, if I uncomment
the schema_binding in the index view, the error also goes away.
anyone know what is going on?
thanks
PAny time you access an indexed view the connection must have certain SET
settings such as QUOTED_IDENTIFIER set a certain way. Look up Indexed Views
and then Set options that affect results underneath that for details.
Andrew J. Kelly SQL MVP
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:79578F94-688A-4A17-B8BA-D6418C437AEA@.microsoft.com...
> Hi
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where
> it references a table in an index view. if I change the left outer join
> of
> the update statement to an inner join, the error goes away, if I uncomment
> the schema_binding in the index view, the error also goes away.
> anyone know what is going on?
> thanks
> P
>|||alfred (alfred@.discussions.microsoft.com) writes:
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where it references a table in an index view. if I change the left
> outer join of the update statement to an inner join, the error goes
> away, if I uncomment the schema_binding in the index view, the error
> also goes away.
> anyone know what is going on?
As Andrew said, you are performing an update that affects an indexed
view. Whenever the you work with an indexed view, these settings must
be on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDNING,
CONCAT_NULL_YIELDS_NULL and ARITHABORT. All but the last option are
on by default when you connect with any API but DB-Library.
However, for the first two settings, what applies when you run a stored
procedure is not the setting for the connection, as these settings are
saved with the stored procedure.
And very unfortunate, there are two tools for which QUOTED_IDENTIFIER
is off by default: OSQL and Enterprise Manager (the latter also has
ANSI_NULLS off by default). Therefore, if you use these tools, you
must take precautions to make sure that this setting is on. If you
use OSQL, use the -I option to turn on QUOTED_IDENFIER. If you use
Enterprise Manager to edit your procedures, simply stop doing that
and use Query Analyzer instead. QA does not have this issue, and is
better editor anyway.
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.mspxsql
Friday, March 9, 2012
Quick select statement question
hi all
another easy one
how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.What have you come up with so far?|||select case when exists(select * from MyTable where MyValue <> 0) then 1 else 0 end|||SELECT dbo.tPA00175.chrJobNumber, dbo.tPA00175.intJobKey, dbo.tPA00125.numQuantityToInv
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQuantityToInv = 0)
but this is only selecting the single values...id like to select the ones that have all 0's for the particular jobkey|||How about:
select dbo.tPA00175.chrJobNumber,
dbo.tPA00175.intJobKey,
numQuantityToInv = 0
from dbo.tPA00175 inner join (select intJobKey
from dbo.tPA00125
group by intJobKey
having max(case when numQuantityToInv = 0 then 0 else 1 end) = 0
) as t2 on dbo.tPA00175.intJobKey = t2.intJobKey|||thanks, that works nicely
another easy one
how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.What have you come up with so far?|||select case when exists(select * from MyTable where MyValue <> 0) then 1 else 0 end|||SELECT dbo.tPA00175.chrJobNumber, dbo.tPA00175.intJobKey, dbo.tPA00125.numQuantityToInv
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQuantityToInv = 0)
but this is only selecting the single values...id like to select the ones that have all 0's for the particular jobkey|||How about:
select dbo.tPA00175.chrJobNumber,
dbo.tPA00175.intJobKey,
numQuantityToInv = 0
from dbo.tPA00175 inner join (select intJobKey
from dbo.tPA00125
group by intJobKey
having max(case when numQuantityToInv = 0 then 0 else 1 end) = 0
) as t2 on dbo.tPA00175.intJobKey = t2.intJobKey|||thanks, that works nicely
Quick reference to sp_??
Hi:
Does anyone know of a quick reference guide to MSSQL stored procedures
(sp_?) where there is a 'one line' description of what the procedure
actually does?
I have seen the complete reference in the online books, but want a
quick-and-dirty guide.
TIA,
Martin.
Hi,
Procedure:-
A set of precompiled TSQL commands stored under a single name and processed
as a unit.
See the below link for the informations and egs: for stored procedures.
http://www.awprofessional.com/articl...le.asp?p=25288
Thanks
Hari
MCDBA
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:#uKvxNgeEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hi:
> Does anyone know of a quick reference guide to MSSQL stored procedures
> (sp_?) where there is a 'one line' description of what the procedure
> actually does?
> I have seen the complete reference in the online books, but want a
> quick-and-dirty guide.
> TIA,
> Martin.
>
|||Hmm, I believe we've just witnessed the effects of a rift in the spoken
language continuum. When such rifts are visible, it's possible for
unsuspecting questions to get sucked into a sort of linguistics black hole,
and I fear that is exactly what has happened here.
There are those who believe that if we were able to travel faster than the
speed of light, we could navigate these rifts to our advantage, and thereby
gain access to The Supreme Knowledge Of All Ages... not that I'm one of
them, of course, it's just something I overheard while killing time at a
sleazy dive bar, in a back-water space port about half a light-year and
change from... or wait, did I dream it? :-) No matter...
I gather from your question that you already know what a stored procedure
is, in generic terms. What you're looking for instead is a quick reference
that covers the *system* stored procedures that ship with SQL Server, right?
I agree this level of reference would be useful, at one point I was trying
to generate one from info_schema. Got it to the point that I needed a
scripting environment or cursors on steroids to make it look like I wanted.
I was hoping to extract a one-line mission statement from a good percentage
of them by loosely parsing the sources for comments -- god forbid they
follow any sort of prevalent convention. In the end I found myself wishing
that all the replication-specific SPs could be easily isolated/filtered from
view, as I found myself not caring quite so much if they were represented in
my little reference...
Then I decided just the names and order of the parameters for each would be
nominally useful. Then I decided to cut my losses and work on things that
made me money
Sadly, after all that, I'm not aware of any such reference. A book by
Kalen Delaney has a chapter that somewhat approaches it, but packing around
1000 pages of paper defeats the premise of a quick ref.
Author Kalen Delaney Based on the first edition by Ron Soukup
Pages 1088
Disk 2 Companion CD(s)
Level Intermediate
Published 11/15/2000
ISBN 0-7356-0998-5
Sorry I don't have anything more worthwhile for you.. but at least I got
your question. :-)
-Mark
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uiIW5BheEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Procedure:-
> A set of precompiled TSQL commands stored under a single name and
processed
> as a unit.
>
> See the below link for the informations and egs: for stored procedures.
> http://www.awprofessional.com/articl...le.asp?p=25288
> Thanks
> Hari
> MCDBA
>
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:#uKvxNgeEHA.3520@.TK2MSFTNGP10.phx.gbl...
>
begin 666 1pxt.gif
L1TE&.#EA`0`!`( ``/___P```"'Y! $`````+ `````!``$`0 ("A%$`.P``
`
end
|||Hi Mark:
"Mark J. McGinty" <mmcginty@.spamfromyou.com> escribi en el mensaje
news:uSRVpoqeEHA.2812@.tk2msftngp13.phx.gbl...
[snip]
> Hmm, I believe we've just witnessed the effects of a rift in the spoken
> language continuum.
> I gather from your question that you already know what a stored procedure
> is, in generic terms. What you're looking for instead is a quick
reference
> that covers the *system* stored procedures that ship with SQL Server,
right?
[snip]
Yes, you are correct. I know what they are and how to use them, I just want
to have a list (which I can extract from the meta data) and a quick
description of what the procedure does, and maybe a brief rsum of the
parameters.
Indeed language is a strange medium and not easily interpreted. In
programming terms I'm looking for the XML documentation equivalent provided
in the .NET environment to document methods and properties!!
Thanks,
Martin.
|||Martin,
> Does anyone know of a quick reference guide to MSSQL stored
> procedures (sp_?) where there is a 'one line' description of
> what the procedure actually does?
> I have seen the complete reference in the online books, but want a
> quick-and-dirty guide.
The closest thing to quick reference would be this page from Books
Online that lists the documented stored procedures grouped by
category.
http://msdn.microsoft.com/library/de...sp_00_519s.asp
Linda
Does anyone know of a quick reference guide to MSSQL stored procedures
(sp_?) where there is a 'one line' description of what the procedure
actually does?
I have seen the complete reference in the online books, but want a
quick-and-dirty guide.
TIA,
Martin.
Hi,
Procedure:-
A set of precompiled TSQL commands stored under a single name and processed
as a unit.
See the below link for the informations and egs: for stored procedures.
http://www.awprofessional.com/articl...le.asp?p=25288
Thanks
Hari
MCDBA
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:#uKvxNgeEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hi:
> Does anyone know of a quick reference guide to MSSQL stored procedures
> (sp_?) where there is a 'one line' description of what the procedure
> actually does?
> I have seen the complete reference in the online books, but want a
> quick-and-dirty guide.
> TIA,
> Martin.
>
|||Hmm, I believe we've just witnessed the effects of a rift in the spoken
language continuum. When such rifts are visible, it's possible for
unsuspecting questions to get sucked into a sort of linguistics black hole,
and I fear that is exactly what has happened here.
There are those who believe that if we were able to travel faster than the
speed of light, we could navigate these rifts to our advantage, and thereby
gain access to The Supreme Knowledge Of All Ages... not that I'm one of
them, of course, it's just something I overheard while killing time at a
sleazy dive bar, in a back-water space port about half a light-year and
change from... or wait, did I dream it? :-) No matter...
I gather from your question that you already know what a stored procedure
is, in generic terms. What you're looking for instead is a quick reference
that covers the *system* stored procedures that ship with SQL Server, right?
I agree this level of reference would be useful, at one point I was trying
to generate one from info_schema. Got it to the point that I needed a
scripting environment or cursors on steroids to make it look like I wanted.
I was hoping to extract a one-line mission statement from a good percentage
of them by loosely parsing the sources for comments -- god forbid they
follow any sort of prevalent convention. In the end I found myself wishing
that all the replication-specific SPs could be easily isolated/filtered from
view, as I found myself not caring quite so much if they were represented in
my little reference...
Then I decided just the names and order of the parameters for each would be
nominally useful. Then I decided to cut my losses and work on things that
made me money
Sadly, after all that, I'm not aware of any such reference. A book by
Kalen Delaney has a chapter that somewhat approaches it, but packing around
1000 pages of paper defeats the premise of a quick ref.
Author Kalen Delaney Based on the first edition by Ron Soukup
Pages 1088
Disk 2 Companion CD(s)
Level Intermediate
Published 11/15/2000
ISBN 0-7356-0998-5
Sorry I don't have anything more worthwhile for you.. but at least I got
your question. :-)
-Mark
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uiIW5BheEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Procedure:-
> A set of precompiled TSQL commands stored under a single name and
processed
> as a unit.
>
> See the below link for the informations and egs: for stored procedures.
> http://www.awprofessional.com/articl...le.asp?p=25288
> Thanks
> Hari
> MCDBA
>
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:#uKvxNgeEHA.3520@.TK2MSFTNGP10.phx.gbl...
>
begin 666 1pxt.gif
L1TE&.#EA`0`!`( ``/___P```"'Y! $`````+ `````!``$`0 ("A%$`.P``
`
end
|||Hi Mark:
"Mark J. McGinty" <mmcginty@.spamfromyou.com> escribi en el mensaje
news:uSRVpoqeEHA.2812@.tk2msftngp13.phx.gbl...
[snip]
> Hmm, I believe we've just witnessed the effects of a rift in the spoken
> language continuum.
> I gather from your question that you already know what a stored procedure
> is, in generic terms. What you're looking for instead is a quick
reference
> that covers the *system* stored procedures that ship with SQL Server,
right?
[snip]
Yes, you are correct. I know what they are and how to use them, I just want
to have a list (which I can extract from the meta data) and a quick
description of what the procedure does, and maybe a brief rsum of the
parameters.
Indeed language is a strange medium and not easily interpreted. In
programming terms I'm looking for the XML documentation equivalent provided
in the .NET environment to document methods and properties!!
Thanks,
Martin.
|||Martin,
> Does anyone know of a quick reference guide to MSSQL stored
> procedures (sp_?) where there is a 'one line' description of
> what the procedure actually does?
> I have seen the complete reference in the online books, but want a
> quick-and-dirty guide.
The closest thing to quick reference would be this page from Books
Online that lists the documented stored procedures grouped by
category.
http://msdn.microsoft.com/library/de...sp_00_519s.asp
Linda
Subscribe to:
Posts (Atom)