Data Truncation in sp parameter

It seems that there is no error message if the length of the parameter
value exceeds parameter datalength
Create Procedure #test (@.data varchar(10))
as
Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
The result is
This is te
Why does SQL Server not raise any error?
MadhivananThat is what is called implicit conversion. Read BOL on this
Regards
R.D
--Knowledge gets doubled when shared
"Madhivanan" wrote:

> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||Hi
You just "selected" the data.SQL Server cuts off the result. Try doing
insertion and you will get the error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(2))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129800867.933647.36380@.g49g2000cwa.googlegroups.com...
> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||But when you give width 10, there is no error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(10))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
Madhivanan|||well,because @.data is already varchar(10) (sql server cuts off) and your
column had declared as varchar(10) , what's problem?
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129802989.571260.209840@.f14g2000cwb.googlegroups.com...
> But when you give width 10, there is no error
> Create Procedure #test (@.data varchar(10))
> as
> create table #t (col varchar(10))
> insert into #t Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
>
> Madhivanan
>|||> But when you give width 10, there is no error
That's right, because the parameter already made your data 10 characters
long, which fits just nicely in a VARCHAR(10) column.
You realize that not all data validation *has* to happen within the
database, right?|||Well
My question is why does SQL Server doesnt raise an error when the
length of value is nore than the parameter length?
I expect the same error that happend in this case
Declare @.t table(data varchar(10))
insert into @.t values('This is testing')
select data from @.t
Server: Msg 8152, Level 16, State 9, Line 2
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
Madhivanan

Data truncation does not trigger truncation error - why?

Hi,

I have a data file that has numeric data that looks like:

1.123456

And this column is defined as a DT_NUMERIC(18.6) in the flat file conn mgr.

As an experiment, I changed the destination column to a NUMERIC(18,0) - hoping that this would throw a truncation error at the flat file task level (where I have Truncation on all columns set to "fail component").

Not a peep. It loaded the data into the table, chopping off the 6 digits after the decimal point.

You would THINK that this would cause an error, but no. Why is this? The flat file task complains about all kinds of things, but this is such a gross error, you would think it would catch it!

Thanks

Well, double-click on the green arrow right before the destination. Verify that the metadata for the column in question is different than NUMERIC(18.0).|||

I'm not sure I follow your reasoning... although that's a cool way to view the metadata for the flat file.

I am more concerned about a situation where a table column changes, data is getting truncated unknowingly and no errors are ever triggered.

I just find it strange that it doesn't catch something like this, whereas it's extremely picky about other things that seem inconsequential (to me anyways). I find this inconsistent, especially when losing digits after the decimal point could create a big problem for someone. I don't think I'm being unreasonable here...!

|||

sadie519590 wrote:

I'm not sure I follow your reasoning... although that's a cool way to view the metadata for the flat file.

I am more concerned about a situation where a table column changes, data is getting truncated unknowingly and no errors are ever triggered.

I just find it strange that it doesn't catch something like this, whereas it's extremely picky about other things that seem inconsequential (to me anyways). I find this inconsistent, especially when losing digits after the decimal point could create a big problem for someone. I don't think I'm being unreasonable here...!

My reasoning for having you double check the metadata is to ensure that in fact, there is truncation going on. Having a destination column of NUMERIC(18,0) should yield a truncation error when the metadata for a column going into the destination is specified as NUMERIC(20,5) or something like that.

If the precision is the same though, such as a NUMERIC(18,5) going to a NUMERIC(18,0), the default SQL Server behavior is to round. See: http://msdn2.microsoft.com/en-us/library/ms187928.aspx|||

Ok, thanks for the clarification

I verified this myself, you have to overflow the precision before you will get an error, although I was hoping it would catch it on scale