September 28, 2011 at 9:57 am
The application that we develop prepares and executes statements and I was recently asked a question as to why automatic truncation occurs.
I know that setting ansi warnings on the data will be truncated but this still didin't explain why it had NO affect on a prepare followed by an exec sp_execute always truncates. All databases have ansi warnings enabled set to falseon the instance of sql 2008r2. I have also tested this on sql 2005.
What am I missing?
here is a script to demonstare the issue.
create table FRED (ACCNT_CODE char(15) not null, LOOKUP char(10) not null)
print 'ANSI_WARNINGS OFF'
SET ANSI_WARNINGS OFF;
print 'prepare, execute insert'
declare @p1 int
set @p1=-1
exec sp_prepare @p1 output,N'@P1 char(15),@P2 char(10)',
N'insert into FRED ( ACCNT_CODE, LOOKUP ) values ( @P1, @P2)',1
select @p1
exec sp_execute @p1,'1234567890123422222222256','LOOKUP1111'
print 'Direct insert'
insert into FRED ( ACCNT_CODE, LOOKUP ) values ('1234567890123422222222256','LOOKUP1111')
print 'ANSI_WARNINGS ON'
SET ANSI_WARNINGS ON;
print 'prepare, execute insert'
set @p1=-1
exec sp_prepare @p1 output,N'@P1 char(15),@P2 char(10)',
N'insert into FRED ( ACCNT_CODE, LOOKUP ) values ( @P1, @P2)',1
select @p1
exec sp_execute @p1,'1234567890123422222222256','LOOKUP1111'
print 'Direct insert'
insert into FRED ( ACCNT_CODE, LOOKUP ) values ('1234567890123422222222256','LOOKUP1111')
select * from FRED
--delete from FRED
Many Thanks
September 28, 2011 at 10:14 am
This is from BOL page about ANSI_WARNINGS. http://msdn.microsoft.com/en-us/library/ms190368.aspx
ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2011 at 10:16 am
Thanks for this, but is there any way to force the truncation error to occur?
September 29, 2011 at 1:59 am
Can anyone get the exec sp_execute styatement above to give a truncation error?
Many thanks
September 29, 2011 at 7:15 am
According to the documentation I linked it is how MS designed it to work. You are assigning a value to a variable that is larger than 15 so the data is truncated. I don't think there is anyway around this. Sql will just silently truncate the data. The problem you are running into is that your data is being truncated when it is assigned to your variable. This is why you are not seeing the "string or binary data would be truncated" error. You will most likely have to add some checks in your code for data validation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 29, 2011 at 7:20 am
The only way I can think of to do it would be to insert the value into a table column defined in such a way that it would give a truncation error, then assign the variable value from the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2011 at 7:23 am
thanks for your replies
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply