Skip to main content

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.

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.

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?

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

Terms & ConditionsAccessibility statement