Views, Transactions and blocking

[FunkyDexter]

It sounds like Zvoni has resolved your main question but I thought I'd chip in on a few of the side questions you asked:-

the SQL OleDB provider doesn't support nested transactions

This is because SqlServer doesn't support nested transactions - at least, not properly. You can open several transactions but, when you issue a single commit or rollback, they are ALL processed, not just the last one. If you need to nest transactions properly you need to keep track of @@TranCount and use that to decide what to do. It's a bit crap and gets complicated but it's the way SQLServer has always been (I'm not sure that any other database supports proper nesting - it's to do with the way the log works so may not be a soluble problem).

why is creation of a VIEW different to say, a Select into or Alter table that also modifies the DB schema?

It's not. I think the problem is that you're creating the view in one session and querying it on another. You're using the same connection but the view is being created via a command and the query is being issued via a recordset and those two entities aren't associated so the database treats them separately. I think, if you did the whole thing via the command or the result set you would find it worked though I'm not in a position to test that right now. Similarly, as Zvoni said, you could just commit the command first or, as you have done, check for it's existence before querying.

Will a Select into also cause blocking issues in some situations?

EVERYTHING causes blocking because EVERYTHING is a transaction - it's just that sometimes it's not obvious. E.g. "Select Count(*) From My Table" executes in it's own transaction - it's just that it's (probably) a very quick transaction and only applies Shared locks so it doesn't get in the way of anything else. But if you do an update (either schema or data) query it will apply Exclusive locks until the transaction is committed or rolled back. If you do that in a transaction that takes a long time to run (for any reason, it doesn't need to be the actual update) then you will get blocking issues. This is true even of a long running update statement that isn't in an explicit transaction - because it is in an implicit transaction.

The take away is that you need to keep your transactions short. If you can't do that then you have a deeper problem - either your approach or your architecture is wrong. We can help with that but would need more context.

Views should never be created from a running program

This! One thousand times: This! Is there a reason you're creating views on the fly? There's almost certainly something better we can suggest.