Need resolution for this below code issue

Question

I have Ran below query

DECLARE @temp TABLE(Id int identity(1,1), name varchar(20))
INSERT INTO @temp (name)
select name from sys.tables

The above query is working fine with out any issues in one machine. But i ran the another machine it through some error. Its very wired for me. I have attached the screen shot.

Both machines are having same sql server SQL server 2008 R2 Express(Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3) ) Edition.

Answer 1

This means one machine has table names that are all less than 20 characters, but the machine it doesn't work on has table names that are longer than 20 characters.

Change the size of your name column to nvarchar(255); you can go all the way up to 4000 if you still have trouble.

DECLARE @temp TABLE(Id int identity(1,1), name nvarchar(255))
INSERT INTO @temp (name)
select name from sys.tables

EDIT: based on @Raj's response and my subsequent research, I have modified the 'varchar' column to be 'nvarchar' because that is what table names can hold. For quick queries, I personally don't care if I use 255 instead of the actual potential length of a column's name (128). But per @Raj and the T-SQL documentation, the max column name length is 128. http://technet.microsoft.com/en-us/library/ms188348.aspx

Answer 2

The definition for thename column in sys.tables is

sysname(nvarchar(128))

In the first machine, you probably do not have any table names that are longer than 20 characters, so it works. However, in the second machine, looks like you have table names longer that 20 characters, hence the error. Try changing your @temp definition to

DECLARE @temp TABLE
(
ID int identity(1,1),
name nvarchar(128)
)

Answer 3

String or Binary data would be truncated error you get when you're trying to insert string which is more than defined length, in your case it's 20 and string trying to fit in that column is greater than 20.

Resolution: Make it as nvarchar and increase the length.

DECLARE @temp TABLE(Id int identity(1,1), name nvarchar(255))
INSERT INTO @temp (name)
select name from sys.tables

Answer 4

As Raj states the datatype for a column name in SQL Server is SYSNAME, therefore just use this as it is both backward and forward compatible:

DECLARE @temp TABLE(Id int identity(1,1), name sysname)
INSERT INTO @temp (name)
select name from sys.tables

See What is SYSNAME data type in SQL Server?