Solved

How to override the String or Binary Data being Truncated

Posted on 2009-07-15
7
1,005 Views
Last Modified: 2012-05-07
SQL 2005
Shared hosting Query Analyzer.

Hi, all.  I have a SQL 2005 db on a shared host (GoDaddy).

I have duplicated a table on there (Giving it a diff name, of course), and I am now trying to copy the data from Tbl1 to Tbl2.  All is coming over fine, except for the "MAX" fields in Tbl1.

I have changed those values in Tbl2, as I am using Classic ASP, and it does not play nicely with the "MAX" fields.

So, if I set the Tbl2 respective field/column to "varchar(4000)", and run an UPDATE script to copy from Tbl1 to Tbl2, it continues to give me the error message:

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

Is there any way to override this, as I do not care if it truncates it?


Thanks so much,
Shane
0
Comment
Question by:lshane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24863583
>Is there any way to override this, as I do not care if it truncates it?
you cannot.

you have to get rid of the "problem2.

so, varchar(8000) is the max size in sql 2000, varchar(max) in sql 2005+

the problem could be a trigger on the table, running another statement which you might need to check...
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24863599
you can change that to bigger length , Varchar(8000)  or if you dont mind trimming the data  u can use ,  LEFT(Columnname, 4000) to update the targetColumn
0
 

Author Comment

by:lshane
ID: 24863624
Hi, aneeshattingal.  How would I incorporate your suggestion with the following code:

UPDATE Orders

  SET sale_listings2.sale_desc = (SELECT sale_desc FROM sale_listings WHERE

                     sale_listings.sale_ID = sale_listings2.sale_ID)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24863651
simple:
UPDATE Orders
 
  SET sale_listings2.sale_desc = (SELECT left(sale_desc,4000) FROM sale_listings WHERE
 
                     sale_listings.sale_ID = sale_listings2.sale_ID)

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24863666
well, the syntax seems wrong, you update orders, but join to sale_listing2 ...
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 24863708
UPDATE sale_listings2
SET sale_listings2.sale_desc = left(sale_desc,4000)
FROM sale_listings2
INNER JOIN sale_listings on sale_listings.sale_ID = sale_listings2.sale_ID
0
 

Author Comment

by:lshane
ID: 24864552
Thanks aneeshattingal and angellll.

Both of you had great input, and I appreciate it.

I split points between both of you.


I'll probably be back soon for some more questions.

Thanks so much,
Shane
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question