I've run into a problem like this. I used ADO.NET 4 with VB.
Here's what I did:
- Created a .txt file with list of servers.
- Dump the .txt file into a List Box.
- Wrote the .NET application to scan through the list box, line by line, dumping each line into a text box on the form.
- Replace the connection string on the SQL connection with the database currently in the text box.
- Wrote the SQL scripts inside the command clause.
- Execute the SQL command on the current database.
- Next line.
'Loop through the server names.
For i = 0 To lbxServers.SelectedItems.Count - 1
txtServer.Text = ""
txtServer.Text &= lbxServers.SelectedItems.Item(i).ToString() & " "
'Call your sub procedure which contains your sql connection and sql command.
That will get your loop going, then you need to write some sub procedures for your queries.
'Declare SQL connection variables.
Dim connection As New Data.SqlClient.SqlConnection
Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
Dim command As New Data.SqlClient.SqlCommand
'Connect to the server next in the list box.
connection.ConnectionString = "Server= " & txtServer.Text & "; integrated security=true"
'SQL Statement to run on the server.
command.CommandText = "TSQL"
command.Connection = "connection"
dataAdapter.SelectCommand = "command"
I put all of this inside of a try/catch loop so if there is an error it will rollback.
The TRY/CATCH loop was inside of my CommandText.
--Run your Statements
DECLARE @Msg NVARCHAR(MAX)
RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
I used my application to grab and dump that local variable for each session into a datagridview. That way if error happened, I saw it in the application.