sql server 2005 - exporting nvarchar(max) data

Question

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)?

Thanks!

Christian

Answer 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.

Answer 2

I ususally use Powershell for this kind of stuff.

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.

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