SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SET options
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mssqlteam
Starting Member

India
2 Posts

Posted - 02/18/2013 :  02:19:12  Show Profile  Reply with Quote
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?

Edited by - mssqlteam on 02/18/2013 04:10:49

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/18/2013 :  02:44:59  Show Profile  Reply with Quote
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

Edited by - bandi on 02/18/2013 02:50:04
Go to Top of Page

mssqlteam
Starting Member

India
2 Posts

Posted - 02/18/2013 :  03:31:34  Show Profile  Reply with Quote
Hi Chandu thanks for reply but i didn't find any info specific to my problem..
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/18/2013 :  05:16:45  Show Profile  Reply with Quote
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

Edited by - bandi on 02/18/2013 05:21:28
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
85 Posts

Posted - 02/19/2013 :  07:23:40  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 02/19/2013 :  08:10:07  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/20/2013 :  01:06:24  Show Profile  Reply with Quote
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

Edited by - bandi on 02/20/2013 01:09:28
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000