[thelist] CF/SQL Server 2k Stored Procedures
Joshua Olson
joshua at waetech.com
Mon Oct 14 22:30:01 CDT 2002
Hi Gang,
I recently upgraded a site from a Sybase Database to SQL Server 2k. I used
a number of stored procedures and called these stored procedures using
<cfstoredproc>. After upgrading the SQL Server 2k I noticed my code broke.
The stored procedures suddenly because very sensitive to missing attributes
and the ordering of the attributes. For example:
CREATE PROCEDURE sp_myproc
@mystring varchar(100) = null,
@mynumber int = null
AS
--Do something cool here, like return a resultset
GO
The following code would fail:
<cfstoredproc procedure="sp_myproc" datasource="myDS">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@mynumber"
value="1234" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@mystring"
value="foo" null="No">
<cfprocresult name="get_things">
</cfstoredproc>
It would fail until I reversed the order of the cfprocparam tags, making
this:
<cfstoredproc procedure="sp_myproc" datasource="myDS">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@mystring"
value="foo" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@mynumber"
value="1234" null="No">
<cfprocresult name="get_things">
</cfstoredproc>
Does anybody know why the interface between Cold Fusion and MS SQL Server 2k
is so sensitive when calling Stored Procedures? Is there some way to fix
this annoyance?
TIA,
-joshua
More information about the thelist
mailing list