Solved

How to override the String or Binary Data being Truncated

Posted on 2009-07-15
7
1,004 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL order by with "over" statement and row_number() 11 63
How to import SQL 2000 database to SQL 2014 5 180
Replace Dates in query 14 56
Substring works but need to tweak it 14 35
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

739 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