Appologies for cross posting this but languages.vb may not be the correct
place to have asked in hindsight.

Im creating a dotnet windows form app that will run a given TSL script
across a domain of sql servers with the same login credentials(for this
version at least)

I would like the RunallServers() below to run in a backround worker to keep
my ui responsive. but within the for each loop I would like the executesql()
to run on seperate spawned threads appending its results to the public
dataset
table.

The background worker is looking ok, its spawning threads for the
Executesql() that is posing a problem.

I am not bothered what order the results are obtained they are destined for
a sorted datagrid. The rest of the app is looking fine but running tsql over
150 servers takes a bit of time and im hoping threading the individual
requests could help, but that bit is beyond my experience. Im hoping someone
here can help!

(incidentally if I declare another background worker im getting background
worker is busy error on the second iteration of the loop even when the do
work event dims a new backroundworker as  = to sender )

Colin Robinson

Sub RunAllServers()

'CheckForIllegalCrossThreadCalls = False

Me.sqlDATA.Clear()

Me.TabControl1.TabPages("Results").Focus()

'all servers

'Reset the Dataset

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Servers.Tables("sqlData").Clear()

Dim server As String = ""

Dim dr As DataRow

Dim servercount As Integer = 0

For Each dr In Servers.Tables(0).Rows

status.Text = " Processing: " & CStr(dr.Item(0) + ";" + dr.Item(1))

Application.DoEvents()

Windows.Forms.Cursor.Current = Cursors.WaitCursor

executesql(dr.Item(0))

'Me.ExecutesqlThread.RunWorkerAsync(dr.Item(0))

servercount = servercount + 1

Windows.Forms.Cursor.Current = Cursors.Default

Next

status.Text = " Done: " & "Servers = " + CStr(servercount) + " Rows =" &
CStr(Servers.Tables("sqlData").Rows.Count)

GridResults.DataSource = Servers.Tables("sqlData")

End Sub

Function RunAllservers(ByVal n As Integer, ByVal worker As
System.ComponentModel.BackgroundWorker, ByVal e As
System.ComponentModel.DoWorkEventArgs) As String

Me.sqlDATA.Clear()

CheckForIllegalCrossThreadCalls = False

Me.TabControl1.TabPages("Results").Focus()

'all servers

'Reset the Dataset

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Servers.Tables("sqlData").Clear()

Dim server As String = ""

Dim dr As DataRow

Dim servercount As Integer = 0

For Each dr In Servers.Tables(0).Rows

status.Text = " Processing: " & CStr(dr.Item(0) + ";" + dr.Item(1))

Application.DoEvents()

Windows.Forms.Cursor.Current = Cursors.WaitCursor

'executesql(dr.Item(0))

Me.ExecutesqlThread.RunWorkerAsync(dr.Item(0))

servercount = servercount + 1

Windows.Forms.Cursor.Current = Cursors.Default

Next

status.Text = " Done: " & "Servers = " + CStr(servercount) + " Rows =" &
CStr(Servers.Tables("sqlData").Rows.Count)

GridResults.DataSource = Servers.Tables("sqlData")

Return status.Text

End Function

Function executesql(ByVal server As String) As String

CheckForIllegalCrossThreadCalls = False

Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Dim connstr As String

If txtPassword.Text = "" Then

connstr = "timeout = 60;packet size=4096;user id=" & Me.TxtLogin.Text &
";pwd = '' ;data source=" & server & ";persist security info=False;initial
catalog=" & Me.TxtDB.Text

Else

connstr = "timeout = 60;packet size=4096;user id=" & Me.TxtLogin.Text &
";Pwd=" & Me.txtPassword.Text & ";data source=" & server & ";persist
security info=False;initial catalog=" & Me.TxtDB.Text

End If

Me.SqlConnection1.ConnectionString = connstr

Me.SqlCommand1.CommandText = Me.TxtSQL.Text

If Not Me.ChkProcedure.Checked Then

Me.SqlCommand1.CommandType = CommandType.Text

Else

Me.SqlCommand1.CommandType = CommandType.StoredProcedure

End If

Me.SqlCommand1.Connection = Me.SqlConnection1

Dim dt As New DataTable

dt.TableName = "execute"

'Split and Execute GO batches

Dim sqlBatches() As String

Dim batchseperator As String = "GO"

sqlBatches = Microsoft.VisualBasic.Split(Me.TxtSQL.Text, batchseperator)

'sqlBatches = Me.TxtSQL.Text.Split(batchseperator)

Dim batch As String

'Me.TxtSQL.ForeColor = System.Drawing.Color.Black

Dim batchconnection As New SqlConnection

batchconnection.ConnectionString = Me.SqlConnection1.ConnectionString

batchconnection.Open()

Me.SqlCommand1.Connection = batchconnection

For Each batch In sqlBatches

If batch.ToString.StartsWith("O") Then

batch = batch.Remove(0, 1)

End If

Me.SqlCommand1.CommandText = batch.ToString

With Me.SqlDataAdapter1

.SelectCommand = SqlCommand1

'.FillSchema(dt, SchemaType.Source)

Try

.Fill(sqlDATA)

Catch ex As Exception

Me.TxtSQL.Text = "Error Server " & server & ": " & ex.Message & vbNewLine &
TxtSQL.Text

Me.TxtSQL.ForeColor = System.Drawing.Color.Red

End Try

End With

Next

batchconnection.Close()

'add server name

'dt.Columns.Add("Server")

'Dim dr As DataRow

'For Each dr In dt.Rows

' dr.Item("server") = server

'Next

'Servers.Tables.Add(dt)

'Dim parentCol As DataColumn

'Dim childCol As DataColumn

'' Code to get the DataSet not shown here.

'parentCol = Servers.Tables("server").Columns(0)

'childCol = dt.Columns("server")

'' Create DataRelation.

'Dim relserver As DataRelation

'relserver = New DataRelation("Results", parentCol, childCol)

'' Add the relation to the DataSet.

'Servers.Relations.Add(relserver)

Me.GridResults.DataSource = Servers.Tables("sqlDATA")

Return server

End Function