August 4, 2015 at 6:22 am
Friends,
I am trying to logging properties of the ssrs reports. After googling , got a script which I could find few of the details like report name, report parameters, createddate but not able to get the source query. Can some one help me in finding the source query of a report?
any suggestions would be really appreciated.
Thanks,
Charmer
August 4, 2015 at 7:03 am
If you open the report as an XML file, you'll see the <DataSets> section with <Query> delimiter. So you'd just use XQuery to grab the contents of that part.
August 4, 2015 at 10:05 pm
Hi Pietlinden,
I noticed that all the reports are logged by default in the SQL server. Is that possible to get the source query from any of the logging tables? I believe it is stored some where in the log tables? isn't it?
Thanks,
Charmer
August 5, 2015 at 5:41 am
Googlefu turned up this:
select
Catalog.name,
cat1.Name datasource
from
Catalog
join DataSource
on Catalog.ItemID = DataSource.ItemID
join Catalog cat1
on DataSource.Link = cat1.ItemID
where
Catalog.Type = 2
from http://dba.stackexchange.com/questions/6887/how-can-i-tell-which-data-sources-are-being-used-in-ssrs
August 5, 2015 at 6:11 am
;WITH XMLNAMESPACES (
DEFAULT ''http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'',
''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd --ReportDefinition
)
SELECT
CreatedBy
,CreationDate
,ModifiedBy
,ModifiedDate
,NAME
,PATH
,x.value (''@Name'', ''VARCHAR(100)'') AS ReportParameterName
,x.value (''DataType[1]'', ''VARCHAR(100)'') AS DataType
,x.value (''AllowBlank[1]'', ''VARCHAR(50)'') AS AllowBlank
,x.value (''Prompt[1]'', ''VARCHAR(100)'') AS Prompt
,x.value (''Hidden[1]'', ''VARCHAR(100)'') AS Hidden
,x.value (''data(DefaultValue/Values/Value)[1]'', ''VARCHAR(100)'') AS Value
,x1.value(''data(Fields/Field)[1]'',''VARCHAR(max)'') AS DataSetFieldName
,x1.value (''@Name'', ''VARCHAR(100)'') AS DataSetName
,x1.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'') As DataSourceName
,x2.value(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'') AS ConnectionString
,x3.value(''UsedInQuery[1]'', ''VARCHAR(250)'') AS UsedInQuery
,x3.value(''CommandType[1]'', ''VARCHAR(50)'') AS CommandType
,x3.value(''CommandText[1]'',''VARCHAR(50)'') AS CommandText
FROM (
SELECT
U.UserName AS CreatedBy
,C.CreationDate
,UM.UserName As ModifiedBy
,ModifiedDate
,NAME
,PATH
,CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM Reportserver.dbo.Catalog C
JOIN Reportserver.dbo.Users U
ON C.CreatedByID = U.UserID
JOIN Reportserver.dbo.Users UM
ON c.ModifiedByID = UM.UserID
WHERE CONTENT IS NOT NULL AND TYPE = 2
) A
CROSS APPLY ReportXML.nodes(''/Report/ReportParameters/ReportParameter'') R(x)
CROSS APPLY ReportXML.nodes(''/Report/DataSets/DataSet'') r1(x1)
CROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r2 ( x2 )
CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet/Query'') r3(x3)
Where NAME = '''+@Report+'''
--Use the where clause above to look for a specific report
ORDER BY NAME
I am using this script. I am able to see all the data source if it is executed through SP.
But if it is executed through query, I could not get it.
Can you suggest me ?
Thanks,
Charmer
August 5, 2015 at 6:53 am
I guess you need to explain the context in which you are doing this. Why is creating a stored procedure and executing it not an option? Seems silly to rewrite something like this all the time. Just write it once, test it, and then use it. Maybe add some parameters so you can filter for what reports/whatever you want....
August 5, 2015 at 9:11 pm
Yes I am using a parameter to filter the reports. All the time, it is going to be a SP but in case if any reports are using direct query, and that is my concern.
After all, what I am doing with this is for auditing purpose. I have to give all the details about the reports deployed in the report server.
for eg: reportname, parameters used, data source, execution time, how long it has not been used, who created, so and so.
Thanks,
Charmer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply