Search code examples
sqlsql-serversql-server-2008coldfusioncoldfusion-9

Coldfusion SQL Server giving The hexadecimal string is invalid

I am getting this error - 

[Macromedia][SQLServer JDBC Driver]The hexadecimal string is invalid

when I run the following query - 

<cfquery datasource="#getDatasource()#" name="qryAdminActivity">
    select * from v2_admin_activity
      where adminId = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.adminId#"> 
</cfquery>

The Row in question in the database is looks like this

enter image description here

Anyone has any idea what is wrong here?

::::EDIT::::

I found that the problem is with this string in the database - 

&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt; &lt;activities&gt;&lt;activity&gt;&lt;date&gt;{ts &apos;2013-02-19 16:36:43&apos;}&lt;/date&gt;&lt;query&gt;Test,,,1,2013-02-19 16:36:43.625,1&lt;/query&gt;&lt;className&gt;moduleDAO&lt;/className&gt;&lt;functionName&gt;addNewSubModule&lt;/functionName&gt;&lt;type&gt;Add&lt;/type&gt;&lt;/activity&gt;&lt;/activities&gt;

Now could anyobe tell me what is wrong with this?

The column type in database is varchar(max)

Solution

  • Is this error occurring on the query or on the display of the results? How are you displaying the output from that query? Is it trying to parse the XML in your result? Is that XML valid? I've found that storing XML in SQL Server as anything other than the XML datatype can sometimes cause issues when trying to treat it as XML again. I guess it would depend on how you're ultimately using that XML string.

    I would recommend working through each of your dynamic CF variables and replacing them with a valid hard-coded value until you can narrow it down to the one causing the problem. And try replacing that xml in the database with another value. It looks like it might be trying to parse that varchar(max) as XML.