Re: SQL 7 Question
I set SET NOCOUNT ON/OFF outside the BEGIN/COMMIT TRANSACTION and it seems to work fine. Thanks! best, paul At 10:54 AM 5/10/01 +0700, you wrote: * Team Allaire * Thanks! Now that you mention it I did had BEGIN TRANSACTION/COMMIT TRANSACTION in another version, but I guess it fell down the proverbial crack in this developing application. (Probably a bad copy/paste ;-) if you shove a bunch of TSQL statements into one cfquery you have to enclose them all in SET NOCOUNT ON/OFF block to prevent cf from thinking its getting valid resultsets everytime sql server sends back x rows selected messages. in fact, for most sp its a good idea to shave a few ms here there... ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL 7 Question
The dbms needs to support it, but yeah it's known. I guess the only limit would be the amount of time you want that connection open to the database server. It is not the equivalent to a transaction. I thought so also until Dave Watts corrected me a little while ago ;-) Each different command between the cfquery tags is it's own seperate transaction. Same as in a stored procedure. You have (I'm almost sure) three transactions in the below statement. In order to make all of the below statements a single transaction, put BEGIN TRANSACTION at the beginning, and COMMIT TRANSACTION at the end. As you have it now, two concurrent users could execute the cfquery at the same time, and potentially get unpredictable results. What happens if they both hit it at the same time and the two updates run before either of the selects? @max is probabaly going to be off... jon - Original Message - From: Paul Smith [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 9:36 PM Subject: SQL 7 Question I've been putting one SQL statement after another like the following inside a CFQUERY for a while. Seems to work fine. UPDATE MAX_ID_Clients SET MAX_ID_Client = MAX_ID_Client + 1 DECLARE @MAX INT SELECT @MAX = MAX_ID_Client FROM MAX_ID_Clients INSERT INTO ClientData(CFID,CFTOKEN) VALUES(@MAX,#CFT#) Is this a known feature? Is there a limit to how many SQL statements can be combined this way? I assume the above is equivalent to a CFTRANSACTION. Is this so? best, paul ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL 7 Question
Thanks! Now that you mention it I did had BEGIN TRANSACTION/COMMIT TRANSACTION in another version, but I guess it fell down the proverbial crack in this developing application. (Probably a bad copy/paste ;-) I happen to have the SQL below within a CFTRANSACTION of 3 CFQUERIES. They take zip time so I guess I should move all SQL inside one CFQUERY and use the DB BEGIN/COMMIT TRANSACTION instead. best, paul At 10:36 PM 5/9/01 -0400, you wrote: The dbms needs to support it, but yeah it's known. I guess the only limit would be the amount of time you want that connection open to the database server. It is not the equivalent to a transaction. I thought so also until Dave Watts corrected me a little while ago ;-) Each different command between the cfquery tags is it's own seperate transaction. Same as in a stored procedure. You have (I'm almost sure) three transactions in the below statement. In order to make all of the below statements a single transaction, put BEGIN TRANSACTION at the beginning, and COMMIT TRANSACTION at the end. As you have it now, two concurrent users could execute the cfquery at the same time, and potentially get unpredictable results. What happens if they both hit it at the same time and the two updates run before either of the selects? @max is probabaly going to be off... jon - Original Message - From: Paul Smith [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 9:36 PM Subject: SQL 7 Question I've been putting one SQL statement after another like the following inside a CFQUERY for a while. Seems to work fine. UPDATE MAX_ID_Clients SET MAX_ID_Client = MAX_ID_Client + 1 DECLARE @MAX INT SELECT @MAX = MAX_ID_Client FROM MAX_ID_Clients INSERT INTO ClientData(CFID,CFTOKEN) VALUES(@MAX,#CFT#) Is this a known feature? Is there a limit to how many SQL statements can be combined this way? I assume the above is equivalent to a CFTRANSACTION. Is this so? best, paul ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
