Skip to main content
A
Arny-jWJb8P
Participant
October 12, 2006
Question

Error Message 22005

  • October 12, 2006
  • 3 replies
  • 691 views
I get the following error when trying to access my .cfm page using a stored procedure on MSSQL.


Here is the error message:

Error Diagnostic Information
ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

SQL = "sp_SearchResult"

Data Source = "artSql"

The error occurred while processing an element with a general identifier of (CFSTOREDPROC),
occupying document position (119:1) to (119:147) in the template file D:\HTML\USERS\mysite\HTML\RESULTS_
ADV2.CFM.

Line 119 of the RESULTS_ADV2.CFM file states:

<cfstoredproc procedure="sp_SearchResult" datasource="#MM_connArtSql_DSN#" username="#MM_connArtSql_
USERNAME#" password="#MM_connArtSql_PASSWORD#">

I have a feeling it does not have anything to do with this line, but something else in the code. I
am looking for someone to help us out off forum, and to help us implement other coldfusion projects
on our site. Any help appreciated.

Paul
    This topic has been closed for replies.

    3 replies

    A
    Anonymous
    October 14, 2006
    What version of SQL server are you using?

    Why are you using the ODBC drivers? That can cause this problem.

    In the CF Administrator, change the datasource to use the JDBC driver.

    A
    Anonymous
    October 14, 2006
    Paul,

    That's a lot of code and there could be a number of issues going on. Using an JDBC driver straight to SQL Server is definitely preferred, the ODBC should (in theory) still work even if it's not as efficient.

    Because your code is so large and extensive I think you need to isolate things to the stored proc call alone. Some of the code I see doesn't quite make sense to me like:

    <cfif isDefined("subject") And subject eq -1>
    <cfset subject = -1>
    </cfif>

    If the variable is already defined an infact equal to -1, why is it being set to negative -1 or should that be a neq?? So I think you've got some potential logic issues which is why someone was suggesting that you output the values of your call.

    Another way of doing this is to create a test page w/ nothing but the cfstoredproc call. Copy and paste the code you've got and remove the if/else statements in between the procparams and hard-code the actual values w/ what you believe is the data that should be going through. Since your params are nothing by strings and numbers, this shouldn't be an issue. By doing this you rule out any potential errors in application logic. Also you can setup a second datasource that uses a JDBC instead of ODBC driver to see if there's any difference in the results. If you can't get the hard-coded stored proc call to work, you've got to resolve that before you deal w/ it in the context of your application.

    -Tim
    A
    Anonymous
    October 13, 2006
    Paul,

    It sounds like the data types CF is passing into the stored proc are not what SQL Server expects. Are there parameters being passed in? I'm guessing they are below the initial cfprocedure tag. Also what datatypes are the expected for the stored proc?

    Another possiblity is that the parameters you're passing aren't being properly converted by CF itself. That error gives me the impression that you could be trying to cast input parameters with cfprocparam and that's not working out. I think the answer lies somewhere between those two possibilities. Take a closer looker at what you're passing in; how it's passed in and what's expected by the DB.


    -Tim
    A
    Arny-jWJb8PAuthor
    Participant
    October 13, 2006
    Code is attached. I know some code is not related, just wanted to have it all there in case one part was affecting the other.

    Paul
    C
    cf_dev2
    Inspiring
    October 13, 2006
    The stored procedure has a large number of parameters. Usually, fewer is better and also easier to debug. I assume you already tested the stored procedure extensively from ISQL?

    I can only guess about error cause without knowing the MSSQL procedure parameter declarations AND the actual values passed to the cfprocparam tags. Like cecropin said its likely to be some sort of conversion problem.

    Try outputting the actual values causing the error and look for obvious things like

    * Are the cfprocparams in the right order?
    * Do the cfsqltypes match the datatypes of the corresponding stored procedure parameters
    * Are you passing in acceptible values for the declared cfsqltypes
    "ABC" is not a valid cfsqltype="CF_SQL_INTEGER"
    * Are any there any problems with value formats or single quotes?

    C
    cf_dev2
    Inspiring
    October 13, 2006
    Did you test the stored procedure in MSSQL?

    It would be easier for someone to help if you posted the full cfstoredproc block and the search values causing the error.
    <cfstoredproc procedure="sp_SearchResult" ...>
    <cfprocparam ...>
    <cfprocparam ...>
    </cfstoredproc>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices