[SQL]從 ReportServer DB中取回 RDL 檔案的內容

2015-09-03

2359
0
SQL

要如何從 ReportServer DB中取回 RDL 檔案的內容呢?

最近同事在詢問說，平時修改報表都是透過 Browser 直接修改 Reporting Server 的 Report。

如果要將裡面的 Report 匯出來的話，要如何做呢 ?

在網路上找到「Extracting SSRS Reports (RDL files) directly from the database 」這篇。

它可以取回 ReportServer DB 中 RDL 檔案的內容，如下，

--http://www.sbrickey.com/Tech/Blog/Post/Extracting_SSRS_Reports_RDL_files_directly_from_the_database
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH    ItemContentBinaries
          AS ( SELECT   ItemID ,
                        Name ,
                        [Type] ,
                        CASE Type
                          WHEN 2 THEN 'Report'
                          WHEN 5 THEN 'Data Source'
                          WHEN 7 THEN 'Report Part'
                          WHEN 8 THEN 'Shared Dataset'
                          ELSE 'Other'
                        END AS TypeDescription ,
                        CONVERT(VARBINARY(MAX), Content) AS Content
               FROM     ReportServer.dbo.Catalog
               WHERE    Type IN ( 2, 5, 7, 8 )
             ),


--The second CTE strips off the BOM if it exists...
        ItemContentNoBOM
          AS ( SELECT   ItemID ,
                        Name ,
                        [Type] ,
                        TypeDescription ,
                        CASE WHEN LEFT(Content, 3) = 0xEFBBBF
                             THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
                                                              LEN(Content)))
                             ELSE Content
                        END AS Content
               FROM     ItemContentBinaries
             )
    --The outer query gets the content in its varbinary, varchar and xml representations...

SELECT  ItemID ,
        Name ,
        [Type] ,
        TypeDescription ,
        Content --varbinary
        ,
        CONVERT(vARCHAR(MAX), Content) AS ContentVarchar --varchar
        ,
        CONVERT(XML, Content) AS ContentXML --xml
FROM    ItemContentNoBOM
--如果只找某一張Report請下報名表單來篩選
WHERE Name = N'你的報表名稱';

當取出資料後，就可以把 ContentXML 的內容，最上面加上 <?xml version="1.0" encoding="utf-8"?> 後，

另存成 RDL 檔案就可以了，如下，