set ansi_warnings off question 1

[TysonLPrice]

The second select is in production and started generating truncation warning. We fixed the issue but I wonder why it
generates errors with set ansi_warnings off in place?

set ansi_warnings off

Declare @T1 TABLE(TestIt VARCHAR(20))

-- This works – no warning

insert into @T1(Testit) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')

-- This doesn’t work – warning and is what is done in that SP

insert into @T1(Testit)
exec('Select ''xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'' ')

 

[Jamfool]

you are trying to insert a string that is too long for the type.

when you turn the warnings off, it will truncate it to a length of 20 and discard the rest of the string. (and it will not tell you it has done this...as you told it not too)

with the warnings on, it will error and tell you why it can not do the insert.

set ansi_warnings off
Declare @T1 TABLE(TestIt VARCHAR(20))
insert into @T1(Testit) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
SELECT LEN(Testit) [LengthOfString],Testit FROM @T1

 

[TysonLPrice]

The question is why do the warning still appear with set ansi_warnings off in the second insert?

 

[Jamfool]

sorry, misread it first time.

if you have got set ansi_warnings off, then it shouldnt do it.

where are you setting ansi_warnings in the stored procedure?

declare @ansi_warnings bit
select @ansi_warnings = ansi_warnings from sys.dm_exec_requests where session_id = @@spid
if @ansi_warnings = 1
begin
print 'ansi warnings is on'
end

 

[TysonLPrice]

Right at the top and also in the Select as a test. This occurrs in 2008 also (I'm on 2005)

 

[Jamfool]

I cant replicate it using what you have posted. if i place the ansi_warnings off in the stored proc, or in the select the error is not returned.

I can reproduce it if i make the scenario more complicated, with a trigger that fires off, and thus reverting back to default ansi warnings setting:

drop table t123
go
create table t123(TestIt VARCHAR(20))
GO
CREATE TRIGGER tt123
   ON  t123
   AFTER INSERT 
AS 
BEGIN
	PRINT 'Go Trigger'
	Declare @T1 TABLE(TestIt VARCHAR(20))
	insert into @T1(Testit) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')   
END
GO


insert into t123
exec('set ansi_warnings off; Select    ''xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'' ')

 

[TysonLPrice]

That's strange...just plugging this into Query Analyzer createes the issue (no warning on the first insert and one one the second.

set ansi_warnings off
Declare @T1 TABLE(TestIt VARCHAR(20))
insert into @T1(Testit) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
insert into @T1(Testit)exec('set ansi_warnings off; Select    ''xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'' ')

I wonder if it's the database setup or the connection properties in our different environments (DB and SQL session).

Putting 'set ansi_warnings off' in the select didn't stop the warning for me.

 

[Jamfool]

set ansi_warnings off
Declare @T1 TABLE(TestIt VARCHAR(20))
insert into @T1(Testit) values('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
insert into @T1(Testit)exec('set ansi_warnings off; Select    ''xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'' ')

I have just run your above code with no error. My box details:
10.0.2746.0 SP1 Standard Edition

So I ran it on two other boxes...and it errors on (I Dont believe they are configured differently but I will have a trawl thru the configs)

10.0.1600.22 RTM Standard Edition
9.00.3042.00 SP2 Express Edition with Advanced Services

 

[Jamfool]

Tried on my old test box and it errored
10.0.2531.0 SP1 Standard Edition

Applied updates to take it to
10.0.2746.0 SP1 Standard Edition

...error has gone away

 

[Jamfool]

haha found it, now that i have worked out its fixed ;p

http://support.microsoft.com/kb/971057/

FIX: An INSERT INTO <table name> EXEC <stored procedure name> statement generates error messages even if you set the ANSI_WARNINGS option to "OFF" in SQL Server 2008

Part of

http://support.microsoft.com/kb/971491

Cumulative update package 3 for SQL Server 2008 Service Pack 1

 

[TysonLPrice]

Great work! I'll pass that on to our DBA group. STAR!