获取用于电子邮件输出的SQL复制PowerShell脚本

Question

我正在使用以下脚本在输出环境中输出SQL服务器之间的复制状态。该脚本运行良好，并在PowerShell控制台中提供了我所需的内容，但是我需要将脚本中的结果通过电子邮件发送到特定的DL。

我尝试了几种方法，但似乎无法正确地将其输出/格式化。

##Cross-Server Replication Monitor
## Script referenced http://www.madeirasql.com/cross-server-replication-health-check-using-      powershell/

##If not Loaded, Please Load SQLServer Snapins Into PowerShell By Running The Following Cmdlets:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

##If Script Execution Is Disabled Please Run The Following cmdlet:
##Set-ExecutionPolicy RemoteSigned

## Beginning of Monitor

## Path To Server ListFile: Edit Path and List Target Servers In File With Line Breaks, For Named     Instances Use The Typical "Server\Instance" Convention
 foreach ( $svr in get-content "C:\Scripts\Servers.txt") 
{
##Connection String With Server Variable, Distribution Database name is 'Distribution'
$con = "server= $svr;database=Distribution;Integrated Security=sspi" 

##Begin SQL Query
##Refreshing Replication Monitor Cache
$cmd = "SET NOCOUNT ON; EXEC sp_replmonitorhelppublisher" 
##Getting Info From Replication Sp and Tables, Joining and Selecting 
$cmd = $cmd + " CREATE TABLE #COUNTERS"
$cmd = $cmd + " ("
$cmd = $cmd + " [DATABASE]       SYSNAME,"
$cmd = $cmd + " REPLICATEDTRANS  INT,"
$cmd = $cmd + " REPRATESEC       FLOAT,"
$cmd = $cmd + " REPLATENCY       FLOAT,"
$cmd = $cmd + " LSN1             BINARY(10),"
$cmd = $cmd + " LSN2             BINARY(10)"
$cmd = $cmd + " )"
$cmd = $cmd + " INSERT INTO #COUNTERS"
$cmd = $cmd + " EXEC sp_replcounters"
$cmd = $cmd + " SELECT DISTINCT "
$cmd = $cmd + "        m.Publisher_db,"
$cmd = $cmd + "        m.Publication,"
$cmd = $cmd + "        s.name AS 'Subscriber',"
$cmd = $cmd + "        ma.Subscriber_db,"
$cmd = $cmd + "        CASE [Status]"
$cmd = $cmd + "             WHEN 1 THEN 'Started'"
$cmd = $cmd + "             WHEN 2 THEN 'Succeeded'"
$cmd = $cmd + "             WHEN 3 THEN 'In Progress'"
$cmd = $cmd + "             WHEN 4 THEN 'Idle'"
$cmd = $cmd + "             WHEN 5 THEN 'Retrying'"
$cmd = $cmd + "             WHEN 6 THEN 'Failed'"
$cmd = $cmd + "        END AS [Status],"
$cmd = $cmd + "        CASE warning"
$cmd = $cmd + "             WHEN 0 THEN NULL"
$cmd = $cmd + "             WHEN 1 THEN 'Expiration'"
$cmd = $cmd + "             WHEN 2 THEN 'Latency'"
$cmd = $cmd + "             ELSE 'MergeWarning'"
$cmd = $cmd + "        END AS [Warning],"
$cmd = $cmd + "        C.REPLICATEDTRANS AS 'Awaiting Transactions To Dist',"
$cmd = $cmd + "        CONVERT(INT, c.REPRATESEC) AS 'Avrage Trans/Sec to Dist',"
$cmd = $cmd + "        CONVERT(DECIMAL(10, 2), c.REPLATENCY) AS 'Avg. Latency to Dist/Sec',"
$cmd = $cmd + "        CONVERT(DECIMAL(10, 2), mm.cur_latency) AS 'Avg. Latency to Subscriber'"
$cmd = $cmd + "        INTO #MonitorRepl"
$cmd = $cmd + " FROM   dbo.MSpublications m"
$cmd = $cmd + "        INNER JOIN dbo.MSreplication_monitordata mm"
$cmd = $cmd + "             ON  mm.publisher_db = m.publisher_db"
$cmd = $cmd + "             AND mm.publication_id = m.publication_id"
$cmd = $cmd + "             AND mm.agent_type = 3"
$cmd = $cmd + "        INNER JOIN dbo.MSdistribution_agents ma"
$cmd = $cmd + "             ON  ma.publisher_id = m.publisher_id"
$cmd = $cmd + "             AND ma.publication = mm.publication"
$cmd = $cmd + "             AND ma.job_id = mm.job_id"
$cmd = $cmd + "        INNER JOIN sys.servers s"
$cmd = $cmd + "             ON  ma.subscriber_id = s.server_id"
$cmd = $cmd + "        INNER JOIN #COUNTERS C"
$cmd = $cmd + "             ON  m.publisher_db = C.[DATABASE]"
$cmd = $cmd + " ORDER BY"
$cmd = $cmd + "        m.publisher_db,"
$cmd = $cmd + "        m.publication,"
$cmd = $cmd + "        s.name,"
$cmd = $cmd + "        ma.subscriber_db"
$cmd = $cmd + "        DROP TABLE #COUNTERS"
$cmd = $cmd + " SELECT *"
$cmd = $cmd + " FROM   #MonitorRepl"
$cmd = $cmd + " DROP TABLE #MonitorRepl"
##Creating DataSet Object
$set = new-object system.data.dataset
##Running Query
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
##Filling DataSet With Results
$da.fill($set) | out-null
##Creating Table Object and Inserting DataSet
$dt = new-object System.Data.DataTable
$dt = $set.Tables[1]
##Displaying Current Server Name
$svr
##Formating DataTable To A Readable List And Presenting
$dt|Format-List 

}

提前感谢！如果有人已经准备好使用HTML输出脚本来实现这一目的，我也不介意尝试一下！

Answer 1

不一定是答案，但我想说明的是，使用here字符串可以更有效地将查询传递给sql

$Query = @"
ALTER DATABASE $dbname SET MULTI_USER;
ALTER DATABASE $dbname SET ONLINE;
BACKUP DATABASE $dbname TO DISK = '$DBbak' WITH INIT;
ALTER DATABASE $dbname SET OFFLINE WITH ROLLBACK IMMEDIATE;
"@

唯一需要注意的是，“由于某些原因，最后一行上的@不能缩进，否则会中断字符串。

还有一个cmdlet，ConvertTo-HTML...不确定你是否尝试过它。

此外，您还可以在此处找到一些关于html报告的有用信息：http://powershell.org/wp/ebooks/

有一本免费的电子书，里面有很多信息。