SQL Server error 1934

2 Sep 2005 4:01 PM

denix

Hello all,

I'm trying to write a very basic stored procedure that inserts a row
into an indexed table with computed columns.
I originally created it with this code :

CREATE PROCEDURE insert_test

AS

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON
SET NUMERIC_ROUNDABORT OFF

INSERT INTO F_DOCENTETE(DO_Date,
            DO_Domaine,
            DO_Piece,
            DO_Type,
            DO_Tiers,
            DE_No)
    VALUES('31/08/2005',
        0,
        'BL123456',
        2,
        '123456',
        0)
GO

When I execute this procedure, it gives me the following error :
"insert failed because the following set options have
incorrect settings : 'ansi_nulls., quoted_idendifier'"

However, when I run only the insert code from the request analyser, it
works fine :

INSERT INTO F_DOCENTETE(DO_Date,
            DO_Domaine,
            DO_Piece,
            DO_Type,
            DO_Tiers,
            DE_No)
    VALUES('31/08/2005',
        0,
        'BL123456',
        2,
        '123456',
        0)

What's wrong with my SP ?

Thanks in advance for any help

Denis

2 Sep 2005 4:20 PM

Trey Walpole

Settings for QUOTED_IDENTIFIER and ANSI_NULLS are applied at compile
time, not run time.
Put them outside the procedure definition, e.g.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE insert_test

AS
....

denix wrote:
Show quote

> Hello all,
>
> I'm trying to write a very basic stored procedure that inserts a row
> into an indexed table with computed columns.
> I originally created it with this code :
>
> CREATE PROCEDURE insert_test
>
> AS
>
> SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
> QUOTED_IDENTIFIER, ANSI_NULLS ON
> SET NUMERIC_ROUNDABORT OFF
>
> INSERT INTO F_DOCENTETE(DO_Date,
>             DO_Domaine,
>             DO_Piece,
>             DO_Type,
>             DO_Tiers,
>             DE_No)
>     VALUES('31/08/2005',
>         0,
>         'BL123456',
>         2,
>         '123456',
>         0)
> GO
>
>
> When I execute this procedure, it gives me the following error :
> "insert failed because the following set options have
> incorrect settings : 'ansi_nulls., quoted_idendifier'"
>
>
> However, when I run only the insert code from the request analyser, it
> works fine :
>
> INSERT INTO F_DOCENTETE(DO_Date,
>             DO_Domaine,
>             DO_Piece,
>             DO_Type,
>             DO_Tiers,
>             DE_No)
>     VALUES('31/08/2005',
>         0,
>         'BL123456',
>         2,
>         '123456',
>         0)
>
>
> What's wrong with my SP ?
>
> Thanks in advance for any help
>
>
> Denis
>

5 Sep 2005 1:06 PM

denix

Thanks a lot, it works now..

Denis