開啟一個由RDLC匯出的EXCEL檔，顯示『我們發現XXX.xlsx的部份內容有問題。您要我們盡可能嘗試復原嗎?如果您信任此活頁簿的來源，請按一下【是】』。
然後按是之後，又再提示 『已取代的部分: /xl/worksheets/sheet5.xml 部分具有 XML 錯誤。 不合法的 xml 字元。 行 16，欄 19。』
解決方式是將此EXCEL按右鍵解縮後，在檔名目錄下，用記事本找到 /xl/worksheets/sheet5.xml，然後就會看到一個很奇怪的文字了，然後再來源的文字把這個肉眼看不到的文字delete掉，重存檔案就可正常開啟EXCEL了。
這個BS是什麼東東啊，肉眼完全看不到得要解壓打開才看得到，就是後面紅底裡的字
如何查看已安裝的SSAS Server Mode?
最近老瓶新裝，在很舊的網站將report viewer升級到15.0的版本。
使用新版的報表預覽時發現會連線到 vortex.data.microsoft.com 這個網址，因為是封閉的內網，所以報表預覽時花了一點時間才發現有這個連線。
查了一下後這是微軟用來匿名收資元件使用狀況? 預設竟是開啟。
查到官方文件可以在report viewer中設定EnableTelemetry="false"
<rsweb:ReportViewer ID="ReportViewer1" runat="server" EnableTelemetry="false">
</rsweb:ReportViewer>
套用後看起來是沒有再有此連線了。
之前在已安裝Microsoft Access Database Engine 64位元主機上要再安裝32位元的exe只需要
Microsoft Access Database Engine.exe /passive
現在還得要多加一個安裝參數
Microsoft Access Database Engine.exe /passive /quiet
SSIS Designer及SQL SERVER中的匯出入資料都需要安裝Microsoft Access Database Engine32位元。
想用powershell撰寫SSAS多個資料庫的備份及還原到備援機。
詢問https://chat.openai.com 大概產出了85%的script，剩下的就自已依想要的需求再調整，在工作上很有幫助。
借助一點科技力量再加上自已七拼八湊土法練鋼後，備份及還原大致可以滿足目前的需要，踏出成功的第一步。
備份：
$srv="10.10.1.100" #要備份的主機
$tomail="myemail@mail.com" #通知對象EMAIL
$psfolder="D:\Schedule" #powershell目錄
$abffolder="E:\OLAP\Backup" #備份的主機目錄
$dblist=@("BIDB1","BIDB2","BIDB13","BIDB4")
$body= -join ("執行poweshell目錄：$psfolder\backup_asdb.ps1", "`n")
$body= -join ($body,"開始時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n","Server：",$srv , "`n")
$body= -join ($body,"========================================================`n")
foreach($db in $dblist)
{
try
{
$body= -join ($body,"Backup database $db.....to $abffolder .....")
Backup-ASDatabase "$abffolder\$db.abf" $db -ApplyCompression -AllowOverwrite -Server $srv
if ($LASTEXITCODE -eq 0)
{
$body= -join ($body,"successed.`n")
}
else
{
$body= -join ($body,"failed!! `n Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
catch {
$body= -join ($body,"failed!! `n Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
$body= -join ($body,"========================================================`n")
$body = -join($body,"結束時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n")
#write-host $body
Send-MailMessage -Encoding "utf8" -SmtpServer "urmailServer" -From '管理員<myadm@mail.com>' -To "$tomail" -Subject "($srv)每月備份AS資料庫處理結果通知...." -Body $body
還原：
## Set the variables
$serverName = "10.10.1.102" # Replace with your server name and instance name
$backupFolder = "\\10.10.1.100\OLAP\Backup" # Replace with your backup folder path
$databases = @("BIDB1","BIDB2","BIDB13","BIDB4") # Replace with your database names
$tomail="myemail@mail.com" #email to
$psfolder="D:\Schedule\powershell"
$body= -join ("執行poweshell目錄：$psfolder\resretor_asdb.ps1", "`n")
$body= -join ($body,"開始時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n","Server：",$serverName, "`n")
$body= -join ($body,"========================================================`n")
# Restore the databases
foreach ($database in $databases) {
try {
$backupFile = Join-Path $backupFolder "$database.abf"
$body= -join ($body,"Restore database $database.....from $backupFile .....")
if (Test-Path $backupFile) #check backup file exist
{
Restore-ASDatabase -Server $serverName -Name $database -RestoreFile $backupFile -AllowOverwrite
$body= -join ($body," successfully.`n")
}
else
{
$body= -join ($body," failed!! File not exist!! `n")
}
#變更連線字串(因為要將備援機的連線切到唯讀的always on)
$xmla2 = @"
<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>$database</DatabaseID>
<DataSourceID>$database</DataSourceID>
</Object>
<ObjectDefinition>
<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xsi:type="RelationalDataSource">
<ID>$database</ID>
<Name>$database</Name>
<ConnectionString>Provider=SQLNCLI11.1;Data Source=$urserver;Persist Security Info=True;User ID=urname;Password=urpwd;Initial Catalog=$database</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
</DataSource>
</ObjectDefinition>
</Alter>
"@
# Execute the XMLA command using Invoke-ASCmd
Invoke-ASCmd -Server $asServer -Database $database -Query $xmla2
if ($LASTEXITCODE -eq 0)
{
$body= -join ($body,"alert db ($database) connection string successfully.`n" )
}else {
$body= -join ($body,"alert db ($database) connection string failed!!. Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
catch
{
$body= -join ($body,"failed!! `n Error Message:`n`n $($_.Exception.Message) `n`n" )
}
}
$body= -join ($body,"========================================================`n")
$body= -join($body,"結束時間：",(Get-Date -Format "yyyy/MM/dd HH:mm:ss"),"`n")
write-host $body
Send-MailMessage -Encoding "utf8" -SmtpServer "urmailServer" -From '管理員<myadm@mail.com>' -To "$tomail" -Subject "($serverName)每月還原SSAS DB處理結果通知...." -Body $body
