I have a stored procedure in SQL Server 2005 which takes two input
parameters, one of which is a varchar(max) data type. When I try to
call the procedure with <cfstoredproc>, I get an error "Operand type
clash: text is incompatible with int ". If I use <cfquery> to execute
the procedure (using cfqueryparams), it runs just fine. I'm assuming
it's something with the varchar(max) datatype. Any ideas what it
could be? Searching hasn't resulted in any issues.
Here's the signature of the procedure:
CREATE PROCEDURE dbo.prc_MyProc
@affiliateID INTEGER,
@xmlText VARCHAR(MAX)
AS
BEGIN
....
END
Here's the <cfquery> which works:
<cfquery name="local.qResults" datasource="#this.dsn#">
EXECUTE dbo.prc_myProc
<cfqueryparam value="#arguments.affiliateID#"
cfsqltype="cf_sql_integer" />,
<cfqueryparam value="#arguments.xmlText#"
cfsqltype="cf_sql_clob" />
</cfquery>
And here's the storedproc which doesn't:
<cfstoredproc procedure="prc_myProc" datasource="#this.dsn#"
result="local.qResults">
<cfprocparam dbvarname="@affiliateID"
variable="#val(arguments.affiliateID)#" cfsqltype="CF_SQL_INTEGER" />
<cfprocparam dbvarname="@xmlText" value="#arguments.xmlText#"
cfsqltype="CF_SQL_CLOB" />
</cfstoredproc>
(and, sure, I could use <cfquery> to do it, but I prefer
<cfstoredproc> to call procedures)
Scott
--
-----------------------------------------
Scott Brady
http://www.scottbrady.net/
----------------
Now playing on iTunes: Phil Collins - Take Me Home
http://foxytunes.com/artist/phil+collins/track/take+me+home
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309185
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
