ado.net transaction with alter database
-
hello all - hope you could help me with this one
I need to make 5 actions to the database as an atomic unit - if one fails then all fail and no changes made.
The answer seems to be easy – put them into a transaction and rollback in a case of trouble.
The complex part is that that one of the statements is an "ALTER DATABASE" statement that produces an error messege :
"ALTER DATABASE is not allowed in a multi statement transaction"
if any one has a solution - TSQL or programmaticly, i would be very gratefull
Chen kirsch
-
Can you post the whole script?
-
thanks for the reply
i can't send the script as it's problematic to produce it
the format in psuadocode is :
TZ = SQLCONNECTION.createtransaction
comm = sqlconnection.createcommand
comm.transaction = tz
comm.command text = "alter database ........"
comm.executenonquery()
comm.command text = script2
comm.executenonquery()
comm.command text = script3
comm.executenonquery()
comm.command text = script4
comm.executenonquery()
comm.command text = script5
comm.executenonquery()
tz.commit
i need tomake sure that if one of the later scripts fail that the ALTER DATABASE statment will be rolled back as well, what cannot be done if it is not in a transaction, which is the problem
thanks for replies
chen
-
ALTER the database, and then start a transaction which tests if the feature you tried to change is changed. for example:
ALTER DATABASE PUBS SET AUTO_CLOSE OFF
--now test if the feature is the desired value...if not, do not proceed.
declare @propflag integer
select @propflag =convert(int,DATABASEPROPERTYEX( 'PUBS' , 'IsAutoClose' ))
if @propflag =0
begin
print @propflag
'do other operations.
end
most likely you are trying to set the UserAccess to SINGLE_USER, but this logic would work regardless;
i would suggest changing the property via ADO / ADO.NET, and then use a command object to return the value of the flag you are testing for, and only if that was returned with the desired value, would I being an ADO transaction and the otehr SQL statements.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
that solutions is probelmatic because it doesn't address the case in which one of the other four commands doesn't work .Then i would have to rollback the alter database as well - losing the values before change. All 5 actions\command executions should be considered a single transaction - ACID - if 1 fails they all rollback
-
Can you do something like this?
Alter DB
begin tran
exec 1
if @@error 0
quitwithRollback
exec 2
if @@error 0
quitwithRollback
...
exec 4
...
no error :
commit tran
return 0
quitwithrollback
rollback tran
return 1
if the return code is 1 then do another alter db statement that undoes the first one, if not then the script is a success.
-
it seems that this is my only way to act
i hoped not to save all the previous db params in the case of a rollback( becauce i have many). but it seems that i have no alternative....
thanks anyway for the help
chen
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply