| Author |
 Topic  |
|
|
mssqlteam
Starting Member
India
2 Posts |
 Posted - 02/18/2013 : 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?
|
Edited by - mssqlteam on 02/18/2013 04:10:49
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1433 Posts |
|
|
mssqlteam
Starting Member
India
2 Posts |
Posted - 02/18/2013 : 03:31:34
|
Hi Chandu thanks for reply but i didn't find any info specific to my problem..
|
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1433 Posts |
Posted - 02/18/2013 : 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 |
Edited by - bandi on 02/18/2013 05:21:28 |
|
|
|
mmkrishna1919
Yak Posting Veteran
India
54 Posts |
Posted - 02/19/2013 : 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
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/19/2013 : 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
Flowing Fount of Yak Knowledge
India
1433 Posts |
Posted - 02/20/2013 : 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 |
Edited by - bandi on 02/20/2013 01:09:28 |
|
|
| |
Topic |
|
SET options
