2

I have been twiddling with a fairly simple idea to export ReportServer reports from the underlying database and then find its dependent stored procedures and export them as well.

However, when testing initially I found that the XML data for the report itself is truncated in the standard way I export things to files, and I think I may be using an incorrect method.

The code is fairly simple at this point, and I am using a simplified report called "ChartReport":

Import-Module 'sqlps'
$saveloc = "$home\savedata\filename.txt"

$dataquery = @"
        DECLARE @name NVARCHAR(MAX)='ChartReport',
                @path NVARCHAR(MAX) = '/ChartReport'
    SELECT CAST(CAST(c.Content AS VARBINARY(MAX)) AS XML) [ReportData], c.Name, c.Path
    FROM ReportServer.dbo.Catalog c
    WHERE   c.Name = @name
    AND c.Path  LIKE @path+'%'
"@

Invoke-SQLCMD -Query $dataquery | select ReportData | Out-File $saveloc

I have verified the query returns XML (The underlying XML file itself is over 25000 characters, and I would be happy to provide a link to it if anyone is interested), however when I save the file I get something like:

Column1

<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsof...

I have attempted to use some of the ideas already posted on SO, such as:

> $somefile Powershell 2: Easy way to direct every bit of output to a file?

out-file and specifying width Powershell Add-Content Truncating Output

Using the format-table with -autosize and -wrap

Each of these fail at some point (though the format-table method gets pretty far before it truncates).

I would definitely consider some sort of XML specific solution, but really I think it is just that I am missing some information. As far as I am considering, this is a file of "stuff" and I want to write said file to the disk after it is loaded into the object.

Would iterating over some sort of line break and writing each line of the object to a file be the idiomatic answer?

Community
  • 1
  • 1
ConstantineK
  • 352
  • 4
  • 19
  • Why are you using select `ReportData`? Does it work if you do `Invoke-SQLCMD -Query $dataquery | Out-File $saveloc` or perhaps `Invoke-SQLCMD -Query $dataquery | Foreach {$_.ReportData} | Out-File $saveloc` – Keith Hill Dec 19 '13 at 05:53
  • Thanks for the response @KeithHill, I was using select report data to get just that column, but obviously I could have skipped that step and just use the query. The first statement you propose ends with the same truncation I use in my example, and the second ends with truncation, giving the same output as the -autosize -wrap example. The file ends up being 4002 characters. – ConstantineK Dec 19 '13 at 15:44
  • Let's see where the truncation happens. What does this return `Invoke-SqlCmd -Query $dataquery | Get-Member`? If that is a string type, what does this return `(Invoke-SqlCmd -Query $dataquery).Length`? – Keith Hill Dec 19 '13 at 16:23
  • @KeithHill Sorry for the delay, I was out of town :) TypeName: System.Data.DataRow for the variable, System.String for the ReportData itself, and 4000 is the length of the string, which makes sense with some of the items I have seen. I think can change the max length of the string based on some googling I have seen, but I still would have the problem if the report data was greater than the system max string length in .net – ConstantineK Dec 24 '13 at 00:59
  • It is kind of hard to tell what exactly is going on here. Can you execute this `Invoke-SQLCMD -Query $dataquery | Export-CliXml query.txt` and then post that somewhere it can be downloaded? If the file is big, go ahead and ZIP it. – Keith Hill Dec 24 '13 at 01:51
  • @KeithHill Sure Keith, I will gist the original stored procedure, and the produced content ASAP. (Probably tomorrow) Thanks for your persistence! – ConstantineK Dec 24 '13 at 16:33

1 Answers1

2

Use -MaxCharLength parameter of Invoke-SQLCMD command. By default it 4000.

See Invoke-SqlCmd doesn't return long string?

Community
  • 1
  • 1
vitrilo
  • 1,437
  • 16
  • 20