ColdFusion 6.1: Stored Procedure Not Found Error

[MindCrafter]

Okay all,

I have a web page which uses a stored procedure to insert form data into a SQL 2000 Db. The StoredProc uses a ColdFusion DSN.

Code Snippet:
Code:

<cftry>
<cfstoredproc debug="no"
procedure="::SP_Name::"
returncode="yes"
datasource="::DSN Name::"
username="::CFM Logon Id::"
password="::Password::">
<cfprocparam cfsqltype="cf_sql_varchar" type="in" value="#FORM.txtFName#" dbvarname="@FNAME">
<cfprocparam cfsqltype="cf_sql_varchar" type="in" value="#FORM.txtLName#" dbvarname="@LNAME">
<cfprocparam cfsqltype="cf_sql_varchar" type="in" value="#FORM.txtEmail#" dbvarname="@@EMAIL">
<!--- Rest of the input Params --->

<cfprocparam cfsqltype="cf_sql_integer" type="Out" variable="NewPIN" dbvarname="@NewPIN">
<cfprocresult name="rsInsertData" resultset="1">
</cfstoredproc>
<cfcatch type="database">
<cfif cfcatch.Detail neq "">
<cfset NewPIN=0>
<!-- Code to handle error and send error message to end user --->
</cfif>
</cfcatch>
</cftry>
<cfif NewPIN neq 0>
<!-- Code that completes processing the form data and displays confirmation message to end user -->
</cfif>


When I run the code on my development server I have no problems. After I upload the page to the ISP's web server the below error is returned.
Code:

Catch Stmts Returned Values

cfcatch.Type Database
cfcatch.NativeErrorCode 2812
cfcatch.Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'spAddUserAccount'.
cfcatch.Message Error Executing Database Query.
cfcatch.SQLState HY000


If I run the code using the default SQL user account the ISP set up which is also the DB Owner, the code works. As it is very dangerous to use any account with owner privs for any production application, I had the ISP create a second user for my Db. This second user is in both DBReader and DBWriters roles with specific permission to execute the stored procedure.

I have also had the ISP varify that the DSN used does have permission to execute Stored procedures.
As for the stored procedure, I have successfully tested it in both classic ASP and ASP.net versions of the web form using the second User account.

I'm confused as to why the error is being generated and how to resolve the issue.

MindCrafter
www.MindCrafter.com

[mindlessLemming]

Hey :)
I think you'll find that there aren't many CFMX programmers round these parts..
I know this because when I was studying CFMX none of my questions ever got a single response. :(
I recommend you join up to the cfaussie (http://lists.daemon.com.au/cgi-bin/lyris.pl?enter=cfaussie&text_mode=0&lang=english) mailing list, as I know it is very active and the members are quite helpful (regardless of what country you're in)
Good luck :thumbsup:

[oracleguy]

Hey :)
I think you'll find that there aren't many CFMX programmers round these parts..
I know this because when I was studying CFMX none of my questions ever got a single response. :(
I recommend you join up to the cfaussie (http://lists.daemon.com.au/cgi-bin/lyris.pl?enter=cfaussie&text_mode=0&lang=english) mailing list, as I know it is very active and the members are quite helpful (regardless of what country you're in)
Good luck :thumbsup:

Yeah... I think I'm one of the few regulars on here who have been cursed by ColdConFusion.

I haven't had your problem exactly, but the variant was that it would run the SP but would never return anything, even though the SP itself was returning stuff. ColdFusion has a lot of little weird quirks, and this is one of them. Have you tried creating a new SP with a different name and the same code?