SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding out where this is falling over?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 10/12/2012 :  13:18:32  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 10/12/2012 :  13:31:02  Show Profile  Visit chadmat's Homepage  Reply with Quote
Use the LEN() function on all of your varchar data to make sure there isn't one that is too long.

-Chad
Go to Top of Page

SergioM
Posting Yak Master

165 Posts

Posted - 10/12/2012 :  13:51:20  Show Profile  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 10/12/2012 :  14:02:15  Show Profile  Reply with Quote
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."
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 10/12/2012 :  14:05:31  Show Profile  Reply with Quote
quote:
Originally posted by SergioM

Is it an error or a warning?


An error: statement has been terminated

quote:
Originally posted by SergioM
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.



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.
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/12/2012 :  14:23:31  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 10/14/2012 :  15:04:33  Show Profile  Reply with Quote
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.
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/15/2012 :  14:00:25  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/15/2012 :  14:25:14  Show Profile  Reply with Quote
1)SELECT '04'
2)count the number of characters
3)copy and paste the result in to Excel
4)count the number of characters
5)subtract the result from step 4 from the result in step 2
6)if the result in step <> 0, your counts are different

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 10/16/2012 :  13:02:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Rasta Pickles

dumped the 49000 records into a spreadsheet



Describe how you did the dump. Did you bcp the data out to a csv file? Or did you just use Management Studio to run the query and then copy/paste? If you used SSMS+copy/paste, that is your problem and the spreadsheet's data is invalid.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 10/23/2012 :  14:26:32  Show Profile  Reply with Quote
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 with
select *
from ...
where len (fieldn) > length of field
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 10/23/2012 :  15:31:03  Show Profile  Reply with Quote
quote:
Originally posted by chadmat
Now, 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.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 10/23/2012 :  22:18:59  Show Profile  Visit jezemine's Homepage  Reply with Quote
Rasta Pickles. any connection to Skankin' Pickle? I saw them at the OK Hotel once.


elsasoft.org
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000