27

I have this table:

   (<NUM_TRF int
   ,<NAME, varchar(255),>
   ,<DESCRIPTION, text,>
   ,<REPORT, varbinary(max),>)

I try to create a script in SQL Server 2008, in order to insert a line on my local database,

INSERT INTO [MY_DB_APP].[dbo].[CONNECT_USER]
VALUES(1, 'name', 'description', Cast('wahid' As varbinary(max)) )
GO

but I get this error:

String or binary data would be truncated.
The statement has been terminated.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
Wahid Gazzah
  • 443
  • 1
  • 5
  • 11
  • 2
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Aug 25 '14 at 13:54
  • 1
    This question is answered here: http://stackoverflow.com/questions/9021873/update-table-inserting-varbinary-data – StrangerTides Mar 12 '15 at 17:03

1 Answers1

36

Two issues:

Issue #1: don't use TEXT anymore - it's deprecated. Use a VARCHAR(n) with a suitable size of n, or if you really must (only if you REALLY must), use VARCHAR(MAX)

CREATE TABLE dbo.CONNECT_USER
(
    NUM_TRF int,
    NAME varchar(255),
    DESCRIPTION varchar(1000),
    REPORT varbinary(max)
)

I would personally also avoid writing EVERYTHING IN ALL CAPS - this just makes it so much harder to read! And I would try to avoid very generic column names like Name or Description - those are not very intuitive, and might collide with other table's columns and / or with SQL Server reserved keywords. Try to use more expressive, more context-related column names that make sense in your environment (ConnectUsername or whatever)

Issue #2: when doing an INSERT, I would recommend to always define the column you want to insert into. This avoids unpleasant surprises when a table is restructured or new columns are added:

INSERT INTO [MY_DB_APP].[dbo].[CONNECT_USER](NUM_TRF, NAME, DESCRIPTION, REPORT)
VALUES(1, 'name', 'description', CAST('wahid' AS VARBINARY(MAX)))
GO
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • If you guys are looking for something to clean a column which is varbinary, use: update COLUMN set TABLE = cast('1' as varbinary(max)) where TABLE.CODE > 0 – Saeger Apr 08 '15 at 21:38
  • 1
    thanks a lot marc_s, it straight away converted my `text` to `varbinary`. It's very simple – Nad Dec 02 '15 at 09:52
  • 2
    I like the answer, but I would like to discuss and learn whether to use more expressive names for a already specific domain is really necessary. I mean, it's more concise and still clear enough if one can access name columns like `store.employee.name` and `store.client.name`, for instance, than repeating the tree naming all over, because we can also have different employee and client tables over multiple databases, and they could be called `store.store_client.store_client_name` and `enterprise.enterprise_client.enterprise_client_name`, for instance, following the same principle. Best regards. – dandev486 Jun 04 '18 at 13:30