OK so I gave up and wrote my own macro, apologies in advance my VB.Net knowledge was close to non-existent when I started. There were also a few classes I hadn't used before so apologies if there are some uses which are not correct or could be improved.
Once you've set up the context menus in Visual Studio you should just able to right click on a script/scripts/folder and select "Run On..." and the scripts will all be run on the selected database. There are instructions for setting up the macro and creating the context menus at the bottom of this post.
Feel free to post any corrections and I'll try and update my post.
Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports EnvDTE90a
Imports EnvDTE100
Imports System.IO
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Generic
Imports System.Windows.Forms
Public Module ContextMenu
' Copy the following files from: C:Program FilesMicrosoft SQL Server100SDKAssemblies
' to: C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDEPublicAssemblies
'
' Microsoft.SqlServer.Smo.dll
' Microsoft.SqlServer.ConnectionInfo.dll
' Microsoft.SqlServerManagement.Sdk.Sfc.dll
'
' Add a reference to them in your Macros project
'
' Add a references to
' System.Data.dll
' System.Drawing.dll
Dim WithEvents _connection As SqlConnection
Dim _lastError As String
Dim _fileNames As New Collection
Dim _errorCount As Int16
Dim _databaseOutputPane As OutputWindowPane
Public Sub RunOnServer()
Dim serverName As String
Dim databaseName As String
Dim fileName As String
Dim scriptError As String
'On Error GoTo ErrHandler
'Rest global variables since macro last run!
_errorCount = 0
_fileNames.Clear()
_lastError = String.Empty
Try
_databaseOutputPane = GetDatabaseOutputPane()
GetAllSelectedFiles()
If Not ScriptsSelectedOk() Then Exit Sub
If Not SelectServer(serverName, databaseName) Then Exit Sub
If Not ValidateDatabaseConnection(serverName, databaseName) Then Exit Sub
WriteToOutputWindow(Environment.NewLine)
WriteToOutputWindow(String.Format("------ Running SQL scripts on '{0}' ------", serverName))
'Iterating through all selected items (for some reason the list is upside down)
For i = _fileNames.Count To 1 Step -1
fileName = _fileNames(i)
If Path.GetExtension(fileName) = ".sql" Then
Try
scriptError = RunScript(serverName, databaseName, fileName)
If Len(scriptError) > 0 Then
If (ResumeRunningScripts(_lastError, fileName) = False) Then
Exit For
End If
End If
Catch ex As Exception
' Any unexpected errors not caught by the Sub Connection_InfoMessage()
Dim message As String = String.Format("Do you wish to continue" + Environment.NewLine + "Error running script {0} {1} {2}", fileName, Str$(ex.Message), ex.StackTrace)
Dim result = MsgBox(message, MsgBoxStyle.YesNo, "Error Running Sql Script")
If result <> MsgBoxResult.Yes Then
Exit For
End If
End Try
End If
Next
DisplayResults()
Catch ex As Exception
Dim message As String = "An unexpected error occurred." + Environment.NewLine + ex.Message + vbCrLf + ex.StackTrace
MessageBox.Show(message, "Run On...", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Function GetConnectionString(ByVal serverName As String, ByVal databaseName As String)
Return String.Format("Database={0};Server={1};Trusted_Connection=True", databaseName, serverName)
End Function
Private Function RunScript(ByVal serverName As String, ByVal databaseName As String, ByVal fileName As String) As String
Dim connectionString As String = GetConnectionString(serverName, databaseName)
Dim script As String
Dim server As Server
WriteToOutputWindow(fileName)
Try
Using reader As New StreamReader(fileName)
script = reader.ReadToEnd()
End Using
_lastError = vbNullString
_connection = New SqlConnection(connectionString)
' Any errors fire the event Connection_InfoMessage
_connection.FireInfoMessageEventOnUserErrors = True
server = New Server(New ServerConnection(_connection))
Dim rowsAffected As Integer = server.ConnectionContext.ExecuteNonQuery(script)
Finally
server.ConnectionContext.Disconnect()
_connection.Close()
'_connection.ClearAllPools()
End Try
Return _lastError
End Function
Private Sub Connection_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs) Handles _connection.InfoMessage
Dim err As SqlError
For Each err In e.Errors
_errorCount = _errorCount + 1
Dim errorMessage As String = String.Format("Msg {0}, Level {1}, State {2}, Line {3}" + Environment.NewLine + "{4}", _
err.Number, err.Class, err.State, err.LineNumber, err.Message)
_lastError = _lastError & Environment.NewLine & errorMessage
WriteToOutputWindow(vbCr)
WriteToOutputWindow(errorMessage)
Next
End Sub
Private Function ResumeRunningScripts(ByVal ErrorMessage As String, ByVal fileName As String) As Boolean
Dim result As MsgBoxResult
Dim message As String = String.Format("An error occured running the script '{0}', see output window for further details.{1}Do you wish to continue?", Path.GetFileName(fileName), Environment.NewLine)
result = MsgBox(message, MsgBoxStyle.YesNo, "Error Running Sql Script")
Return (result = MsgBoxResult.Yes)
End Function
Private Function SelectServer(ByRef serverName, ByRef databaseName) As Boolean
Dim serverList As String() = New String() {"DEVDB", "STAGEDB", "LIVEDB"}
Dim frm As New Form
Dim cboServers As New ComboBox
Dim lblServer As New Label
Dim btnOk As New Button
Dim btnCancel As New Button
Dim lblDatabase As New Label
Dim txtDatabase As New TextBox
'
' cboServers
'
cboServers.FormattingEnabled = True
cboServers.Items.AddRange(serverList)
cboServers.Location = New System.Drawing.Point(99, 13)
cboServers.Size = New System.Drawing.Size(189, 21)
cboServers.TabIndex = 0
cboServers.Name = "cboServers"
cboServers.SelectedIndex = 0
'
' lblServer
'
lblServer.AutoSize = True
lblServer.Location = New System.Drawing.Point(12, 16)
lblServer.Name = "lblServer"
lblServer.Size = New System.Drawing.Size(70, 13)
lblServer.Text = "Server name:"
'
' btnOk
'
btnOk.DialogResult = DialogResult.OK
btnOk.Location = New System.Drawing.Point(132, 69)
btnOk.Size = New System.Drawing.Size(75, 23)
btnOk.TabIndex = 3
btnOk.Text = "OK"
'
' btnCancel
'
btnCancel.DialogResult = DialogResult.Cancel
btnCancel.Size = New System.Drawing.Size(75, 23)
btnCancel.Location = New System.Drawing.Point(212, 69)
btnCancel.TabIndex = 4
btnCancel.Text = "Cancel"
'
' lblDatabase
'
lblDatabase.AutoSize = True
lblDatabase.Location = New System.Drawing.Point(12, 46)
lblDatabase.Size = New System.Drawing.Size(70, 13)
lblDatabase.Text = "Database:"
'
' txtDatabase
'
txtDatabase.Location = New System.Drawing.Point(99, 43)
txtDatabase.Size = New System.Drawing.Size(189, 20)
txtDatabase.Text = "MyDatabaseName"
txtDatabase.TabIndex = 2
'
' frm
'
frm.AutoScaleDimensions = New System.Drawing.SizeF(6.0F, 13.0F)
frm.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
frm.Text = "Select Server"
frm.Size = New System.Drawing.Size(299, 128)
frm.AcceptButton = btnOk
frm.CancelButton = btnCancel
frm.FormBorderStyle = FormBorderStyle.FixedDialog
frm.StartPosition = FormStartPosition.CenterParent
frm.Controls.Add(btnCancel)
frm.Controls.Add(btnOk)
frm.Controls.Add(lblServer)
frm.Controls.Add(cboServers)
frm.Controls.Add(txtDatabase)
frm.Controls.Add(lblDatabase)
Dim winptr As New WinWrapper()
Dim result As String
' Launch server/database dialog
Try
If frm.ShowDialog(winptr) = DialogResult.OK Then
If Not cboServers.SelectedItem Is Nothing Then
serverName = cboServers.SelectedItem
Else
serverName = cboServers.Text
End If
databaseName = txtDatabase.Text
Else
serverName = vbNullString
databaseName = vbNullString
End If
SelectServer = (Len(serverName & databaseName) > 0)
Catch ex As Exception
frm.Close()
Finally
winptr = Nothing
End Try
End Function
Public Function CreateServerForm(ByVal serverList As String()) As Form
Dim frm As New Form
Dim cboServers As New ComboBox
Dim lblServer As New Label
Dim btnOk As New Button
Dim btnCancel As New Button
Dim lblDatabase As New Label
Dim txtDatabase As New TextBox
'
' cboServers
'
cboServers.FormattingEnabled = True
cboServers.Items.AddRange(serverList)
cboServers.Location = New System.Drawing.Point(99, 13)
cboServers.Size = New System.Drawing.Size(189, 21)
cboServers.TabIndex = 0
cboServers.SelectedIndex = 0
'
' lblServer
'
lblServer.AutoSize = True
lblServer.Location = New System.Drawing.Point(12, 16)
lblServer.Name = "lblServer"
lblServer.Size = New System.Drawing.Size(70, 13)
lblServer.Text = "Server name:"
'
' btnOk
'
btnOk.DialogResult = DialogResult.OK
btnOk.Location = New System.Drawing.Point(132, 69)
btnOk.Size = New System.Drawing.Size(75, 23)
btnOk.TabIndex = 3
btnOk.Text = "OK"
'
' btnCancel
'
btnCancel.DialogResult = DialogResult.Cancel
btnCancel.Size = New System.Drawing.Size(75, 23)
btnCancel.Location = New System.Drawing.Point(212, 69)
btnCancel.TabIndex = 4
btnCancel.Text = "Cancel"
'
' lblDatabase
'
lblDatabase.AutoSize = True
lblDatabase.Location = New System.Drawing.Point(12, 46)
lblDatabase.Size = New System.Drawing.Size(70, 13)
lblDatabase.Text = "Database:"
'
' txtDatabase
'
txtData
