if DDL statement fails whole transaction is rolled back sql server

Question

Hi,

I have a series of DML and DDL(Drop View only) statements that I need to execute under single transaction from VB.net app. I m using commandObj, and kept above all I kept beginTransaction statement. Everything is working fine expect when error is returned by Drop statement. If Drop statement returns error then whole transaction is rolledback. I do not want tthis. I want to continue even if Drop fails keeping the other DML statements that were executed before Drop. is this possible?

thanks in advance
Sai

Answer 1

I don't think DDL statements are transactional (i.e. you can not rollback statements before the current one if an error occurs).

See why the error occurs & fix it instead for the DROP VIEW

EDIT: If it could be a view which is non-existent (not created or dropped already), you can use IF EXISTS (....) syntax.

Example here.

Answer 2

Then move your DDL before or after your COMMIT and/or ROLLBACK statement(s) as appropriate.

Answer 3

Execute your statements individually, and handle the errors as required (rollback or not rollback).

Answer 4

You could run a select in your sysobjects view to check if the view exists and drop it only if it exists.

You could also use try ... catch in a stored procedure.

Like others mentionned, you could also drop the view before the transaction.

Or maybe you could use a temporary table instead of the view, but it really depends on your needs and what needs to be accomplished.

Answer 5

Thank you all for your replies..

Actually there is a check already before executing Drop View whether view exists or not. Problem with drop view will arise only if there is a lock by another user. for now as you I have moved all the DML transactions after executing drop view, that way even if the drop view statement fails(which will rollback all the previous transactions), there will no problem as that is the first statement am executing.