Feature Request: Import Database Distribution List from a database

jgraves

Tried to find Multi Script in User Voice and it wasn't there. So posting here.

It would be great to import/manage database distribution lists from an actual database. We regularly update deployment locations in metadata. Currently we have to manually edit the database distribution lists which can lead to errors.

Thank you for consideration! Jay

csuire

This would help me to automatically add new cloned databases to my distribution list.  Each time I use this program I have to go through these steps to add new cloned databases before running scripts. Configure->Expand Server->Highlight all dbs that start with a certain word->Add.

Miker257

We also maintain a database of metadata distribution lists.  It would be useful to manage multiscript distribution lists based on our database version.

ben_b

I showed this tool to a DBA and they asked if they could import their server lists from their existing CMS registered servers (Central Management Server)...  would be a good feature!

KevinDavis

Even Being able to Select FOR XML from SSMS would be great. Attempting a SQL Script for that own my own now.

Ditat

Even leaving all as is but allowing us to specify where distribution list is would be useful. This way we can utilize our version control for those.

KevinDavis

I can't take credit for this one because a Junior Programmer decided to do a quick hit with Powershell for a solution to this problem. And we have never gone back to make it any prettier,
It will create the distribution list to a drive location that it will fill from a Database Table. The values required from that table are a ServerName and a ConnectionString for the referenced server. It will overwrite any file that is currently present on the drive location with the same file name.
You should be able to decipher what we have done from the script below written for PowerShell:
(I obfuscated some of our parameters and static server names here so there may be a syntax error after you have replaced all of your values and attempt to execute. But I feel confident you can correct them!!!
I did genericize this script somewhat from the less abstract version we run but only slightly.
Also, note the comment on the creation of the <cserver> XML Node)

=======================================================

param([switch]$Env1, [switch]$Env2)

$SQLServer = "YourServerName" #SQL Server Name/DAG Name that will be queried

$ListingDatabase = "YourListingDatabaseName"

$DistListName = "Some Databases" # Name that this list will be referenced by in the dropdown selection inside of Multi-Script

$OutputFile = "C:\WhateverTemp\SomeEnvironment_MultiScript_List.smsdl" #

if($Env1) 

{

    $SQLServer = "Environment1"

    $ListingDatabase = "YourListingDatabaseName"

    $DistListName = "Environment2 Databases"

    $OutputFile = "D:\WhateverTemp\Env1_MultiScript_List.smsdl"

}

if($Env2) 

{

    $SQLServer = "Environment2"

    $ListingDatabase = "YourListingDatabaseName"

    $DistListName = "Environment2 Databases"

    $OutputFile = "E:\WhateverTemp\Env2_MultiScript_List.smsdl"

}

if(Test-Path $OutputFile) 

{

    Remove-Item $OutputFile -Force -ErrorAction Stop

}

<#

<?xml version="1.0" encoding="utf-16" standalone="yes"?>

<databaseListsFile version="1" type="databaseListsFile">

  <databaseLists type="List_databaseList" version="1">

    <value version="2" type="databaseList">

      <name>TLOS Alabama Databases</name>

      <databases type="BindingList_database" version="1">

#>

$XML = New-Object System.Collections.ArrayList

[void]$XML.Add('<?xml version="1.0" encoding="utf-16" standalone="yes"?>')

[void]$XML.Add('<databaseListsFile version="1" type="databaseListsFile">')

[void]$XML.Add('  <databaseLists type="List_databaseList" version="1">')

[void]$XML.Add('    <value version="2" type="databaseList">')

[void]$XML.Add('      <name>' + $DistListName + '</name>')

[void]$XML.Add('      <databases type="BindingList_database" version="1">')

$SQL = "select DBServerName, ServerDBConnectionString from Servers order by 1"

$Servers = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $ListingDatabase -Query $SQL 

Write-Host $Servers.Count

foreach($S in $Servers)

{

    <#

        <value version="5" type="database">

          <name>DB0123456789</name>

          <server>123456789</server>

          <integratedSecurity>True</integratedSecurity>

          <connectionTimeout>15</connectionTimeout>

          <protocol>-1</protocol>

          <packetSize>4096</packetSize>

          <encrypted>False</encrypted>

          <selected>True</selected>

          <cserver>123456789\MSSQL</cserver>

        </value>

    #>

    $ServerName = $S.DBServerName

    $ConString = $S.ServerDBConnectionString

    $ConStringBuilder = New-Object System.Data.Common.DbConnectionStringBuilder

    $ConStringBuilder.set_ConnectionString($ConString) # https://stackoverflow.com/a/7615631

    [void]$XML.Add('        <value version="5" type="database">')

    [void]$XML.Add('          <name>' + $ServerName + '</name>')

    [void]$XML.Add('          <server>' + $ConStringBuilder.'data source' + '</server>')

    [void]$XML.Add('          <integratedSecurity>True</integratedSecurity>')

    [void]$XML.Add('          <connectionTimeout>15</connectionTimeout>')

    [void]$XML.Add('          <protocol>-1</protocol>')

    [void]$XML.Add('          <packetSize>4096</packetSize>')

    [void]$XML.Add('          <encrypted>False</encrypted>')

    [void]$XML.Add('          <selected>True</selected>')

    <# \MSSQL is the default instance we have on all DB Servers. You may have to find the right value 

        to complete the construction of this ConnectionString correctly for your servers.#>

    [void]$XML.Add('          <cserver>' + $ConStringBuilder.'data source' + '\MSSQL</cserver>')

    [void]$XML.Add('        </value>')

}

<#    

      </databases>

      <guid>d6a37944-a66f-4016-adb9-1</guid>

    </value>

  </databaseLists>

</databaseListsFile>

#>

[void]$XML.Add('      </databases>')

[void]$XML.Add('      <guid>' + (New-Guid).Guid + '</guid>')

[void]$XML.Add('    </value>')

[void]$XML.Add('  </databaseLists>')

[void]$XML.Add('</databaseListsFile>')

$XML | Out-File $OutputFile -Force -ErrorAction Stop

Write-Host $OutputFile