Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to override the String or Binary Data being Truncated

Posted on 2009-07-15
7
Medium Priority
?
1,014 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 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

916 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