Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
SteveEly Absent Member.
Absent Member.
4056 views

AcuSQL error "Invalid character value for cast specification" after switching machines.

Jump to solution

We have a program that reads a text file and uses AcuSQL to insert the data into an MS-SQL table. The program has been working for years. i runs on a seperate computer from where the SQL table resides. The PC on which the program runs died yesterday and a new PC was installed.

Since the program has been running on the new PC we get the following error on each insert:

SQLCODE: 000000001   SQLSTATE: 22018   Invalid character value for cast specification

Most fields in the insert are Char or varchar and are fed a pic x.

But I also have the following:
a smallint that is fed a s9(9) comp-5
a smalldate that is fed a pic x(10)   (Filled with a date formatted mm/dd/yyyy)
a smallmoney that is fed a pic 9(4)v99.

As I said this was working fine on the old PC. Any ideas would be appreciated.

0 Likes
1 Solution

Accepted Solutions
SteveEly Absent Member.
Absent Member.

RE: AcuSQL error "Invalid character value for cast specification" after switching machines.

Jump to solution

Through experimentation  I discovered it was the smalldatetime data type that was the problem. When I format the insert as YYYY-MM-DD 00:00:00 the insert succeeds. I suppose it may be the difference in the driver that requires the additional precision but it works now so I will move on to something else.

Thanks for your help.

View solution in original post

0 Likes
5 Replies
Micro Focus Expert
Micro Focus Expert

RE: AcuSQL error "Invalid character value for cast specification" after switching machines.

Jump to solution

Which SQL Server driver is listed in your ODBC Data Source Administrator / Driver? It is possible you have a different SQL Server driver than previously?

0 Likes
Micro Focus Expert
Micro Focus Expert

RE: AcuSQL error "Invalid character value for cast specification" after switching machines.

Jump to solution

For version 9.2 and later .. There is a new configuration variable, ASQL-ODBC-DRIVER-NAME. The

default value of this variable is "SQL Server" (which is the ODBC driver we have been using for years). You can change this to any value. However, if you change it to a value which is not an ODBC

driver for SQL Server on your system, the results are undefined.

Supported values (at the time of this writing) are

 SQL Server

 SQL Server Native Client 10.0

0 Likes
SteveEly Absent Member.
Absent Member.

RE: AcuSQL error "Invalid character value for cast specification" after switching machines.

Jump to solution

We have sort of used AcuSQL "out of the box" without any ODBC configuration. We are still able to read the tables in the database just fine. We only encounter this error on an insert.

0 Likes
Micro Focus Expert
Micro Focus Expert

RE: AcuSQL error "Invalid character value for cast specification" after switching machines.

Jump to solution

My guess is that the new PC has SQL Server Native Client 10.0 or the SQL Server Native Client 11.0 , and if you install or use the basic SQL Server 10.0 or 11.0 driver the inserts will work as they did in the past.

0 Likes
SteveEly Absent Member.
Absent Member.

RE: AcuSQL error "Invalid character value for cast specification" after switching machines.

Jump to solution

Through experimentation  I discovered it was the smalldatetime data type that was the problem. When I format the insert as YYYY-MM-DD 00:00:00 the insert succeeds. I suppose it may be the difference in the driver that requires the additional precision but it works now so I will move on to something else.

Thanks for your help.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.