November 15, 2018 at 1:09 pm
i'm running the below in the hopes of getting all the proc or sql calls from all the ssrs reports that we are currently running from our list of 300 reports. We are making some schema changes and i need to identify which procs are getting called from our reports. I have a list of 255 report. I try and run the below on the 255 active reports and i get a "Msg 9420, Level 16, State 1, Line 1" error.
XML parsing: line 1337, character 10, illegal xml character. I notice some of the xml had "" in it so i removed that but still getting the error. Does anyone have any ideas here? I just need a way to get all the procs or sql being called in all of our ssrs reports w/o having to manually go to each one through the interface. We are currently using sql server 2008r2. I'm going to start down the path of creating a C# console app in the meantime because i'm at my wits end at this point w sql and how it manages ssrs. Don't even get me started on the fact that i can't get all the people that are getting emailed in a subscription that uses a query to get the email list. Not happy right now w/ the way ssrs stores it's data.
;WITH xmlnamespaces (
default
'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
NAME AS reportname,
q.value('@Name[1]', 'VARCHAR(50)') AS datasetname,
x.
value
('DataSourceName[1]', 'VARCHAR(50)') AS datasourcename,
x.
value
('CommandText[1]', 'VARCHAR(50)') AS spname
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') d(q)
CROSS apply q.nodes('Query') r(x)
November 15, 2018 at 2:43 pm
Snargables - Thursday, November 15, 2018 1:09 PMi'm running the below in the hopes of getting all the proc or sql calls from all the ssrs reports that we are currently running from our list of 300 reports. We are making some schema changes and i need to identify which procs are getting called from our reports. I have a list of 255 report. I try and run the below on the 255 active reports and i get a "Msg 9420, Level 16, State 1, Line 1" error.
XML parsing: line 1337, character 10, illegal xml character. I notice some of the xml had "" in it so i removed that but still getting the error. Does anyone have any ideas here? I just need a way to get all the procs or sql being called in all of our ssrs reports w/o having to manually go to each one through the interface. We are currently using sql server 2008r2. I'm going to start down the path of creating a C# console app in the meantime because i'm at my wits end at this point w sql and how it manages ssrs. Don't even get me started on the fact that i can't get all the people that are getting emailed in a subscription that uses a query to get the email list. Not happy right now w/ the way ssrs stores it's data.
;WITH xmlnamespaces (
default
'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
NAME AS reportname,
q.value('@Name[1]', 'VARCHAR(50)') AS datasetname,
x.
value
('DataSourceName[1]', 'VARCHAR(50)') AS datasourcename,
x.
value
('CommandText[1]', 'VARCHAR(50)') AS spname
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') d(q)
CROSS apply q.nodes('Query') r(x)
Actually, going down the road of a C# app isn't a bad idea. Some of the issue is that querying the tables in SSRS isn't supported and the tables aren't documented, the database isn't relational, in some cases you can't always get to the encrypted parts of Data Sources after a data source has changed and the current data source moves to the DataSource table and other odd things like that. The supported method to access the information is to go through the web service which uses SOAP API. That's how Report Manager does it as it's not using t-sql other than to call some of the stored procedures in the ReportServer database. This is the documentation on it if you wanted to try it:
Report Server Web Service Methods
There are some examples in a post up here as well:
decipher ReportServer.DataSource.ConnectionString
If you wanted to try shredding the xml and going that route with SQL, it's a bit more to get the information you want. Take a look at this post and the code to see if that works for you - I don't know if it works on 2008 and don't have that version available to test right now:
SSRS: Auditing Report Queries
Sue
November 15, 2018 at 2:48 pm
Going to have to knock the dust off my C# skills i suppose.
November 15, 2018 at 2:54 pm
I'm curious about that last script - Did you try the script in that last link to see if it works on a 2008 version?
Sue
November 16, 2018 at 2:58 pm
none of those scripts worked for me
November 16, 2018 at 3:00 pm
Consider the dust knocked off! Please excuse me if there are any experienced front end developers out there but this is the best i can do w/ the time i have and it works. Closing the loop. Thanks for nothing SSRS.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Linq;
namespace ConsoleApplication1
{
class ReportProcList
{
static void Main(string[] args)
{
string ConnectionString = "xxxxxxxx;DataBase=dbautility;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("testproc", conn);//this proc pulls all the report names, path, and the xml as a varchar(max)
//CREATE proc [dbo].[testproc]
//as
//SELECT name
// , path
// , replace(convert(varchar(max),CONVERT(VARBINARY(MAX),Content)),'','') xmldata
//FROM ReportServer.dbo.Catalog
//where path in (xxxxxxxxx)
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string name = reader[0].ToString();
string path = reader[1].ToString();
string xmldata = reader[2].ToString();
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(xmldata);
XmlNodeList DataSetName = xdoc.GetElementsByTagName("DataSourceName");
XmlNodeList DataSet = xdoc.GetElementsByTagName("DataSet");
XmlNodeList Query = xdoc.GetElementsByTagName("Query");
XmlNodeList CommandTypVal = xdoc.GetElementsByTagName("CommandType");
XmlNodeList CommandText = xdoc.GetElementsByTagName("CommandText");
int DataSetNamecnt = DataSetName.Count;
int DataSetcnt = DataSet.Count;
int Querycnt = Query.Count;
int CommandTypValcnt = CommandTypVal.Count;
int CommandTextcnt = CommandText.Count;
int cnter = 0;
for (int ctr = 0; ctr < DataSetNamecnt; ctr++)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand inserts = new SqlCommand("LoadSSRSReportListWProcs", con))//this loads a table that has the below cols
//CREATE TABLE [dbo].[SSRSReportListWProcs](
// [id] [int] IDENTITY(1,1) NOT NULL,
// [reportName] [varchar](4000) NULL,
// [ReportPath] [varchar](4000) NULL,
// [datasetname] [varchar](max) NULL,
// [commandtype] [varchar](max) NULL,
// [commandtext] [varchar](max) NULL,
// [query] [varchar](max) NULL,
// [XMLData] [varchar](max) NULL,
// [createddate] [datetime] NOT NULL
//) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
//GO
//ALTER TABLE [dbo].[SSRSReportListWProcs] ADD CONSTRAINT [DF_SSRSReportListWProcs_createddate] DEFAULT (getdate()) FOR [createddate]
//GO
//and heres the LoadSSRSReportListWProcs proc
//CREATE proc [dbo].[LoadSSRSReportListWProcs]
// @reportName varchar(4000),
// @ReportPath varchar(4000),
// @datasetname varchar(max) = null
// ,@commandtype varchar(max) = null
// ,@commandtext varchar(max) = null
// ,@query varchar(max) = null
// ,@XMLData varchar(max)
//as
//INSERT INTO [dbo].[SSRSReportListWProcs]
// ( reportName
// ,ReportPath
// ,[datasetname]
// ,[commandtype]
// ,[commandtext]
// ,[query]
// ,XMLData
// )
//VALUES ( @reportName,@ReportPath,@datasetname, @commandtype, @commandtext, @query,@XMLData )
{
inserts.CommandType = CommandType.StoredProcedure;
inserts.Parameters.Add("@reportName", SqlDbType.VarChar).Value = CommandText[cnter].InnerText;
inserts.Parameters.Add("@ReportPath", SqlDbType.VarChar).Value = CommandText[cnter].InnerText;
inserts.Parameters.Add("@XMLData", SqlDbType.VarChar).Value = CommandText[cnter].InnerText;
if (cnter < DataSetNamecnt)
{
Console.WriteLine("DataSourceName: " + DataSetName[cnter].InnerText);
inserts.Parameters.Add("@datasetname", SqlDbType.VarChar).Value = DataSetName[cnter].InnerText;
}
if (cnter < CommandTypValcnt)
{
Console.WriteLine("CommandType: " + CommandTypVal[cnter].InnerText);
inserts.Parameters.Add("@commandtype", SqlDbType.VarChar).Value = CommandTypVal[cnter].InnerText;
}
if (cnter < Querycnt )
{
Console.WriteLine("Query: " + Query[cnter].InnerText);
inserts.Parameters.Add("@query", SqlDbType.VarChar).Value = Query[cnter].InnerText;
}
if (cnter < CommandTextcnt)
{
Console.WriteLine("CommandText: " + CommandText[cnter].InnerText);
inserts.Parameters.Add("@commandtext", SqlDbType.VarChar).Value = CommandText[cnter].InnerText;
}
cnter = cnter++;
con.Open();
inserts.ExecuteNonQuery();
con.Close();
}
}
}
// Console.WriteLine("DataSourceName: " + DataSetName[0].InnerText);
// Console.WriteLine("DataSourceName: " + DataSetName[1].InnerText);
//// string query =
// Console.WriteLine(reader[0].ToString());
// Console.WriteLine(reader[1].ToString());
// Console.WriteLine(reader[2].ToString());
}
Console.Read();
Console.Clear();
//Close reader and connection
reader.Close();
conn.Close();
Environment.Exit(0);
}
}
}
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply