ANSI_WARNINGS - 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

SQL Server 2005 Forums

Transact-SQL (2005)

ANSI_WARNINGS

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-25 : 10:06:07

Hi, We have an insert statement as part of a stored procedure.
This stored proc has the ANSI_WARNINGS OFF.

So when there is a data overflow error on some field, it just truncates the value and inserts it into the table.

Is there a way to identify these records after the insert is done. I'm assuming @@ERROR would return just a 0. Any other way?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-06-25 : 14:15:59

perhaps something like this:


declare @t table (col1 int, col2 varchar(5))
begin try
	insert into @t values (1,'aaaaaaaaaaaaaaa')
end try
begin catch
	if @@ERROR > 0
		begin
			set ansi_warnings off
			insert into @t values (1,'aaaaaaaaaaaaaaa')
			set ansi_warnings on
			select 'error occured'
		end
end catch
select * from @t

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:21:13

quote:
Originally posted by vijayisonly

Hi, We have an insert statement as part of a stored procedure.
This stored proc has the ANSI_WARNINGS OFF.

So when there is a data overflow error on some field, it just truncates the value and inserts it into the table.

Is there a way to identify these records after the insert is done. I'm assuming @@ERROR would return just a 0. Any other way?

where does the data for insert come from? is it passed through parameter in stored procedure?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-25 : 14:39:38

quote:
Originally posted by dinakar

perhaps something like this:

declare @t table (col1 int, col2 varchar(5))
begin try
insert into @t values (1,'aaaaaaaaaaaaaaa')
end try
begin catch
if @@ERROR > 0
begin
set ansi_warnings off
insert into @t values (1,'aaaaaaaaaaaaaaa')
set ansi_warnings on
select 'error occured'
end
end catch
select * from @t

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Thanks for this approach. I will certainly try it out.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-25 : 14:41:15

quote:
Originally posted by visakh16

quote:
Originally posted by vijayisonly

Hi, We have an insert statement as part of a stored procedure.
This stored proc has the ANSI_WARNINGS OFF.

So when there is a data overflow error on some field, it just truncates the value and inserts it into the table.

Is there a way to identify these records after the insert is done. I'm assuming @@ERROR would return just a 0. Any other way?

where does the data for insert come from? is it passed through parameter in stored procedure?

Yes it comes as input to the SP. This SP is called by a different Distributed system , and they keep sending crappy data saying its a limitation on their system. I dont want to do a substring on most of the data before the insert.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:46:21

then isnt it enough to check the length of value passed to parameter and identify those that are longer than actual field length and then may be add a bit field to your table and populate them as 1 for identifying this type of records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources