openrowset excel欄位超過255個字

select * from openrowset('Microsoft.ACE.OLEDB.16.0','Excel 12.0;HDR=YES;IMEX=1;Database=c:\temp\test.xlsx','select * from [sheet1$] ')
如果test.xlsx中欄位內容超過255個字元，則匯入後欄位內容會被截斷。


調整SQL SERVER主機機碼

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

將TypeGuessRows 改為0。

但可能會影響到效能，因為要掃一遍所有資料列以判斷欄位長度。

在使用的經驗上，openrowset會有卡住問題。 

excel的部份內容有問題

 開啟一個由RDLC匯出的EXCEL檔，顯示『我們發現XXX.xlsx的部份內容有問題。您要我們盡可能嘗試復原嗎?如果您信任此活頁簿的來源，請按一下【是】』。

然後按是之後，又再提示 『已取代的部分: /xl/worksheets/sheet5.xml 部分具有 XML 錯誤。  不合法的 xml 字元。 行 16，欄 19。』

解決方式是將此EXCEL按右鍵解縮後，在檔名目錄下，用記事本找到 /xl/worksheets/sheet5.xml，然後就會看到一個很奇怪的文字了，然後再來源的文字把這個肉眼看不到的文字delete掉，重存檔案就可正常開啟EXCEL了。

這個BS是什麼東東啊，肉眼完全看不到得要解壓打開才看得到，就是後面紅底裡的字  

查看及更改SSAS Server Mode

如何查看已安裝的SSAS  Server Mode?

目前為 Multidimensional 

如要變更為Tabular，開啟c:\Program Files\Microsoft SQL Server\MSAS?.MSSQLSERVER\OLAP\Config\msmdsrv.ini   更改<DeploymentMode>2</DeploymentMode>

2=Tabular

0=Multidimensional

重啟SSAS服務。

Reportviewr 匯出時連線到 vortex.data.microsoft.com

 最近老瓶新裝，在很舊的網站將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 32bit安裝

之前在已安裝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多資料庫

想用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