[cfaussie] RE: Slow insert with SP ???

[Taco Fleur]

Title: Message

Morning!   -------------------------- Do you have the cfstoredproc's cfprocparam set to something like this:   <cfprocparam type="out" variable="myVar" (careful not to use value="myVar", although I would expect the compiler to pick that one up.) cfsqltype="CF_SQL_INTEGER" dbvarname="@newThreadID" null="yes" > -------------------------- That is correct I am using cfstoredproc, following is the code:   <cfif NOT len(variables.error)>   <cfstoredproc procedure="spForumThreadInsert" datasource="#request.mainDSN#">    <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#val(url.category)#" maxlength="10" null="no">    <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.strHeader#" maxlength="200" null="no">    <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#form.strBody#" maxlength="8000" null="no">    <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#val(client.someoneID)#" maxlength="10" null="no">    <cfprocparam type="In" cfsqltype="CF_SQL_TIMESTAMP" null="yes">    <cfprocparam type="In" cfsqltype="CF_SQL_TIMESTAMP" null="yes">    <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="request.threadID" dbvarname="@newThreadID" null="no">    <cfprocparam type="Out" cfsqltype="CF_SQL_TINYINT" variable="variables.bApprovalRequired" dbvarname="@bApprovalRequired" null="no">    <cfprocparam type="Out" cfsqltype="CF_SQL_BIT" variable="variables.bError" dbvarname="@bError" null="no">   </cfstoredproc>   <!------------------------------------------------------------------   - Check if the insert was successfull, and if approval is required   - before this thread shows up   ------------------------------------------------------------------>   <cfif NOT variables.bError AND NOT variables.bApprovalRequired>    <cflocation url="" addtoken="no">   <cfelseif variables.bError>    <cfset variables.error = variables.error & "There was an error trying to insert your thread.<br>">   <cfelseif variables.bApprovalRequired>    <cflocation url="" addtoken="no">   </cfif>  <cfelse>   <!------------------------------------------------------------------   - Make sure we return in new thread mode again   ------------------------------------------------------------------>   <cfset form.btnNewThread = true>  </cfif>     -------------------------- I wouldn't mind changing this part of your proc just to be sure of whats returned (if anything):    IF NOT (@@ERROR = 0)  BEGIN   SET @bError = 1  END  SET @newThreadID = SCOPE_IDENTITY()   Changed to:   IF @@ERROR <> 0     BEGIN        SET @newThreadID = -1         RETURN @@ERROR     END ELSE     BEGIN         SET @newThreadID = SCOPE_IDENTITY()     END   The reason I want to change this is if an error did occur above the next part of your proc would still execute and not return an identity value. To use the above code you need to enable the return code in the cfstoredproc and catch it using using the #cfstoredproc.statuscode#. --------------------------   I am actually returning the variable bError to the webpage, if a error occured it would notify the client and return to the page of the insert. So I guess it's really the same as changing it to the above code, right?   --------------------------   PS. This is nothing to do with the problem your having, but be carefull with your rows sizes (Bytes per row). SQL Server can hold 8060 bytes the rest that makes the 8192 bytes is SQL's overhead (header information). If a user enters a thread that has a combined column size greater than 8060 bytes, your insert will fall over.   -------------------------- Do you mean that even though I specify maxlength="8000" the data could be larger? I'm not sure if thats what you mean as its still early in the morning and haven't had a coffee yet ;-)   PS. the insert page is located at http://www.coldfusionist.com/forum/dsp_category.cfm?category=15 if you would want to test it, you can login with test123 and test123 on http://www.coldfusionist.com/forum/dsp_login.cfm   Thanks, Taco   --- You are currently subscribed to cfaussie as: archive@mail-archive.com To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/