Sql server 2005 - export nvarchar (max) data

Question

I would like to run the query and save the results as a file with section separators. This is not a problem, but in:

Query → Query Parameters → Results → Text → Maximum Number of Characters in Each Column

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

Thank!

Christian

Answer 1

Right-click on the database in the management studio, Tasks => Export Data. Set the destination to "Flat file destination", and then select the entry to export.

Answer 2

Powershell .

script, . , nvarchar (max) , .

##---[ 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)