C++ SQLState: 22005 [Microsoft][SQL Native Client]Invalid character value for cast specification

Question

/*
Problem with the following error:
SQLState: 22005
ErrorCode: 0
Message: [Microsoft][SQL Native Client]Invalid character value for cast specification

I have a similar yet different issue to many of the others I've seen with this error.
I am calling an SQL stored procedure from a C++ program. I pass the data (ASCII text) to the stored
procedure via ODBC. I do NO DATA CONVERSION, in order to avoid any datatype issues.
All data is passed as varchar and the stored procedure reads in the parameters as varchar.
I have done over 100 of these programs in exactly the same manner with no problem, but for some reason, have now encountered this error.
As I am not trying to cast any data to anything, I cannot work out why I am getting this error and more importantly, cannot get rid of it.
We are using:
Microsoft Visual Studio 2010 Ultimate, version 10.0.30319.1
on a 64 bit server.
We are running: Microsoft SQL Server 2005 - 9.00.4262.00 (X64)  
 Aug 13 2009 17:06:39   Copyright (c) 1988-2005 Microsoft Corporation 
 Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

The following code is from the C++ program. This prepares the statement and binds the parameters to it.
*/
void sqlprep::PrepInsert()
{
 cb4CHR = SQL_NTS;
 STind = 0;

 // set query timeout to be 30 seconds
 UDWORD Timeout = 30;
 if (SQLSetConnectOption(hdbc1,SQL_QUERY_TIMEOUT,Timeout) != SQL_SUCCESS)
 {
  fprintf(Msgfile,"PrepInsert: Failed to set Query Timeout.\n");
  Error_Dump(1); // if not possible, display error message, exit program
 }

 SQLAllocStmt(hdbc1, &hstmt[STind]); // allocate ODBC statement handle
 // Prepare the SQL statement to be executed
 if ( SQLPrepare(hstmt[STind], (SQLCHAR *)"{CALL P_INS_XXXXX \
 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,\
 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,\
 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,\
 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,\
 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,\
 ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}", SQL_NTS) != SQL_SUCCESS)
 {
  fputs("** ERROR preparing INSERT: ",Msgfile);
  Error_Dump(1);
 }

 for (int i = 0, j = 1; i < FLDS; i++, j++)
 { //fields and lengths are arrays of the length of each field and the field string itself
  if (SQLBindParameter(hstmt[STind], j, SQL_PARAM_INPUT, SQL_C_CHAR, \
  SQL_VARCHAR, lengths[i] - 1, 0, fields[i], lengths[i], &cb4CHR) != SQL_SUCCESS)
  {
   fprintf(Msgfile,"** ERROR cannot bind parameter: %s\n",fields[i]);
   Error_Dump(1);
  }
 }
}

//The following executes the statement prepared above, this is where we get the error.
int sqlprep::ExInsert()
{
 STind = 0;
 if (SQLExecute(hstmt[STind]) != SQL_SUCCESS)
 {
  Bad_Cnt++;
  fprintf(Msgfile,"** Record # %d - ",Recs_read);
  Message_Dump(1);
  if (SQLerr == INTEGRITY_VIOLATION)
   Dup_Cnt++;
  return(1);
 }
 else
  Recs_writ++;
 return(0);
}

/*
The stored procedure is simply as follows.
Given the problems I have had getting this particular case to work, I have disabled the stored proc
by simply reading in the parameters and then returning, doing nothing. Even with this, I still get the error.
Therefore I am at a loss as to where the CAST is occurring and how I can avoid it.
*/
CREATE PROCEDURE dbo.P_INS_XXXXX
(
 @BUSINESS_DATE varchar(8),
 @DATE_ADDED  varchar(5),
 @DATE_CHANGED varchar(5),
 @TIME_CHANGED varchar(6),
 ........(the rest of the parameters)
)
as
return;

Answer 1

I would start from eliminating parameter one by one until you no longer get an error. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Naomi, thanks for your reply.

As I started my process of elimination, I saw that next to the 25th parameter on the 1st line of "?,?,?"; there was a "1". (you can see it above)

THAT was the problem. I have been scratching my head trying to figure this out, when all the time it was a simple extra character where it shouldn't have been, that was causing the problem.

When in doubt, consider the simplest solution.