| Author |
Topic |
|
Rasta Pickles
Posting Yak Master
174 Posts |
 Posted - 2012-10-12 : 13:18:32
|
| Evening all,I am trying to insert records into a table but I get a "string or binary data would be truncated" error.I've executed the SELECT statement on its own, dumped the 49000 records into a spreadsheet and ensured that any of the fields that are type VARCHAR don't exceed the relevant length.Is there a SQL equivalent of the plain English "this is the record where it all went pear-shaped" or "this is the last successful record that could be imported"?If it's any use, there is a worklogid field being imported that it unique so that could be used as a guide.Alternatively, in VBA you can do a "on error next", i.e skip over any errors......is there a SQL equivalent?Thanks for advice as always. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-12 : 13:31:02
|
| Use the LEN() function on all of your varchar data to make sure there isn't one that is too long.-Chad |
 |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2012-10-12 : 13:51:20
|
| Is it an error or a warning? A warning just lets you know that variable text is being placed into a fixed field, but it doesn't calculate the length of each row before mentioning the potential for an issue.Also, what RDBMS are you using? I use Microsoft SQL 2008 & in SSIS, I can just designate that all rows that do not meet the database constraints should be put elsewhere. That way the process continues & I can deal with those rows afterwards without it affecting my process. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-10-12 : 14:02:15
|
quote:
Originally posted by chadmat
Use the LEN() function on all of your varchar data to make sure there isn't one that is too long.-Chad
I guess you missed "I've executed the SELECT statement on its own, dumped the 49000 records into a spreadsheet and ensured that any of the fields that are type VARCHAR don't exceed the relevant length." |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-10-12 : 14:05:31
|
quote:
Originally posted by SergioM
Is it an error or a warning?
An error: statement has been terminatedquote:
Originally posted by SergioMAlso, what RDBMS are you using? I use Microsoft SQL 2008 & in SSIS, I can just designate that all rows that do not meet the database constraints should be put elsewhere. That way the process continues & I can deal with those rows afterwards without it affecting my process.
I have no idea what you mean.....this is a newbie forum  I'm using SQL 2008 R2 and interrogating tables that live within a database accessible via the SSMS environment. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-12 : 14:23:31
|
quote:
Originally posted by Rasta Pickles
quote:
Originally posted by chadmat
Use the LEN() function on all of your varchar data to make sure there isn't one that is too long.-Chad
I guess you missed "I've executed the SELECT statement on its own, dumped the 49000 records into a spreadsheet and ensured that any of the fields that are type VARCHAR don't exceed the relevant length."
I didn't miss it, I just don't necessarily agree it is a valid test. There is obviously something that is too long or you wouldn't get that message. Take the spreadsheet out of the equation and see what SQL Server thinks the length is.-Chad |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-10-14 : 15:04:33
|
quote:
I didn't miss it, I just don't necessarily agree it is a valid test. There is obviously something that is too long or you wouldn't get that message. Take the spreadsheet out of the equation and see what SQL Server thinks the length is.-Chad
Yep, SQL's observations are spot on with what Excel reports.Are you saying Excel can't be trusted to calculate the number of characters in a field?Pretty heroic allegation I have to say.I don't want to forward your aspersions to Redmond because there's no telling what they're capable of but alluring to the fact that Excel can't calculate is very brave my friend and I salute you for daring to state so in a public forum. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-15 : 14:00:25
|
| Interesting way to illicit help...I am making no claims for or against Excel..in fact I didn't even know Excel was the spreadsheet being used until now. When I troubleshoot, I try to remove as many variables as possible. Since there is no need for a spreadsheet to do the calculations, I suggested removing it from the equation all together. Feel free to forward to Redmond what you wish.Now, would you like to receive assistance with your issue, or just continue to insult the people who are trying to help?-Chad |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-10-15 : 14:25:14
|
| 1)SELECT '04'2)count the number of characters3)copy and paste the result in to Excel4)count the number of characters5)subtract the result from step 4 from the result in step 26)if the result in step <> 0, your counts are differentJimEveryday I learn something that somebody else already knew |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2012-10-23 : 14:26:32
|
To filter out all of the fluff (and there will be a lot with 49,000 records), you can try to narrow in on the offending record by doing the select statement like this:select max (len (field01)), max (len (field02)), ....from .... All it will take (as you have seen) is one record that is too big. Once you find the column where the bad value is, you can single it(them) out withselect *from ...where len (fieldn) > length of field |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2012-10-23 : 15:31:03
|
quote:
Originally posted by chadmatNow, would you like to receive assistance with your issue, or just continue to insult the people who are trying to help?-Chad
My sincere apologies, it wasn't my intention to offend, I merely assumed you were questioning Excel's functionality. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2012-10-23 : 22:18:59
|
Rasta Pickles. any connection to Skankin' Pickle? I saw them at the OK Hotel once.
elsasoft.org |
|
|
|
|
Finding out where this is falling over?