Hi i'm new here and i hope someone help me

I'm working with PS to get information from my VirtualCenter (VMWare) .I wrote a script which inquiry my VirtualCenter and all of my virtualmachine and it puts every results in a SQL 2005 db.
I try to write a procedure that includes commit or rollback action but i found it very difficult.

I could use approach posted in attachment

 But i'm not sure it's the right way !!! Some help?

 

 

Are you having an issue loading data into SQL Server or is the data already in SQL Server and you're executing a T-SQL update command from Powershell?

In my opinion its easier to do transactions in T-SQL, but as a DBA who works with T-SQL everyday, I'm a little biased. Can you create a T-SQL procedure similar to the code below and then using ADO.NET ExecuteNonQuery method call the procedure from Powershell?

CREATE PROCEDURE usp_update_vc
AS
SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRAN
UPDATE table1 SET col1 = 1, col2 =2
UPDATE table2 SET col1 = 1, col2 =2
UPDATE table3 SET col1 = 1, col2 =2
COMMIT

The XACT_ABORT setting instructions SQL to commit the entire transaction, all 3 updates or none at all. You don't have to worry about rolling back the transaction if any one of the updates fails.

I'm not sure if this is what you are looking to do. It would helpful to see the full code instead of the pseudo code you provided.