我一直在嘗試使用 PS 腳本從 SQL Server 資料庫中提取檔案（pdf、word 等）。我遇到了下面的 PowerShell 腳本。腳本運行并填充目標檔案夾，但所有檔案都是 0 位元組，并且在腳本執行期間。它拋出錯誤：
“從 FILESTREAM 容器匯出物件：.docx 使用“5”個引數呼叫“GetBytes”的例外：“對列‘擴展’的 GetBytes 的嘗試無效。GetBytes 函式只能用于型別為 Text、NText 或 Image 的列。""
誰能指出我做錯了什么以及如何解決這個問題？非常感激。
$Server = ".\xxxxxx";
$Database = "xxxxxx";
$Dest = "C:\DATA\";
$bufferSize = 8192;
$Sql = "
SELECT
[extension]
FROM [XXXXXXXX].[dbo].[XXXXXXdocuments]
";
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$Server;"
"Integrated Security=True;"
"Initial Catalog=$Database";
$con.Open();
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) ": Started ...");
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;
$cmd.CommandTimeout = 120
$rd = $cmd.ExecuteReader();
$out = [array]::CreateInstance('Byte', $bufferSize)
While ($rd.Read())
{
try
{
Write-Output ("Exporting Objects from FILESTREAM container: {0}" -f $rd.GetString(0));
$fs = New-Object System.IO.FileStream ($Dest $rd.GetString(0)), Create, Write;
$bw = New-Object System.IO.BinaryWriter $fs;
$start = 0;
enter code here
$received = $rd.Getbytes(0, $start, $out, 0, $bufferSize - 1);
While ($received -gt 0)
{
$bw.Write($out, 0, $received);
$bw.Flush();
$start = $received;
$received = $rd.Getbytes(0, $start, $out, 0, $bufferSize - 1);
}
$bw.Close();
$fs.Close();
}
catch
{
Write-Output ($_.Exception.Message)
}
finally
{
$fs.Dispose();
}
}
$rd.Close();
$cmd.Dispose();
$con.Close();
Write-Output ("Finished");
Read-Host -Prompt "Press Enter to exit"
uj5u.com熱心網友回復：
BinaryWriter 是不必要的。它用于將原始型別寫入 Stream。
并且沒有必要處理緩沖區；你可以簡單地使用SqlDataReader.GetStream(int).CopyTo(Stream)，例如
$Server = "localhost";
$Database = "adventureworks2017";
$Dest = "C:\temp\";
$Sql = "
SELECT concat('photo', ProductPhotoID, '.jpg') name, LargePhoto from Production.ProductPhoto
";
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$Server;Integrated Security=True;Initial Catalog=$Database;TrustServerCertificate=true";
$con.Open();
Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) ": Started ...");
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;
$cmd.CommandTimeout = 120
$rd = $cmd.ExecuteReader();
While ($rd.Read())
{
try
{
Write-Output ("Exporting: {0}" -f $rd.GetString(0));
$fs = New-Object System.IO.FileStream ($Dest $rd.GetString(0)), Create, Write;
$rd.GetStream(1).CopyTo($fs)
$fs.Close()
}
catch
{
Write-Output ($_.Exception.Message)
}
finally
{
$fs.Dispose();
}
}
$rd.Close();
$cmd.Dispose();
$con.Close();
Write-Output ("Finished");
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/374232.html
標籤：sql sql-server 电源外壳
上一篇：如何從顯示名發送郵件？