InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 03:03:31 GMT20http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxGo right ahead, Gary -- your thank you is enough!I'd also look back over the comments. MaricopaJoe has made some significant additions to his version of these reports and posted his code changes in the comments -- Sadly I haven't incorporated them into the base set in the download.Thu, 09 May 2013 06:09:10 GMTmichael_davis2http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThank you for the reports.I downloaded the zip file, extracted the rdl files and imported them into a reporting project.I like the design and choice of color for the reports.They provided excellent information about the reports.I currently have them on my desktop machine.Does the fact that these report, being released to the public, mean we are allowed to use them on a production server?I don't use anything in production that is not licensed. Thanks GaryWed, 08 May 2013 16:19:11 GMTGFhttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxTop job MichaelWed, 17 Apr 2013 23:21:14 GMTRamesh-469611http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThanks for sharing your RDL's for the Report Server management report and great job. It was very helpful and helped resolve most of my issues promptly. Regards.Thu, 28 Feb 2013 09:08:19 GMTzymoshttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxTry this...then circle back to fill the nulls (linked datasources)SELECT DISTINCT Cat.Name AS Reportname, dst.Name AS DataSetName, Dsc.Name AS DataSourceName, Cat.PathFROM DataSets AS dst RIGHT OUTER JOIN Catalog AS Cat ON dst.ItemID = Cat.ItemID LEFT OUTER JOIN DataSource AS Dsc ON Cat.ItemID = Dsc.ItemIDWHERE (Cat.Type = 2)ORDER BY ReportnameThu, 27 Sep 2012 15:11:29 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThanks for the info but to be more specific, it's this section of the ReportInventory report query that's giving us the headache:(select top 1 cat1.Namefrom Catalog join DataSource on Catalog.ItemID = DataSource.ItemID join Catalog cat1 on DataSource.Link = cat1.ItemID where Catalog.Type = 2 and Catalog.ItemID = c.ItemID ) as dataSourceName,because it's a subquery it is limited to return one row even if multiple data sources are in place. We have tried to break it out and use a temp table to hold the data initially and then query from there but have not been successful.Thu, 27 Sep 2012 14:18:41 GMTrpriesinghttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxYou can start by running this query.SELECT Dsc.name DataSourceName, Cat.path, Cat.name AS Reportname FROM catalog AS Cat WITH(nolock) INNER JOIN datasource AS Dsc WITH(nolock) ON Cat.itemid = Dsc.itemidWed, 26 Sep 2012 13:18:17 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxLove the reports and for the most part they have been easy to add additional columns (Linked Report, Linked To, Hidden). One thing that we are trying to get working is the showing of each datasource within a report. Currently the report shows multiple lines for the reports that have multiple datasources but the datasource name shows the same one for each because of the subquery selecting Top 1. If this has been addressed I appologize, I haven't seen it in the posts.ThanksWed, 26 Sep 2012 12:20:35 GMTrpriesinghttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxI can't advise you in deploying in such a manner, unless you are using some sort of custom .net application with the report viewer control.Those reports are designed to be deployed via BIDS and only BIDS.Assuming you are running in native mode, have you attempted to upload the reports manually through the report manager as described below.1.Go to Report Manager 2.Too keep all clean, you can create a folder in which you will keep all your reports 3.Create the data source, if not yet existing, by selecting the New Data Source link. ◦Specify Microsoft SQL Server as the Connection Type. ◦Specify the database Connection String as follows: data source=[Name of database server SQL Instance];initial catalog=[Name of database] ◦Connect Using the Credentials stored securely in the report server and specify the user name and password used to connect to SQL Server instance installed in the database server.4.Upload the report by selecting the Upload File link and navigate to the RDL file stored in the system. 5.Using Show Details view, edit the uploaded reports by selecting the Edit link. 6.Under the Data source link, assign the previously created shared datasource as the source of data for the report. Note: The “Apply” button needs to be selected to apply the changes made for the report. Wed, 19 Sep 2012 10:46:51 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxno, the person that used to handle the report building used VS2010 with TFS source control. We just kept following that process.Wed, 19 Sep 2012 10:27:41 GMTandres.jordanhttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxAre you no longer deploying them through bids?Tue, 18 Sep 2012 16:17:21 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxHopefully you guys can help me, we have been using the diagnostics reports for a long time. But recently we started deploying reports directly from VS2010 to SSRS 2008 R2 and for some reason any of the reports deployed this way do not show up at all when we run the "ReportQueries" if we run any of the other diagnostic reports all of the reports show up.Tue, 18 Sep 2012 12:36:34 GMTandres.jordanhttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxGreat - thanks for the info!!!Fri, 25 May 2012 06:14:09 GMTmichael_davis2http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxYes, these reports will work fine in 2012. With sharepoint intergration the ExecutionlogView3 is on the farm where the reportserverdb lives.This is the primary view for these reports.Thu, 24 May 2012 15:16:03 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxI don't know - we're just getting ready to go to 2012 soon (and when I do, you can bet these will be one of the first things I check out).In the reading I have done, it sounds like SSRS pretty much will be in Sharepoint Integration mode from the start (I may be wrong here but that was the impression I got). Due to that, it may be possible that the repository could be structured differently. Microsoft never blessed accessing the database directly -- so I wouldn't be surprised if there were changes. If you come across the answer before I do, I'd appreciate hearing what it is.Thanks!!!Thu, 24 May 2012 13:01:49 GMTmichael_davis2http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxAny idea if this works with SSRS 2012?Thu, 24 May 2012 09:53:53 GMTmatt.olsonhttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxI was looking manually in some of the report server tables just yesterday and I noticed what a wealth of useful information was contained within them. I was going to create my own reports but these are far more comprehensive!As an additional bonus I can learn some tricks from the formatting and features in these reports to apply to my own reports.Thanks for this most useful collection of reports and interesting article.Wed, 01 Feb 2012 03:53:02 GMTRob-350472http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxAnother fun XML query. This one will get the command text for Datasets in the report:[b]Create a Query to Get the Report Datasets Using the GetReportDefintion Method[/b][ul][li]Right click on the Shared Datasets folder.[/li][li]Select Add New Dataset.[/li][li]Set the Name to ReportService2005_GetReportDefinition_ReportDataSources.[/li][li]Set the Data source to ReportService2005WebServiceAPI.[/li][li]Set the Query type to Text.[/li][li]Place the following code into the Query:[code="xml"]<Query> <Method Name="GetReportDefinition" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> <Parameters> <Parameter Name="Report"/> </Parameters> </Method> <ElementPath IgnoreNamespaces="true"> GetReportDefinitionResponse/Definition(Base64Encoded)/Report{}/DataSets/DataSet { @Name(string), } /Query { CommandText(string), DataSourceName(string) } </ElementPath></Query>[/code][/li][li]Go to the Parameters page.[/li][li]Click OK on the warning that about "Could not create a list of fields for the query."[/li][li]Click Add.[/li][li]Set the Name to Report.[/li][li]Set the Data Type to Text.[/li][li]Go back to the Query page.[/li][li]Click Refresh Fields.[/li][li]Enter the path to a data source, i.e. /MyReportsFolder/MyReport, in the Parameter Value for DataSource.[/li][li]Enter true in the Parameter Value for Item.[/li][li]Click OK.[/li][/ul]Wed, 18 Jan 2012 12:08:15 GMTjcoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspx[quote][b]michael_davis2 (1/12/2012)[/b][hr]Awesome stuff James!!!This is a really great find and is in many ways a holy-grail find for what we've been trying to do!!![/quote]Thanks! FYI, I've updated the prior post to include one more query set that can be used to fetch the Report Data Sources that have a connection string embedded in the report as well. I've managed to work it out in such a way that the whole thing can be resolved without ever touching the database directly.Thu, 12 Jan 2012 16:04:14 GMTjcoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxAwesome stuff James!!!This is a really great find and is in many ways a holy-grail find for what we've been trying to do!!!Thu, 12 Jan 2012 08:53:34 GMTmichael_davis2http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxHi Mike,I've done some further tinkering (trying to find a way to get those Data Sources) and figured out something really cool! You can access the Reporting Service Web Services from a report in Reporting Services. (How's that for a tongue-twister?)Check the bit of fun below out for a Reports project in BIDS 2008 R2. If you're using plain 2008, shared datasets are not supported. You will need to follow a very similar process to create the Datasets directly in the reports.[b]Create an XML Data Source for the Report Service Web Service[/b][ul][li]Right click on the Shared Data Sources folder.[/li][li]Select Add New Data Source.[/li][li]Set the Name to ReportService2005WebServiceAPI.[/li][li]Set the Type to XML.[/li][li]Set the connection string to http://MyReportServer:80/ReportServer/ReportService2005.asmx.[/li][li]Go to the Credentials page.[/li][li]Choose Use Windows Authentication (integrated security).[/li][li]Click OK.[/li][/ul][b]Create a Query to the List Children Method[/b][ul][li]Right click on the Shared Datasets folder.[/li][li]Select Add New Dataset.[/li][li]Set the Name to ReportService2005_ListChildren.[/li][li]Set the Data source to ReportService2005WebServiceAPI.[/li][li]Set the Query type to Text.[/li][li]Place the following code into the Query:[code="xml"]<Query> <Method Name="ListChildren" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> <Parameters> <Parameter Name="Item"/> <Parameter Name="Recursive"/> </Parameters> </Method> <ElementPath xmlns="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> ListChildrenResponse/CatalogItems/CatalogItem { ID(string), Name(string), Path(string), VirtualPath(string), Type(string), Size(integer), Description(string), Hidden(boolean), CreationDate(date), ModifiedDate(date), CreatedBy(string), ModifiedBy(string), MimeType(string), ExecutionDate(date) } </ElementPath></Query>[/code][/li][li]Go to the Parameters page.[/li][li]Click OK on the warning that about "Could not create a list of fields for the query."[/li][li]Click Add.[/li][li]Set the Name to Item.[/li][li]Set the Default Value to /.[/li][li]Set the Data Type to Text.[/li][li]Click Add.[/li][li]Set the Name to Recursive.[/li][li]Set the Default Value to true. (Case must be exact.)[/li][li]Set the Data Type to Boolean.[/li][li]Go back to the Query page.[/li][li]Click Refresh Fields.[/li][li]Enter / in the Parameter Value for Item.[/li][li]Enter true in the Parameter Value for Item.[/li][li]Click OK.[/li][/ul][b]Create a Query to the GetItemDataSources Method[/b][ul][li]Right click on the Shared Datasets folder.[/li][li]Select Add New Dataset.[/li][li]Set the Name to ReportService2005_GetItemDataSources.[/li][li]Set the Data source to ReportService2005WebServiceAPI.[/li][li]Set the Query type to Text.[/li][li]Place the following code into the Query:[code="xml"]<Query> <Method Name="GetItemDataSources" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> <Parameters> <Parameter Name="Item" Type="string"/> </Parameters> </Method> <ElementPath xmlns="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> GetItemDataSourcesResponse/DataSources/DataSource { Name(string) } /DataSourceReference { Reference(String) } </ElementPath></Query>[/code][/li][li]Go to the Parameters page.[/li][li]Click OK on the warning that about "Could not create a list of fields for the query."[/li][li]Click Add.[/li][li]Set the Name to Item.[/li][li]Set the Data Type to Text.[/li][li]Go back to the Query page.[/li][li]Click Refresh Fields.[/li][li]Enter the path to a report or model, i.e. /MyReportFolder/MyReport, in the Parameter Value for Item.[/li][li]Enter true in the Parameter Value for Item.[/li][li]Click OK.[/li][/ul][b]Create a Query to the GetDataSourceContents Method[/b][ul][li]Right click on the Shared Datasets folder.[/li][li]Select Add New Dataset.[/li][li]Set the Name to ReportService2005_GetDataSourceContents.[/li][li]Set the Data source to ReportService2005WebServiceAPI.[/li][li]Set the Query type to Text.[/li][li]Place the following code into the Query:[code="xml"]<Query> <Method Name="GetDataSourceContents" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> <Parameters> <Parameter Name="DataSource"/> </Parameters> </Method> <ElementPath xmlns="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> GetDataSourceContentsResponse/Definition { Extension(string), ConnectString(string), UseOriginalConnectString(boolean), OriginalConnectStringExpressionBased(boolean), OriginalConnectString(string), CredentialRetrieval(string), WindowsCredentials(boolean), ImpersonateUser(boolean), Prompt(string), UserName(string), Enabled(boolean) } </ElementPath></Query>[/code][/li][li]Go to the Parameters page.[/li][li]Click OK on the warning that about "Could not create a list of fields for the query."[/li][li]Click Add.[/li][li]Set the Name to DataSource.[/li][li]Set the Data Type to Text.[/li][li]Go back to the Query page.[/li][li]Click Refresh Fields.[/li][li]Enter the path to a data source, i.e. /MyDataSourceFolder/MyDataSource, in the Parameter Value for DataSource.[/li][li]Enter true in the Parameter Value for Item.[/li][li]Click OK.[/li][/ul][b]Create a Query to Get the Report Data Sources Using the GetReportDefintion Method[/b][ul][li]Right click on the Shared Datasets folder.[/li][li]Select Add New Dataset.[/li][li]Set the Name to ReportService2005_GetReportDefinition_ReportDataSources.[/li][li]Set the Data source to ReportService2005WebServiceAPI.[/li][li]Set the Query type to Text.[/li][li]Place the following code into the Query:[code="xml"]<Query> <Method Name="GetReportDefinition" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices"> <Parameters> <Parameter Name="Report"/> </Parameters> </Method> <ElementPath IgnoreNamespaces="true"> GetReportDefinitionResponse/Definition(Base64Encoded)/Report{}/DataSources/DataSource { @Name(string), DataSourceReference(string), DataSourceID(string), SecurityType(string) } /ConnectionProperties { IntegratedSecurity(string), ConnectString(string), DataProvider(string) } </ElementPath></Query>[/code][/li][li]Go to the Parameters page.[/li][li]Click OK on the warning that about "Could not create a list of fields for the query."[/li][li]Click Add.[/li][li]Set the Name to Report.[/li][li]Set the Data Type to Text.[/li][li]Go back to the Query page.[/li][li]Click Refresh Fields.[/li][li]Enter the path to a data source, i.e. /MyReportsFolder/MyReport, in the Parameter Value for DataSource.[/li][li]Enter true in the Parameter Value for Item.[/li][li]Click OK.[/li][/ul]You can then create appropriate reports and subreports to fully resolve datasource information.There are very few things in the report server that cannot be accessed this way. See the following documents on MSDN for more information on:[url=http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.aspx]ReportingService2005 Class[/url][url=http://msdn.microsoft.com/en-US/library/aa964129(v=SQL.90).aspx]Reporting Services: Using XML and Web Service Data Sources[/url][url=http://msdn.microsoft.com/en-us/library/ms345251(v=SQL.105).aspx]XML Query Syntax for Specifying XML Report Data (SSRS)[/url][url=http://msdn.microsoft.com/en-us/library/ms365158.aspx]Element Path Syntax for Specifying XML Report Data (SSRS)[/url][url=http://msdn.microsoft.com/en-us/library/dd297486(SQL.100).aspx]SQL Server RDL Specification[/url]Hopefully this is as entertaining for you as it was for me. I cheered when I figured this out!James.Wed, 11 Jan 2012 17:27:27 GMTjcoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxMike. Here is a couple more to add to this package. I find myself dealing with the GUID's a lot for the ReportCaching that we are using. I've included the view and report.Wed, 14 Sep 2011 16:49:07 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxJust deployed this and it worked perfectly. It was exactly what we needed. Thanks so much for sharing!!:-DThu, 28 Apr 2011 11:14:11 GMTDancesWithChickenshttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspx[quote][b]michael_davis2 (11/9/2010)[/b][hr]thanks jcoe - i'll have to take a look at the data source as that is something in particular we're looking to include. the problem we have encountered is that the name (which we would report on) isn't always the true data source being used by the report. see below:<DataSource Name="ds_source1"> <rd:DataSourceID>f065cf10-e585-4c14-9fe2-48e8ace9cb4e</rd:DataSourceID> <DataSourceReference>\Data Sources\ds_source2</DataSourceReference></DataSource>in the above example, the code we previously instituted would display the "ds_source1" value for the data source name...while in fact the actual data source is "ds_source2". we have seen this happen as the data source name often gets populated by default during development -- this name is also not always consistent with the prod configuration. as of yet my attempts to get to the actual data source name (and connection string) haven't worked as those are encrypted.i will check your code to see if i get different results. i wouldn't expect the connection string to come from this...but getting the true data source name would be a win. thanks again[/quote]I've done some more tinkering on this today. You can combine my prior query with this one to further resolve data sources:[code="sql"];WITH ReportItems (ItemID, Name, Path, reportXml) AS( SELECT ItemID , Name , Path , CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml FROM Catalog WHERE (Type = 2) --AND ( -- (@reportID = CONVERT(varchar(500), ItemID)) -- OR (@reportID = 'NOID') -- ))SELECT ItemID , Name , Path , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/@Name)[1]', 'nvarchar(MAX)') AS dataSourceName , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/DataSourceReference/text())[1]', 'nvarchar(MAX)') AS dataSourceReference , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/ConnectionProperties/DataProvider/text())[1]', 'nvarchar(MAX)') AS embeddedDataProvider , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/ConnectionProperties/ConnectString/text())[1]', 'nvarchar(MAX)') AS embeddedConnectString , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/ConnectionProperties/IntegratedSecurity/text())[1]', 'nvarchar(MAX)') AS embeddedIntegratedSecurity , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"; (DataSource/rd:SecurityType/text())[1]', 'nvarchar(MAX)') AS designerSecurityTypeFROM ReportItems CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //DataSources') AS report(datasource)UNION ALLSELECT ItemID , Name , Path , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/@Name)[1]', 'nvarchar(MAX)') AS dataSourceName , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/DataSourceReference/text())[1]', 'nvarchar(MAX)') AS dataSourceReference , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/ConnectionProperties/DataProvider/text())[1]', 'nvarchar(MAX)') AS embeddedDataProvider , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/ConnectionProperties/ConnectString/text())[1]', 'nvarchar(MAX)') AS embeddedConnectString , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/ConnectionProperties/IntegratedSecurity/text())[1]', 'nvarchar(MAX)') AS embeddedIntegratedSecurity , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"; (DataSource/rd:SecurityType/text())[1]', 'nvarchar(MAX)') AS designerSecurityTypeFROM ReportItems CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //DataSources') AS report(datasource)[/code]Unfortunately, the connection strings in the DataSource table are encrypted, so joining to that table does no good. It should, however, at least let you cross-reference the DataSourceName used by the query to the DataSourceReference and then look it up on the server manually through the UI. It will also provide connection strings that have been embedded in the report.Tue, 12 Apr 2011 10:34:18 GMTjcoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxMany thanks for sharing this excellent package!I'm running SQL Server 2008 R2 and works great.Tue, 12 Apr 2011 07:19:27 GMTjsmolandhttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxGreat one! I know how much time it would save for one using these analysis reports.ThanksTue, 01 Mar 2011 06:31:28 GMTVijay-154675http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxGreat reports and very easy to set up... Great job and thanks!Mon, 20 Dec 2010 09:54:09 GMTHextallFanForLifehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxthanks jcoe - i'll have to take a look at the data source as that is something in particular we're looking to include. the problem we have encountered is that the name (which we would report on) isn't always the true data source being used by the report. see below:<DataSource Name="ds_source1"> <rd:DataSourceID>f065cf10-e585-4c14-9fe2-48e8ace9cb4e</rd:DataSourceID> <DataSourceReference>\Data Sources\ds_source2</DataSourceReference></DataSource>in the above example, the code we previously instituted would display the "ds_source1" value for the data source name...while in fact the actual data source is "ds_source2". we have seen this happen as the data source name often gets populated by default during development -- this name is also not always consistent with the prod configuration. as of yet my attempts to get to the actual data source name (and connection string) haven't worked as those are encrypted.i will check your code to see if i get different results. i wouldn't expect the connection string to come from this...but getting the true data source name would be a win. thanks againTue, 09 Nov 2010 05:46:01 GMTmichael_davis2http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxHi Mike,I've also come up with a slight restatement of my prior query that allows the datasource that the query runs against to be identified as well:[code="sql"];WITH ReportItems (ItemID, Name, Path, reportXml) AS( SELECT ItemID , Name , Path, CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml FROM Catalog WHERE (Type = 2) AND ( (@reportID = CONVERT(varchar(500), ItemID)) OR (@reportID = 'NOID') ))SELECT ItemID , Name , Path , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSourceName/text())[1]', 'nvarchar(MAX)') AS dataSourceName , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (CommandText/text())[1]', 'nvarchar(MAX)') AS commandTextFROM reportItems CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //Query') AS report(query)UNION ALLSELECT ItemID , Name , Path , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSourceName/text())[1]', 'nvarchar(MAX)') AS dataSourceName , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (CommandText/text())[1]', 'nvarchar(MAX)') AS commandTextFROM reportItems CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //Query') AS report(query)[/code]Mon, 08 Nov 2010 14:39:37 GMTjcoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxHi Mike,I was modifying the reports to provide data to one of our users on only specific reports. While I was going through the queries, it occurred to me that there is a way to more efficiently generate the inactive reports portion of the statistics report by eliminating a join:[code="sql"]SELECT c.Name , c.Path , l.lastRunDateFROM Catalog AS c LEFT OUTER JOIN (SELECT ReportID, MAX(TimeStart) AS lastRunDate FROM ExecutionLog GROUP BY ReportID) AS l ON c.ItemID = l.ReportIDWHERE (c.Type = 2) AND ( (l.lastRunDate IS NULL) OR (l.lastRunDate <= DATEADD(mm, - 6, GETDATE())) )ORDER BY l.lastRunDate DESC , c.Name[/code]Mon, 08 Nov 2010 14:24:26 GMTjcoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThanks a lot Mike! I was just trying to figure out how to write a report server permissions report and these reports worked great for me.Thanks!!Mon, 08 Nov 2010 08:21:04 GMTshabista_rizvihttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThanks for the collaboration Jcoe - I put that query in the report - works great.Fri, 11 Jun 2010 06:04:02 GMTmichael_davis2http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxOUTSTANDING! Thanks so much. This will help a great deal in our migration. Kudos to you!This is a real keeper in my bag of tricks!:-D:-D:-D:-D:-D:-D:-DThu, 10 Jun 2010 16:29:07 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxTry using this as a substitute for the query in ReportQueries.rdl to retrieve SQL queries from reports.;WITH ReportItems (ItemID, Name, Path, reportXml) AS ( SELECT ItemID , Name , Path , CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml FROM Catalog WHERE (Type = 2) AND (@reportID = CONVERT(varchar(500), ItemID) OR @reportID = 'NOID') )SELECT ItemID , Name , Path , report.commandText.value('.', 'nvarchar(MAX)') AS commandTextFROM reportItems CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //CommandText/text()') AS report(commandText)UNION ALLSELECT ItemID , Name , Path , report.commandText.value('.', 'nvarchar(MAX)') AS commandTextFROM reportItems CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //CommandText/text()') AS report(commandText)Thu, 10 Jun 2010 09:59:40 GMTjcoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThanks Michael. Not to be a pest, but I am getting the following error:Msg 9414, Level 16, State 1, Line 2XML parsing: line 1, character 17, equal expectedThu, 03 Jun 2010 08:59:54 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxjoe...sorry for the delay - been working on a large project deployment. anyway, i updated the command text report to display multiple queries (should the report have more than one dataset). usually sqlServerCentral posts updates quickly - but just in case, below is the new query.thanks again for your positive feedback. hope all is well and let me know if you have any questions...set transaction isolation level read uncommittedselect itemID, name, path, replace(replace(CAST(CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS Varchar(max)),'>','>'),'<','<') as reportTextinto #catalog from catalog where CAST(CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS Varchar(max)) like '%<commandText>%' ;with myCTE as (select itemID, name, path, start, finish, len(reportText) as total, substring(reportText, start, (finish-start)) as commandText, right(reportText,len(reportText)-finish) as reportTextfrom ( select c1.itemID, c1.name, c1.path, charindex('<commandText>',c1.reportText) as start, charindex('</commandText>',c1.reportText) as finish, c1.reportText from #catalog c1 ) aunion allselect itemID, name, path, charindex('<commandText>',reportText) as start, charindex('</commandText>',reportText) as finish, total, substring(reportText, (charindex('<commandText>',reportText)), ((charindex('</commandText>',reportText))-(charindex('<commandText>',reportText)))), right(reportText,len(reportText)-(charindex('</commandText>',reportText)))from ( select itemID, name, path, start, finish, total, reportText from myCTE ) xwhere charindex('<commandText>',reportText) > 0 )--Final Selectselect itemID, name, path, replace(commandText,'<commandText>','') as commandTextfrom myCTEorder by itemID--Cleanupdrop table #catalogWed, 02 Jun 2010 11:39:42 GMTmichael_davis2http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxTry this.SELECT Catalog.Name, Catalog.Path, Schedule.NextRunTime, Schedule.LastRunTime, Schedule.StartDate, Schedule.RecurrenceType, Schedule.MinutesInterval, Schedule.DaysInterval, Schedule.WeeksInterval, Schedule.DaysOfWeek, Schedule.DaysOfMonth, Schedule.Month, Schedule.MonthlyWeekFROM Subscriptions INNER JOIN Catalog ON Subscriptions.Report_OID = Catalog.ItemID INNER JOIN ReportSchedule ON Subscriptions.SubscriptionID = ReportSchedule.SubscriptionID AND Catalog.ItemID = ReportSchedule.ReportID INNER JOIN Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleIDWed, 26 May 2010 09:55:10 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxHi Davis,This is an excellent article. It helped me a lot.But I just wanted to know one more thing.I am creating a subscriptions report that lists all the subscriptions.and I also wanted to know their frequency i.e whether they are weekly subscriptions or fortnightly subscriptions or monthly subscriptions or qtrly etc.........So is there any field in the Report Server Database tables which displays the above information.Thanks,grkanth81Tue, 25 May 2010 22:03:07 GMTgrkanth81http://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThanks. It does seem to not bring back all the query text.Fri, 21 May 2010 19:45:33 GMTMaricopaJoehttp://www.sqlservercentral.com/Forums/Topic859325-2580-1.aspxThanks -- the query you mention does have a limitation as it looks for the first instance of "<CommandText>" and "</CommandText>" to use as start and end locations -- but nothing else beyond it. I'll take a look at this to see if I can help you out, though.Fri, 21 May 2010 18:41:12 GMTmichael_davis2