how to save the ssrs queries in sql server database? [Resolved]

Posted by Chamu under Sql Server on 11/9/2016 | Points: 10 | Views : 184 | Status : [Member] | Replies : 1
how to save the ssrs queries in sql server database?




Responses

Posted by: Rajnilari2015 on: 11/10/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Report definitions(.rdl) are stored in the catalog table of ReportServer database.

The below query will extract all the information (including queries) from catalog table of report server and store them into a temp table

--clean up

IF OBJECT_ID('tempdb..#MySSRSQueriesTable') IS NOT NULL DROP Table #MySSRSQueriesTable

;WITH XMLNAMESPACES (

DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT

name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText

INTO #MySSRSQueriesTable

FROM (

select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)


Equally you can look into this article ( http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/ ) for Querying the RDL Contents
Hope that helps

--
Thanks & Regards,
RNA Team

Chamu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response