Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jun 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ColdFusion 6.1: Stored Procedure Not Found Error

    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:
    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:
    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

  • #2
    Senior Coder
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    1,963
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 mailing list, as I know it is very active and the members are quite helpful (regardless of what country you're in)
    Good luck

    I take no responsibility for the above nonsense.


    Left Justified

  • #3
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by 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 mailing list, as I know it is very active and the members are quite helpful (regardless of what country you're in)
    Good luck
    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?
    OracleGuy


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •