sql server 2005 - 导出nvarchar（max）数据

I would like to run a query and save the results as tab separated file. This is all no problem but in:

我想运行一个查询并将结果保存为制表符分隔文件。这一切都没有问题,但在:

Query -> Query Options -> Results -> Text -> Maximum number of characters in each column

查询 - >查询选项 - >结果 - >文本 - >每列中的最大字符数

I can only select 8192 characters as maximum. This may not be enough. Is there a way to ensure that all characters are included if the column is nvarchar(max)?

我最多只能选择8192个字符。这可能还不够。如果列是nvarchar(max),有没有办法确保包含所有字符?

Thanks!

Christian

2 个解决方案

#1

Right click on database in Management studio, Tasks => Export Data. Set the destination to a "Flat file destination" and then chose to write the query to export.

右键单击Management studio中的数据库,Tasks => Export Data。将目标设置为“平面文件目标”,然后选择写入要导出的查询。

#2

I ususally use Powershell for this kind of stuff.

我通常使用Powershell来做这种事情。

Here is a script, feel free to adjust for you needs. I'm assuming that your nvarchar(max) does not have line breaks, otherwise tab separated file doesn't make much sense.

这是一个脚本,随时可以根据您的需要进行调整。我假设你的nvarchar(max)没有换行符,否则tab分隔的文件没有多大意义。

##---[ Script Settings ]-------------------------------------------------------------------------------------------------------------
$sqlServer = "localhost"
$targetDbName = "AdventureWorks2008"
$reportName = "c:\result.txt"

##---[ Common Functions ]------------------------------------------------------------------------------------------------------------
function Get-Dataset {
param($sqlQuery, $sqlServer, $sqlCatalog)

  # Setup SQL Connection
  $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $sqlConnection.ConnectionString = "Server = $sqlServer; Database = $sqlCatalog; Integrated Security = True"

  # Setup SQL Command
  $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $sqlCmd.CommandText = $sqlQuery
  $sqlCmd.Connection = $sqlConnection
  $sqlCmd.CommandTimeout = 0

  # Setup .NET SQLAdapter to execute and fill .NET Dataset
  $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $sqlAdapter.SelectCommand = $sqlCmd
  $dataSet = New-Object System.Data.DataSet

  #Execute and Get Row Count
  $nRecs = $sqlAdapter.Fill($dataSet)
  $sqlConnection.Close();
  $dataSet
}

##---[ Main ]------------------------------------------------------------------------------------------------------------------------

$dataset = Get-DataSet "SELECT * From DatabaseLog" $sqlServer $targetDbName 

$dataset.tables[0].rows |
  #Format-Table -auto | Out-File $reportName -width 100000
  ConvertTo-CSV -Delimiter "`t" -NoTypeInformation| Out-File $reportName

& ($reportName)

#1