BUG: Error 22018 "Invalid character value for cast specification" with SQL Server 6.5

Article translations Article translations
Article ID: 269011 - View products that this article applies to.
This article was previously published under Q269011
Expand all | Collapse all

On This Page

SYMPTOMS

When an ODBC API function such as SQLExecute calls a stored procedure that has a call to sp_getbindtoken or that fires a trigger that has a call to sp_getbindtoken, you may see the following error message:
DIAG [22018] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (0)
SQLExecute returns SQL_SUCCESS_WITH_INFO but reports the above error.

WORKAROUND

Remove the call to the sp_getbindtoken system stored procedure, or eliminate the output parameters.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5.

This error does not occur with SQL Server 7.0 or SQL Server 2000.

MORE INFORMATION

This error occurs only with SQL Server 6.5, including Service Packs 1 through 5a. This error does not occur when the stored procedure is called from isql or has no output parameters.

When called from an ODBC application, the stored procedure execution returns a return value indicating success, but it generates this error and does not pass back the output parameters. The ODBC API call SQLExecute returns SQL_SUCCESS_WITH_INFO and the above error message.

Steps to Reproduce Behavior

  1. Create a table, a stored procedure, and a trigger in the Pubs database using the following SQL script:
    create table emp_table (emp_id int primary key,
                            emp_name varchar(80) not null )
    go
    
    create trigger TR_emp_table_ins on emp_table
    for insert as
    begin
        declare @token_value varchar(255)
        exec master..sp_getbindtoken @token_value output
    end
    go
    
    create procedure test_proc_one
        @return_code int output,
        @error_code int output
    as
    begin
        declare @token_value varchar(255)
        declare @max_emp_id int
    
        select @return_code = 13
        begin tran
        select @max_emp_id = max(emp_id) from emp_table
    
        if @max_emp_id is null
        begin
            select @max_emp_id = 1
        end
        else
        begin
            select @max_emp_id = @max_emp_id + 1
        end
    
        insert into emp_table values(@max_emp_id, 'John Doe')
    
        if @@error <> 0
        begin
            raiserror('Insert into employee_list failed for emp_id %d', 16, -1, @max_emp_id)
            rollback tran
            select @return_code = -100
            select @error_code = -200
        end
        else
        begin
            commit tran
            select @return_code = 0
            select @error_code = 2
        end
    
    end /* procedure test_proc_one */ 
    go
    					
  2. Run the following SQL script inside isql. It should be successful and insert a row into the test table, emp_table:
    declare @ret_value int
    declare @err_code int
    declare @string varchar(255)
    
    select @ret_value = 10
    select @err_code = 20
    
    exec test_proc_one @ret_value output, @err_code output
    
    select @string = 'test_proc_one set ret_value to ' + rtrim(convert(varchar(16), @ret_value))
                     + ' and err_code to ' + rtrim(convert(varchar(16), @err_code))
    
    print @string
    
    select * from emp_table
    					
  3. Call the same stored procedure from an ODBC application using the following sample code. The same error can also be reproduced using the ODBC Test tool by calling the shown functions in the same sequence.
    	SQLRETURN rc;
    	SQLCHAR*  SQLStr = "{call test_proc_one(?,?)}";
       	SQLINTEGER nReturnCode = SQL_NTS;
    	SQLINTEGER nErrorCode = SQL_NTS;
    	SQLINTEGER cbReturnCode = SQL_NTS;
    	SQLINTEGER cbErrorCode = SQL_NTS;
    
    	/* Prepare the statement, bind parameters, and execute the statement */ 
    	rc = SQLPrepare(hStmt, SQLStr, SQL_NTS);
    	rc = SQLBindParameter(hStmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 
    		                    0, 0, &nReturnCode, 0, &cbReturnCode);
    	rc = SQLBindParameter(hStmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER,
    		                    0, 0, &nErrorCode, 0, &cbErrorCode);
    	rc = SQLExecute(hStmt);
    
    	do {
    	   rc = SQLMoreResults(hStmt);
    	} while (rc != SQL_NO_DATA);
    					
  4. The above step results in the SQL state 22018 and "Invalid character value for cast specification" message with a return code of SQL_SUCCESS_WITH_INFO. Following is a sample ODBC trace log:
    mssamp          163:166	EXIT  SQLExecute  with return code 1 (SQL_SUCCESS_WITH_INFO)
    		HSTMT               0x00cc2158
    
    		DIAG [22018] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (0) 
    					

Properties

Article ID: 269011 - Last Review: June 5, 2007 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
Keywords: 
kbbug kbdatabase kbpending KB269011

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com