Insert failed & set option error

[Bercin Ates via SQLMonster.com]

I?m getting an error when I execute a stored procedure which is try to insert a row to a table.

The error is:
Server: Msg 1934, Level 16, State 1, Procedure SRV_SP_IS_EMRI_ SATIRI_EKLE, Line 32
INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS.'.

In my sp, I insert an row to a table. But also I created a view which is select some fields from this table.
(Note: Some fields are calculated fields in this view. To see if calculated fields cause this error , I didnt selec calculated fields in the view, but I?m still getting this insert error.One more note: My indexed field is an identity field also.) I think, index causes this error. But I cant find my error.
Thank you for your help...

To do this view, I execute this code:

drop view SRV_V_GARANTILI _IS_EMRI_SATIRL ARI_T
go

CREATE VIEW dbo.ViewT
with SCHEMABINDING AS
SELECT
S.FieldA,
....S. FieldH
FROM
dbo.TABLE S
WHERE
(S.FieldA = 1) AND (S. FieldB IS NULL) AND (S.FieldH IN (0,1))
go

SET NUMERIC_ROUNDAB ORT OFF
GO
SET ANSI_PADDING,AN SI_WARNINGS,CON CAT_NULL_YIELDS _NULL,ARITHABOR T,QUOTED_IDENTI FIER,ANSI_NULLS ON
GO

create unique clustered index ViewT_IX_FieldA on ViewT (FieldA)
go

*************** *************** ***********
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....4cddba67ade8b6
*************** *************** ***********

[Erland Sommarskog]

Re: Insert failed & set option error

Bercin Ates via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> I?m getting an error when I execute a stored procedure which is try to
> insert a row to a table.
>
> The error is:
> Server: Msg 1934, Level 16, State 1, Procedure
> SRV_SP_IS_EMRI_ SATIRI_EKLE, Line 32
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_NULLS.'.
>
> In my sp, I insert an row to a table. But also I created a view which is
> select some fields from this table.[/color]

In order to use an indexed view, you must have these SET options:
[color=blue]
> SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIE LDS_NULL, ARITHABORT,
> QUOTED_IDENTIFI ER, ANSI_NULLS ON
> GO[/color]

(And NUMERIC_ROUNDAB ORT must be OFF)

Of these QUOTED_IDENTIFI ER and ANSI_NULLS are stored with stored procedures,
triggers and user_defined functions. This means that if you do:

SET ANSI_NULLS ON
go
EXEC procedure_saved _with_ansi_null s_off

The procedure runs with ANSI_NULLS OFF.

I don't know why the procedure was saved with ANSI_NULLS off, but one
possibility is that the procedures was loaded with the command-line tool
ISQL that has all the SET commands off by default. If you use ISQL, you
need to include SET ANSI_NULLS ON, QUOTED_IDENTIFI ER ON in your script.

You can also use OSQL, in which case you should specify the -I option,
to have QUOTED_IDENTIFI ER on. (OSQL has the other options on by default.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bercin Ates via SQLMonster.com]

Re: Insert failed & set option error

Thank you for your reply.
I saw that stored procedures always run by using set options as "off".
I'll try to do your methods. Thank you again.

*************** *************** ***********
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....ql-server/5098
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....1485f604301423
*************** *************** ***********

[Bercin Ates via SQLMonster.com]

Re: Insert failed & set option error

Hi, again. Unfortunately I couldn't do it. Because I must insert my new fields by using stored procedure. Even if I added my setting commands inside the sp, it could't do it.
My sp like tihs:

create prosedure...
as
SET ANSI_NULLS ON
SET QUOTED_IDENTIFI ER ON

INSERT INTO SRV_IS_EMRI_SAT IRLARI_DENEME
(
....

As you said before, it use QUOTED_IDENTIFI ER option as "off". How I can set this option as "on" inside the insert proc? Is it not possible?
Thank you again...

Ber?in Ate?

*************** *************** ***********
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....ql-server/5098
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....33b79b297aaa4d
*************** *************** ***********

[Erland Sommarskog]

Re: Insert failed & set option error

Bercin Ates via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> Hi, again. Unfortunately I couldn't do it. Because I must insert my new
> fields by using stored procedure. Even if I added my setting commands
> inside the sp, it could't do it.
> My sp like tihs:
>
> create prosedure...
> as
> SET ANSI_NULLS ON
> SET QUOTED_IDENTIFI ER ON
>
> INSERT INTO SRV_IS_EMRI_SAT IRLARI_DENEME
> (
> ...
>
> As you said before, it use QUOTED_IDENTIFI ER option as "off". How I can
> set this option as "on" inside the insert proc? Is it not possible?[/color]

How could it? If you say:

SET QUOTED_IDENTIFI ER OFF
go
CREATE PROCEDURE tricky AS
SELECT "This is a string" FROM tbl
SET QUOTED_IDENTIFI ER ON
SELECT "This is an identifier" FROM tbl
go

Or even worse:

SET QUOTED_IDENTIFI ER OFF
go
CREATE PROCEDURE tricky AS
SELECT * FROM "This is illegal"
SET QUOTED_IDENTIFI ER ON
SELECT * FROM "This is now a legal table name"
go

Since QUOTED_IDENTIFI ER affects the interpretation of the procedure
text, it must be a static setting.

I don't know why you are setting quoted identifier off, but you will have
to make a choice: have it on, or stop using indexed views. I much recommend
the former.

(So, OK, by using dynamic SQL you can actually circumvent the problem,
but since it will reappear in each place you have the view, that is not
a viable solution.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bercin Ates via SQLMonster.com]

Re: Insert failed & set option error

Ok, this problem was solved now. I have just understood that,I must set this option before creating the procedure.
Thank you very much :)
But now, it is giving ARITHABORT error :(
I'm searching this problem now.Do you have any idea? Thank you again...

*************** *************** ***********
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....ql-server/5098
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....22f925624f7fcc
*************** *************** ***********

[Erland Sommarskog]

Re: Insert failed & set option error

Bercin Ates via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> Ok, this problem was solved now. I have just understood that,I must set
> this option before creating the procedure. Thank you very much :) But
> now, it is giving ARITHABORT error :(
> I'm searching this problem now.Do you have any idea? Thank you again...[/color]

ARITHABORT is also a setting that has to be on. The problematic issue
with ARITHABORT is that in difference to the other settings is not on
by default when you connect with OLE DB or ODBC.

Therefore you will have to issue a SET ARITHABORT ON when you connect. (For
ARITHABORT there is no setting saved with the procedure.) An alternative
is to use ALTER DATABASE to set ARITHABORT on by default for the database,
or set the configuration option 'user options' to set it by default on
server level. (See Books Online for syntax etc.)

What will not work is to set ARITHABORT inside the procedure. This is
because when you invoke the procedure, SQL Server builds the query plan,
and then finds that there is an access to an indexed view with ARITHABORT
off, because the command SET ARITHABORT ON has not yet been executed. (And
once the command is hit, the procedure will be recompiled, which has a
performance cost.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bercin Ates via SQLMonster.com]

Re: Insert failed & set option error

Thanks a lot:)It's running now.I wish success for all...

_______________ ______
Message posted via http://www.sqlmonster.com