sql server 2005 - exporting nvarchar(max) data
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
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.
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)
精彩评论