Transaction management in CF 4.0 with SQL-server 6.0

[Nazghul]

Hi there,

I'm having trouble updating multiple tables: I want all of the SQL-commands to rollback whenever an error occurs with 1 of the SQL-commands, CF won't rollback although it should do so (according to the help). (working with CF 4.0, so no <cftransaction action=&quot;ROLLBACK&quot;/> or any of that kind is used)

This is not a logical programming-error (thus I won't paste the script here)

I'm connecting to a SQL-server database, of which the standard is an autocommit(I think). If this is the fact, than I'm looking for a script to pass to sql-server via the query-tags.

something like this:
<cfquery name=&quot;q_mysql&quot; datasource=&quot;mydatasource&quot;>
AutoCommit = FALSE
</cfquery>

If any of you can help me with this one, please post!

Thanks!

 

[iza]

the solution i've found is to update multiple tables in a stored procedure (then i can control rollbacks and even have savepoints), and call that procedure with cfstoredproc. It works fine now.
maybe there is a solution in coldfusion, then i hope someone else could help !

 

[iza]

the solution i've found is to update multiple tables in a stored procedure (then i can control rollbacks and even have savepoints), and call that procedure with cfstoredproc. It works fine now.
maybe there is a solution in pure coldfusion, then i hope someone else could help !

 

[Nazghul]

Yeah, that's a fine solution. But for the site I'm working on it is preferred to execute a solution in CF. The server that hosts this site won't let us connect directly to the SQL-server, and they also said they won't perform other restores of frequent database dumps in the near future.(I guess we actually gave them something to do ) Solution: get a new provider, I know

Thank you Iza for the superfast reply...

 

[iza]

yeah, and 2 replies for 1 as well ;-)
allaire's doc is weird, it's not the 1st time they say it works that way and nobody manage to !