1

I have a scenario where there is a table and i nned to pass table values param inside one of the stored procedure to peform certain actions . Basic table structure is as follows. 

CREATE TABLE [dbo].[CitytTax] (
    [CountryCode] [int] NOT NULL,
    [TaxType] [varchar](255) NULL
)

As you see, Taxtype column is varchar type and takes upto 255 chars. However I will create on table type as below in part of application code and pass this table type as param to one of sp. 

DECLARE @TaxDetails as [CitytTax]

Now i will insert some dummy values into it and pass this table type to one of the SP. 

INSERT INTO @TaxDetails ([CountryCode],TaxType )
VALUES (6047,'Codfggtuioanoio charge to fund the liquidation of insurancevalues')

but getting error as below :

String or binary data would be truncated

The question here is table value type is having a column which is similar to actual database table. SO when i insert above script, it fails. However if i insert any value to [taxtype] which is less than 50 characters, it will insert successfully. but fails for more than 50 chars. IM wondering why it fails,it is supposed to take upto 255 characters right??

iamdave
  • 12,023
  • 3
  • 24
  • 53
maheshkumarbm
  • 11
  • 6
  • Do you have a `CREATE TYPE [dbo].[CitytTax] AS TABLE` ? What is it? – Alex K. May 05 '17 at 12:11
  • are you sure those are the only values being inserted? – Vamsi Prabhala May 05 '17 at 12:11
  • I'm out of my league with sql-server but this could be related to ASCII/Unicode etc. issue. Switch to unicode (use `nvarchar`) and check your DB schema/collations. Sorry if not helpful but see also e.g. http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar – ZZ-bb May 05 '17 at 12:14
  • the parameter that goes into a SP is as below: @TaxDetails as [CitytTax] ReadOnly, here this param is of type which is same as existing database table CitytTax . – maheshkumarbm May 05 '17 at 12:28
  • `CREATE TABLE` isn't how a user-defined table type is declared, which is what the other code samples you've shown us appear to be using. So can you show us the *actual* definition for the Table type? – Damien_The_Unbeliever May 05 '17 at 12:52
  • Well I figured out something which is a quick fix: just added below line before insert query: SET ANSI_WARNINGS OFF – maheshkumarbm May 05 '17 at 13:10

2 Answers2

0

If your data length is indeed shorter than the field length. Then you're having having another table filled by a trigger on the main table, where the column size also had to be changed.

Also, replace all single quotes of your insert into query to double quotes and pass it into the stored procedure.

Frank Fotangs
  • 50
  • 9
  • It has worked with ANSI_WARNIGS OFF . I had given a piece of SP, not complete one .... you can refer to other cases as well here: https://raresql.com/2014/01/03/sql-server-a-quick-solution-to-string-or-binary-data-would-be-truncated-using-stored-procedure/ – maheshkumarbm May 05 '17 at 13:23
0

Well I figured out something which is a quick fix: just added below line before insert query:

SET ANSI_WARNINGS OFF .

maheshkumarbm
  • 11
  • 6
  • 1
    That's not a fix. That's sticking your fingers in your ears and shouting `LA LA LA I CAN'T HEAR YOU`. The truncation will still **happen**, you're just "happily" ignoring that fact now. – Damien_The_Unbeliever May 05 '17 at 13:14
  • It has worked. I had given a piece of SP, not complete one .... you can refer to other cases as well here: https://raresql.com/2014/01/03/sql-server-a-quick-solution-to-string-or-binary-data-would-be-truncated-using-stored-procedure/ – maheshkumarbm May 05 '17 at 13:26
  • IM AGAIN TELLING, ITS JUST A QUICK FIX I FOUND OUT...... EVEN I WUDNT ACCEPT THIS AS A FINAL FIX. – maheshkumarbm May 05 '17 at 13:27
  • You still need to show us how you create the table type, your `CREATE TABLE` code is **not** the table type, its a completely different thing (with the correct size, presumably unlike the type). – Alex K. May 05 '17 at 13:52
  • it was the issue with table type which is resolved after modifying the size of the column in table type variable – maheshkumarbm Mar 27 '18 at 16:16