SQL - Error: (String Data, Right Truncation, Invalid Character value for cast specifications (01004))

First time this error popped up!
SAP database, MS SQL .
Browse contains Invoice related fields, including these two fields.

  1. CardName, nvarchar(100), Clarion CSTRING(101)
  2. NumAtCart, nvarchar(100), Clarion CSTRING(101)

An ABC browse has been working since 2010, never missed a beat. Went through all the version of clarion to the latest 11, still beating.

Then NumAtCart was added to the same Browse. Now when the user click on a Print button, Generate an invoice, we get the error above. Only on some records…

NumAtCart: is the Customer’s Orders Number. I guess the Sales Agent copies and pastes from their e-mail the customers’ OrderNumber… So there might be “bad” characters stored in this field.

The error goes away when NumAtCart is removed from the Browse. But we need it to formulate part of the subject line of an e-mail.

Any suggestions?

PS! We are not allowed to write to the SAP SQL Database.


As I understand: nvarchar [ ( n | max ) ]
Variable-size string data. n defines the string size in byte-pairs and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^30-1 characters (2 GB). The storage size is two times n bytes + 2 bytes.

So I suppose in Clarion it should be CSTRING(203)

and you had no problem before because data was not reached max length

Thank you for the suggestion.
In clarion the Characters of CSTRING was updated to 203, recompiled, but still error persists. :frowning_face:

What I meant above was that the Browser never had NumAtCart in the list before. Only when we added that field, did the error appear when we click on PRINT button.
The report also accesses that same table.

no idea then. If you really do not need NumAtCart in your browse - remove it but add NumAtCart as a Hot field.

also, try to check the length of the NumAtCart in SSMS like:

select LEN(NumAtCart) from yourtable

Good idea. Thank you for your input.
SELECT NumAtCard, len(NumAtCard)
FROM [MyDB].[dbo].[OINV] where len(NumAtCard) >30
Only two records appear. Both: LENGTH 33.

Removing them from the browse and adding them as a hot field, produces the same error. :thinking:

try to add
to your DRIVER Options for that table

This error is evil and awful. It hung around in a system i supported/developed for quite a while.

No helpful comments other than going direct to SQL bypassing Clarion driver libraries as much as possible.

Do you have ascii visual range characters only in this field, 32 thru 127 I think?

Regards Richard Bryce

Thank you all for responding.
Tried: “/IGNORETRUNCATION=TRUE”, still an issue. :sob:

Let me paint a bigger picture. 2 X list boxes 3 tables.
List box 1: Invoice header DBO.OINV joined to Customer Table DBO.OCRD on CARDCODE
List Box 2: Invoice line items DBO.INV1

Once we added the OINV:.NUMATCARD field, the error presented itself.
Making a new browse out of the wizard did not present a problem-it appears DBO.OCRD join provoked the issue.
Back to original browse: Removing the DBO.OCRD and returning the field OINV:.NUMATCARD. It works! YES!! yes !! why?? I have no idea. :confused: but :grinning_face_with_smiling_eyes:

How would one bypass the clarion libraries? I have no idea!
(We try not to fiddle to much with drivers settings, considering they have been working for a few year)

Bypassing the template clarion browse / view engine I think is required, I think I also ended up hand coding the small update form for this table. Using the turbosql switch to talk direct to sql in the end.

I had to revisit this procedure. Same error.
Digging and digging I found that I actually had a miss matched field. :face_with_raised_eyebrow: :see_no_evil:

Quite embarrassing. But lesson learned-be thorough.

All is well!

A clear vote of confidence goes to Clarion dictionary import!

I think unique keys defined in the dct using cstrings over 1000 characters throw this message or similar if using a tps file as well.


you might be right but TBH I wouldn’t use a CSTRING in a key (because I would be nervous about crud after the terminating null), and if I ever did I certainly wouldn’t have a key over 1000 characters.

The error you are referring to could be due to storing the position in a variable which is too small? This one:

Error 78 is different to what I’ve been seeing, and whilst there is no harm in being nervous about the crud after the cstrings null terminator, if the code processing the key is using that crud, then the code processing the key has a bug, it shouldnt matter whether the cstring is 10 characters long or more.

However it seems MS have also stated what is the maximum allowable index size here.
CREATE INDEX (Transact-SQL) - SQL Server | Microsoft Docs

Up to 32 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. The limits are 16 columns and 900 bytes for versions before SQL Database and SQL Server 2016 (13.x).

Columns that are of the large object (LOB) data types ntext , text , varchar(max) , nvarchar(max) , varbinary(max) , xml , or image can’t be specified as key columns for an index. Also, a view definition can’t include ntext , text , or image columns, even if they are not referenced in the CREATE INDEX statement.

Oracle is working to 6,398 bytes, which would be using the ODBC driver.

How to Fix ORA-01450: Maximum Key Length (6398) Exceeded Errors (oracle.com)

So I think the cstring(1000) might be the internal limit for Clarion looking at the key which is a single field unique key, where the single field is a cstring(1000). As soon as I specify a cstring(1001), I get the error message, what ever it was.

The column in SQL is a varchar. SQL handles using that as an index very well. I have 100s of indexes on varchar columns.
Perhaps that was an issue with the TopSpeed driver? I always used STRING for character columns back then.


yes you are both right of course.

I seem to recall reading about a CSTRING key problem way back in the early CW days but thinking about it perhaps that person was using a GROUP that contained a CSTRING and so in that case the crud would be used. To get around it some people “cleaned” their cstrings by clearing the crud after the null terminator to either spaces or <0>.

CLEAR(MyCString,-1) will set all characters to <0>

It doesnt on my my machine, it just puts a <0> on the 1st character which is why I had to roll my own code to wipe every character.

You will only see this in the debugger, which in plain sight happens to be a good colour blind test.

Hmmm… Not happening here in C10 either.

I am pretty dang sure this was working at some point. Perhaps C6?

Not C6 or C11. Havent tried earlier versions though.

History will show who has been weakening the security of clarion apps, if there has been any. :male_detective:

After reading the docs on CLEAR(), apparently I was mistaken.

CLEAR(NormalString,-1) will fill a STRING with <0>

You could put that OVER a CSTRING.