Solved

How to override the String or Binary Data being Truncated

Posted on 2009-07-15
7
997 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 142

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 142

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 142

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL HELP 2 87
SQL Syntax:  How to Find Commonality Among Similar Results 2 48
How to place a condition in a filter criteria in t-sql? 12 68
SQL Error - Query 6 24
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now