# SQL - Error: (String Data, Right Truncation, Invalid Character value for cast specifications (01004)) **URL:** \ **Category:** questions\ **Tags:** mssql, odbc\ **Created:** [February 25, 2021, 7:23pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894 "2021-02-25T19:23:48Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![FoucheRabie](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/7cd45c/32.png) [@FoucheRabie](https://clarionhub.com/u/FoucheRabie)\ **Post date:** [February 25, 2021, 7:23pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/1 "2021-02-25T19:23:48Z")
Greetings, 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.. Background: 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. ![image](https://clarionhub.com/images/transparent.png) ---
**Author:** ![Clarion\_clarion](https://clarionhub.com/user_avatar/clarionhub.com/clarion_clarion/32/636_2.png) [@Clarion\_clarion](https://clarionhub.com/u/Clarion_clarion)\ **Post date:** [February 26, 2021, 8:48am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/2 "2021-02-26T08:48:52Z")
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 ---
**Author:** ![FoucheRabie](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/7cd45c/32.png) [@FoucheRabie](https://clarionhub.com/u/FoucheRabie)\ **Post date:** [February 26, 2021, 9:33am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/3 "2021-02-26T09:33:58Z")
Thank you for the suggestion. In clarion the Characters of CSTRING was updated to 203, recompiled, but still error persists. ☹ 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. ---
**Author:** ![Clarion\_clarion](https://clarionhub.com/user_avatar/clarionhub.com/clarion_clarion/32/636_2.png) [@Clarion\_clarion](https://clarionhub.com/u/Clarion_clarion)\ **Post date:** [February 26, 2021, 11:51am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/4 "2021-02-26T11:51:48Z")
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` ---
**Author:** ![FoucheRabie](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/7cd45c/32.png) [@FoucheRabie](https://clarionhub.com/u/FoucheRabie)\ **Post date:** [February 26, 2021, 12:40pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/5 "2021-02-26T12:40:57Z")
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. 🤔 ---
**Author:** ![Clarion\_clarion](https://clarionhub.com/user_avatar/clarionhub.com/clarion_clarion/32/636_2.png) [@Clarion\_clarion](https://clarionhub.com/u/Clarion_clarion)\ **Post date:** [February 26, 2021, 1:09pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/6 "2021-02-26T13:09:20Z")
try to add `/IGNORETRUNCATION=TRUE` to your DRIVER Options for that table ---
**Author:** ![Richard](https://clarionhub.com/user_avatar/clarionhub.com/richard/32/368_2.png) [@Richard](https://clarionhub.com/u/Richard)\ **Post date:** [March 2, 2021, 5:22am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/7 "2021-03-02T05:22:19Z")
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 ---
**Author:** ![FoucheRabie](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/7cd45c/32.png) [@FoucheRabie](https://clarionhub.com/u/FoucheRabie)\ **Post date:** [March 2, 2021, 3:38pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/8 "2021-03-02T15:38:59Z")
Thank you all for responding. Tried: “/IGNORETRUNCATION=TRUE”, still an issue. 😭 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. 😕 but 😄 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) ---
**Author:** ![Richard](https://clarionhub.com/user_avatar/clarionhub.com/richard/32/368_2.png) [@Richard](https://clarionhub.com/u/Richard)\ **Post date:** [March 2, 2021, 9:56pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/9 "2021-03-02T21:56:18Z")
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. Richard ---
**Author:** ![FoucheRabie](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/7cd45c/32.png) [@FoucheRabie](https://clarionhub.com/u/FoucheRabie)\ **Post date:** [August 30, 2022, 4:11pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/10 "2022-08-30T16:11:34Z")
I had to revisit this procedure. Same error. Digging and digging I found that I actually had a miss matched field. 🤨 🙈 Quite embarrassing. But lesson learned-be thorough. All is well! A clear vote of confidence goes to Clarion dictionary import! ---
**Author:** ![anon23294430](https://clarionhub.com/user_avatar/clarionhub.com/anon23294430/32/3829_2.png) [@anon23294430](https://clarionhub.com/u/anon23294430)\ **Post date:** [August 30, 2022, 4:33pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/11 "2022-08-30T16:33:49Z")
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. fwiw. ---
**Author:** ![vitesse](https://clarionhub.com/user_avatar/clarionhub.com/vitesse/32/837_2.png) [@vitesse](https://clarionhub.com/u/vitesse)\ **Post date:** [August 31, 2022, 4:18am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/12 "2022-08-31T04:18:06Z")
> [@anon23294430](#): > > 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: > [@What causes an Error 78 and/or sometimes "phantom" records in a browse?](https://clarionhub.com/t/what-causes-an-error-78-and-or-sometimes-phantom-records-in-a-browse/999): > > Here is a reproduction of my old PTSS report. @Mark_Sarson might be able to describe the situation he found today with phantom records due to the same issue? Clarion 8 PTSS - [Problem-Tracker Support System](http://problemtracker.softvelocity.com/clarion8/UpdtCustBugsview.php?ID1=39361) Clarion 9 PTSS - [Problem-Tracker Support System](http://problemtracker.softvelocity.com/clarion9/UpdtCustBugsview.php?ID1=39361) Problem Reference: 39361 Date Received: 2012-05-30 Status: Opened for Review Fixed in Build Number: Product: Clarion8 Build No: 89285 Operating System: Category ID: ABC Classes Summary: Variables used with return value from Position(… ---
**Author:** ![anon23294430](https://clarionhub.com/user_avatar/clarionhub.com/anon23294430/32/3829_2.png) [@anon23294430](https://clarionhub.com/u/anon23294430)\ **Post date:** [August 31, 2022, 9:18am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/13 "2022-08-31T09:18:16Z")
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](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16) > 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)](https://blogs.oracle.com/sql/post/how-to-fix-ora-01450-maximum-key-length-6398-exceeded-errors) 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. ---
**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\ **Post date:** [August 31, 2022, 1:58pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/14 "2022-08-31T13:58:48Z")
> [@vitesse](#): > > I wouldn’t use a CSTRING in a key (because I would be nervous about crud after the terminating null) 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. ---
**Author:** ![vitesse](https://clarionhub.com/user_avatar/clarionhub.com/vitesse/32/837_2.png) [@vitesse](https://clarionhub.com/u/vitesse)\ **Post date:** [September 1, 2022, 1:44am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/15 "2022-09-01T01:44:28Z")
> [@anon23294430](#): > > 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 > [@Rick\_UpperPark](#): > > The column in SQL is a varchar. SQL handles using that as an index very well. I have 100s of indexes on varchar columns. 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\>. ---
**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\ **Post date:** [September 1, 2022, 5:35am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/16 "2022-09-01T05:35:12Z")
CLEAR(MyCString,-1) will set all characters to \<0\> ---
**Author:** ![anon23294430](https://clarionhub.com/user_avatar/clarionhub.com/anon23294430/32/3829_2.png) [@anon23294430](https://clarionhub.com/u/anon23294430)\ **Post date:** [September 1, 2022, 6:21am UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/17 "2022-09-01T06:21:54Z")
> [@jslarve](#): > > CLEAR(MyCString,-1) 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. ---
**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\ **Post date:** [September 1, 2022, 5:22pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/18 "2022-09-01T17:22:08Z")
Hmmm… Not happening here in C10 either. I am pretty dang sure this was working at some point. Perhaps C6? ---
**Author:** ![anon23294430](https://clarionhub.com/user_avatar/clarionhub.com/anon23294430/32/3829_2.png) [@anon23294430](https://clarionhub.com/u/anon23294430)\ **Post date:** [September 1, 2022, 6:02pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/19 "2022-09-01T18:02:34Z")
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. 🕵️‍♂️ ---
**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\ **Post date:** [September 1, 2022, 11:19pm UTC](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894/20 "2022-09-01T23:19:59Z")
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. [Next page](https://clarionhub.com/t/sql-error-string-data-right-truncation-invalid-character-value-for-cast-specifications-01004/3894.md?page=2)