SET options - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SET options

Author

Topic

mssqlteam
Starting Member

2 Posts

Posted - 2013-02-18 : 02:19:12

Hi All,
I am trying to execute an stored procedure but it is throwing an error message like

"Server: Msg 1934, Level 16, State 1, Procedure Line 12
INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations."

Currently my session settings are as shown below:

select sessionproperty('ANSI_WARNINGS') ---0
select sessionproperty('ANSI_PADDING')---0

If i add the below statements at the begining and the execute the same proc then it executed successfully:
GO
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
GO

I am not using any views in my Proc
No indexes are created.
My code contain a set of few sql functions like (charindex,substring,len,Ltrim...etc)

why do we need to set to ON only during execution of Proc any specific reasons are there?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-18 : 02:44:59

Check these links
http://beginsql.wordpress.com/2012/01/28/set-ansi_nulls-onoff-setting-in-sql-server/
http://beginsql.wordpress.com/2012/02/04/insertupdate-failed-because-the-following-set-options-have-incorrect-settings-quoted_identifier/

--
Chandu

mssqlteam
Starting Member

2 Posts

Posted - 2013-02-18 : 03:31:34

Hi Chandu thanks for reply but i didn't find any info specific to my problem..

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-18 : 05:16:45

Can you post procedure where that error occurs? Probably you might inserted NULL values for non-nullable column or any violation of integrity constraints..

ANSI_NULLS OFF treats NULL as a single value, whereas ANSI_NULL ON treats NULL as unknown(undefined) value
ANSI_PADDING Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

You can understand the reason behind the error?
http://msdn.microsoft.com/en-us/library/ms187403.aspx

--
Chandu

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-02-19 : 07:23:40

Hi Chandu,

This is my simple proc
GO
SET ANSI_WARNINGS OFF
SET ANSI_PADDING OFF
GO

ALTER PROCEDURE test
AS
BEGIN
CREATE TABLE testing (num char(10) )
INSERT INTO testing
SELECT value FROM test_table
END

while i am executing this proc i am getting the below error:

INSERT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations."

I just modify the above proc settings like

GO
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
GO

ALTER PROCEDURE test
AS
BEGIN
CREATE TABLE testing (num char(10) )
INSERT INTO testing
SELECT value FROM address
END

Now it is executing successfully...

i didn't get any hint from the predefined error message thrown by sql server.
because here in my proc i am not using any
indexed views
indexes on computed columns
filtered indexes
query notifications
XML data type methods
spatial index operations

Then why still i am getting above error message...?

Thanks...

M.MURALI kRISHNA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 08:10:07

Examine the table address. Does it fall under any of the categories that require ANSI_PADDING? In any case it might be a good idea to have ANSI_PADDING. In some future version of SQL Server, it will always be on.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 01:06:24

what is the datatype for value column in test_table?
and also check that column data for any padding related errors

your first procedure is also executed successfully for me... is there any computed columns in the table?
--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources