Solved

How to override the String or Binary Data being Truncated

Posted on 2009-07-15
7
995 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

27 Experts available now in Live!

Get 1:1 Help Now