Monday, November 14, 2011

SQL Multi Script by Redgate distribution list generator

The following code generates a custom distribution list for SQL Multi Script by Redgate for version 1.1.0.34.

We have a lot of databases and several servers that change constantly, and I got sick of updating the distribution list each time I ran the program, so I came up with this that runs each time before launching Multi Script.  It is a VB.NET code file and the second part is a class file.  Just set up a new VB application project, add this code file and class file, edit as needed and compile.

  • You will have to modify SQLExecT for your environment
  • Repeat the Begin Distribution List section for each distribution list needed, each with a unique guid and name.
  • We store a list of all databases and which server they are on in a table called CustomerDatabases. You will have to customize this to your own environment.
  • The DENSE_RANK() allows me to order the databases in a custom way so I get better parallel execution.
  • It requires 3 arguments.  The first is the file path to the Multi Script distribution list xmlx/dat file, the second is the username that Multi Script will use, the third is the encrypted password that you will need to get out of the Multi Script distribution file before you generate your own.
  • Depending on OS, the filepath should be something like: C:\Users\XXXX\AppData\Roaming\Red Gate\SQL Multi Script\Application.dat


Imports System
Imports System.Net
Imports System.IO
Imports System.Xml
Imports Microsoft.VisualBasic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports MultiScript.CWDBUtil

Module Module1
   Sub Main(ByVal args As String())
      Dim dt As DataTable, dt2 As DataTable, sSQL As String = "", username As String = "", password_encrypted As String = ""
      Dim server_name As String = "", database_name As String = "", environment As String = "", nguid As String = "", filepath As String = ""

      If args.Length > 0 Then
         filepath = args(0)
      Else
         filepath = "C:\Users\Dave\AppData\Roaming\Red Gate\SQL Multi Script\Application.dat"
      End If

      If args.Length > 1 Then
         username = args(1)
      Else
         username = "myusername"
      End If

      If args.Length > 2 Then
         password_encrypted = args(2)
      Else
         password_encrypted = "xxxxxxxxxxxxx"
      End If

      File.Delete(filepath)
      Dim fs As FileStream = New FileStream(filepath, FileMode.OpenOrCreate, FileAccess.Write)
      Dim s As StreamWriter = New StreamWriter(fs, Encoding.Unicode)

      s.Write("<?xml version=""1.0"" encoding=""utf-16"" standalone=""yes""?>")
      s.Write("<!--" & vbCrLf & "SQL Multi Script 1" & vbCrLf & "SQL Multi Script" & vbCrLf & "Version:1.1.0.34-->")
      s.Write("<multiScriptApplication version=""2"" type=""multiScriptApplication"">")
      s.Write("<databaseLists type=""List_databaseList"" version=""1"">")

      '-- Repeat the following section for each distribution list needed, each with a unique guid and name.
      '-- Begin distribution list 1
      s.Write("<value type=""databaseList"" version=""2"">")
      s.Write("<name>*ALL LIVE DATABASES</name>")
      s.Write("<databases type=""BindingList_database"" version=""1"">")
      sSQL = "SELECT server_name, database_name, nguid = Convert(Varchar(50), NEWID()) FROM CustomerDatabases " & _
             "WHERE is_live = 1 ORDER BY DENSE_RANK() OVER (PARTITION BY server_name ORDER BY customer_id), server_name"
      dt = SQLExecT(sSQL)
      For Each row As DataRow In dt.Rows
         server_name = row("server_name")
         database_name = row("database_name")
         nguid = row("nguid")
         s.Write("<value type=""database"" version=""5"">")
         s.Write("<name>" & database_name & "</name>")
         s.Write("<server>" & server_name & "</server>")
         s.Write("<integratedSecurity>False</integratedSecurity><username>" & username & "</username><savePassword>True</savePassword><password encrypted=""1"">" & password_encrypted & "</password>")
         s.Write("<connectionTimeout>15</connectionTimeout><protocol>-1</protocol><packetSize>4096</packetSize><encrypted>False</encrypted><selected>True</selected>")
         s.Write("<cserver>" & server_name & "</cserver></value>")
      Next
      dt.Dispose()
      s.Write("</databases>")
      s.Write("<guid>" & nguid & "</guid>")
      s.Write("</value>")
      '-- End distribution list 1

      s.Write("</databaseLists>")
      s.Write("<addedServers type=""List_server"" version=""1"">")
      sSQL = "SELECT DISTINCT server_name FROM CustomerDatabases ORDER BY server_name"
      dt = SQLExecT(sSQL)
      For Each row As DataRow In dt.Rows
         server_name = row("server_name")
         s.Write("<value type=""server"" version=""1""><name>" & server_name & "</name><integratedSecurity>False</integratedSecurity><username>" & username & "</username><savePassword>True</savePassword><password encrypted=""1"">" & password_encrypted & "</password><connectionTimeout>15</connectionTimeout><protocol>-1</protocol><packetSize>4096</packetSize><encrypted>False</encrypted></value>")
      Next
      dt.Dispose()
      s.Write("</addedServers>")
      s.Write("</multiScriptApplication>")

      s.Close()
      fs.Close()
      s.Dispose()
      fs.Dispose()

      System.Diagnostics.Process.Start("C:\Program Files\Red Gate\SQL Multi Script 1\SQLMultiScript.exe")
   End Sub

End Module


-----------------------------------------------------------------------
-----------------------------------------------------------------------

Imports Microsoft.VisualBasic
Imports System.Text
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net.Mail

Public Class CWDBUtil
   ''' <summary>Opens a database connection if it is not already open</summary>
   ''' <param name="DataSource">Server name</param>
   Public Shared Function OpenSqlConn(ByVal DataSource As String) As SqlConnection
      Dim userid As String = "sa"
      Dim password As String = "xxxxxxxx"

      Dim dbConn As SqlConnection
      dbConn = New SqlConnection("Data Source=" & DataSource & ";Initial Catalog=Master;Persist Security Info=True;User ID=" & userid & ";Password=" & password)
      dbConn.Open()

      Return dbConn
   End Function

   ''' <summary>Closes an sql connection</summary>
   Public Shared Sub CloseSqlConn(ByRef dbConn As SqlConnection)
      If Not dbConn Is Nothing Then
         Try
            If dbConn.State = ConnectionState.Open Then dbConn.Close()
         Catch
         End Try
         dbConn = Nothing
      End If
   End Sub

   ''' <summary>Runs an SQL Statement and returns a DataTable.</summary>
   ''' <param name="sSQL">An SQL string</param>
   ''' <param name="DataSource">Server name</param>
   Public Shared Function SQLExecT(ByVal sSQL As String, ByVal DataSource As String) As DataTable
      Dim adpt As SqlClient.SqlDataAdapter, dt As DataTable
      Dim cmnd As SqlCommand = Nothing, dbConn As SqlConnection = Nothing
      dbConn = OpenSqlConn(DataSource)
      Try
         cmnd = New SqlCommand(sSQL, dbConn)
         adpt = New SqlClient.SqlDataAdapter(cmnd)
         dt = New DataTable
         adpt.Fill(dt)
         SQLExecT = dt
         CloseSqlConn(dbConn)
      Catch e As SqlException
         CloseSqlConn(dbConn)
         Throw
      End Try
   End Function

End Class 

No comments:

Post a Comment