User profile for 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 / comments

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 cr...

0 votes

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

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

0 votes

This is a little disappointing for me. I can understand why Redgate couldn't deploy to the system database but Data Compare should at least do the compare. I was hoping that this tool might be able to show me values in system tables to help troubleshoot an issue I was having with CRL assemblies between a test and production environment. / comments

This is a little disappointing for me. I can understand why Redgate couldn't deploy to the system database but Data Compare should at least do the compare. I was hoping that this tool might be able...

0 votes

How can we help you today?

KevinDavis

Activity overview