String or binary data would be truncated. The statement has been terminated

Question

0.00/5 (No votes)

See more:

I tried the suggestions found elsewhere on here....the table as it was created has a varchar length of 255 in SQL SMS.

The coding which was apparently working before (stopped updating last month) used a "Cast"command to convert via varchar. I tried changing these lines in the code to 255 and it still errors out.
The data is merely a formula to put coordinates together (ie: latitude +" , "+ longitude) so it should be around 19 characters long.

Any suggestions? This table used to work until about a month ago and i think this line of code is why its not working. When I run the query without putting into the table it runs fine.

Thanks for any help!

Distance_Calc as

(select distinct

cid.CaseID
,cast(bd.latitude as varchar(20)) + ', ' + cast(bd.longitude as varchar(20)) bd_geo
,cast(tt.latitude as varchar(20)) + ', ' + cast(tt.longitude as varchar(20)) tt_geo
,cast(sp.latitude as varchar(20)) + ', ' + cast(sp.longitude as varchar(20)) sp_geo

What I have tried:

changing varchar(20) to 255 and MAX, converting them under the Select statement to varchar(20) and 255

Posted 15-May-18 6:27am

Member 13828533

Updated 16-May-18 4:21am

Add a Solution

Comments

MadMyche 15-May-18 12:49pm

Table schemas would be really helpful; the command as is shouldn't fail; but without knowing the destination it's a crapshoot

Richard Deeming 15-May-18 14:10pm

If you're using MS SQL Server 2008 or later, you might want to look at the built-in spatial types support:
geography (Transact-SQL) | Microsoft Docs[^]
geometry (Transact-SQL) | Microsoft Docs[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-05-15T07:46:00

Start by looking very closely at your data - bd, tt, and sp - and see exactly what they contain.
Check their datatype: I'd expect a lat or long to be either a float (for DDD.dddd format) or a string (for DDD MM SS + compass and similar formats). For strings, look to see if there are any "odd values" and run a test query to find the maximum length of each of the six fields.

It's a conversion problem, so it's almost certainly data content related.

Quote:
When i go to 75 days, it gives the error....so im guessing somewhere in there, the data attempting to be pushed into the table exceeds the defined limits and it may even be a one off thing. is that a fair statement to make?

Absolutely! It may be related to the SQL guy leaving - not necessarily deliberate, but he may have known that an app adds bad data and arranged something to "fix" it, and now he's not there, it doesn't run automatically?
Use your date filter to narrow down the timeframe and reduce the number of rows that might contain the "bad data" to manageable levels, then look at the raw data to isolate the problem rows. From them, you should be either able to mod your query to work, or better identify the source app that puts it in there, and fix that.

Member 13828533 · Answer 2 · 2018-05-16T04:22:00

The script runs daily between 2am and 6am along with several others.

I used a timeframe to start with a smaller dataset and slowly increased it until i found the exact day the error occured. Extracted the last "good day" and first bad day, did a MAX Len comparison in Excel and surprise - there was a value that was 52 characters long and the SQL Database length is 50.

The comments in here were based on a value in a string with keywords "Changed To" to define a change in status. it seems an autopopulated value in our dispatch system was different and got around his logic.

Changing the value in the SQL Table wouldn't resolve this because the resulting value is not something we want to see....its an "irrelevant" value...so i coded in a line to prevent that
"and len(spstatchg.comment)<51"
to default the value to something else.

Thank you all for the help in figuring this out!

String or binary data would be truncated. The statement has been terminated

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0