Just use one CFQUERY tag for the whole transaction. <cfquery> ...
exec sproc_1 arg1 arg2 exec sproc_2 arg1 ... </cfquery> You don't need more than one cfquery tag. I don't know where the idea began that you should use one query tag per query, it's just not so. - Daniel Elmore -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mukesh Saxena Sent: Wednesday, October 27, 2004 5:04 PM To: [EMAIL PROTECTED] Subject: RE: Begin/Commit Database Transaction I tried this by setting appropriate ISOLATION LEVEL. It gives me following error: "A nested CFQUERY tag has been encountered at document position (1134:5) to (1135:57). CFQuery tags cannot be nested inside one another." This is due to my "EXEC stored procs" called within a loop are again CFQuery to a given database. I usually do not write Select/Update/Delete statements within <CFQuery> .... </CFQuery>, rather calling pre-complied stored procs with CFQuery. --Mukesh -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Elmore Sent: Wednesday, October 27, 2004 6:07 AM To: [EMAIL PROTECTED] Subject: RE: Begin/Commit Database Transaction Yes I would fear a database deadlock most of all. If one occurs, you will only know about it if kills this process. You greatly reduce the risk of deadlocking if you have been accessing your tables within your work units in the same order. I think that would be worth examining, but I don't know what your traffic load is for this application. If you have poorly structured procs in multi-proc transactions, add a little load (accessing the affected tables in some way) and your almost guaranteed a deadlock. What are your isolation level needs? If you can safely go down to read committed (or possibly read uncommitted) you may not need to worry as much about the above clause. That said, I would perform this transaction like so: <cfquery> SET TRANSACTION ISOLATION LEVEL ... BEGIN TRAN <loop over form fields> <loop deciding with sp's to run> EXEC stored procs </loop> </loop> IF @@ERROR <> 0 ROLLBACK TRAN ELSE COMMIT TRAN </cfquery> -Daniel Elmore -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mukesh Sent: Wednesday, October 27, 2004 1:26 AM To: [EMAIL PROTECTED] Subject: Begin/Commit Database Transaction I am working with CF (5.0) & MS SQL 2000 and have specific need as follows: Processing a multiline form on processing page in loop. Each line involves more than one database Stored Procedure (SP) calls. There could be a chance of database lock or other possible database error while processing. So, wanted to execute a "begin transaction" before a loop processing starts. And once all lines are processed a "commit transaction" or "rollback transaction" can be called depending on error state. My problem is: SQL SP expects Begin/Commit/Rollback in the same SP. Addionally, I have explored a CF tag CFTransaction also which requires to insert a code for CFCatch after every SP calls used for line processing. I appreciate feedback from this forum. Thanks, Mukesh ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
