問題描述

我有一個 Powershell 腳本，它調用 SQL 服務器中的存儲過程并將結果 XML 直接輸出到文件.它適用于較小的測試文件，但會下降到完整大小(~1.5gb 文件).

I have a Powershell script that calls a stored procedure in SQL server and outputs the resulting XML directly to a file. It works for a smaller test file but falls down with the full size (~1.5gb file).

存儲過程工作正常.我可以在 SQL 服務器中調用它 - 問題是我必須在 SQL 服務器中打開它，然后手動將它保存到一個文件，然后編輯該文件以刪除換行符.當我運行腳本時，它會在最后嘗試刪除中間文件時崩潰.但是，當我一行一行地運行它時，它會落在 invoke-sql 行.

The stored procedure works fine. I can call it within SQL server - the problem is that I have to open it in the SQL server then manually save it to a file, then edit that file to remove newlines. When I run the script it falls down when it tries to delete the intermediary files at the end. However, when I run it line by line it falls down at the invoke-sql line.

#net use S: "\\processStore\projects"

# Create variables
$SQLquery = "DECLARE @return_value int; EXEC @return_value = [XML].[XMLdata];"
$outpath = "D:\MyDocuments\XML\XML files" 
$outfile = "TestOutput"

# Run the SQL command and store the object to a variable. Need to extend the timeout from the default.
$sql = invoke-sqlcmd -Database 'APP2021' -Query $SQLquery -serverinstance 'statdata' -QueryTimeout 100000

cd C:

# Store the SQL output as an interim text file
$sql.{XML_F52E2B61-18A1-11d1-B105-00805F49916B} | out-file -Filepath "$outpath\$outfile.txt" 

# Remove Linebreaks
(Get-Content "$outpath\$outfile.txt" -Raw).Replace("`r`n","") | Set-Content "$outpath\${outfile}_del.xml" -Force
format-xml "$outpath\${outfile}_del.xml" | Set-Content "$outpath\$outfile.xml" -Force

# Delete interim text files
del "$outpath\$outfile.txt"
del "$outpath\${outfile}_del.xml"

當一行一行地運行時，代碼在 invoke-sqlcmd 行出現錯誤:

When running lines one by one the code falls down at the invoke-sqlcmd line with the error:

invoke-sqlcmd : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote 
host.)
At line:1 char:8
+ $sql = invoke-sqlcmd -Database 'APP2021' -Query $SQLquery -serverinst ...
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

推薦答案

Change from
invoke-sqlcmd -Database 'APP2021' -Query $SQLquery -serverinstance 'statdata' -QueryTimeout 100000
到
invoke-sqlcmd -Database 'APP2021' -Query $SQLquery -serverinstance 'statdata' -QueryTimeout 0

Change from
invoke-sqlcmd -Database 'APP2021' -Query $SQLquery -serverinstance 'statdata' -QueryTimeout 100000
to
invoke-sqlcmd -Database 'APP2021' -Query $SQLquery -serverinstance 'statdata' -QueryTimeout 0

設置 QueryTimeout = 0 將防止查詢超時

這篇關于使用 Powershell 從 SQL 輸出大型 XML 文件的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！