Identifying stored procedures used by reporting services

Question

Identifying stored procedures used by reporting services

Fishbarnriots

SSCrazy

Points: 2568
More actions
June 23, 2008 at 9:31 am

#381296

Hi,
A developer has asked me if there is anyway to identify where a reporting within reporting services is getting its data from (The sp name or the underlying query). Rather than opening each report in Visual studio and taking a look, is there a bit of wizardry I can use to obtain the information?
This is on a SQL 2000 server.
Any pointers gratefully received.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Perhaps you could run profiler for a while and filter on the application name. I'm not sure 100% what appname reporting services uses, but it should be fairly easy to ID.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Fishbarnriots SSCrazy Points: 2568 More actions · Answer 2

I was thinking of using profiler, this problem is that some of the reports access databases on separate servers and there are a number of reports that are run very infrequently. I was sort of hoping (probably in vain) that there might be buried within reporting services a file or table containing this information.

checkai Hall of Fame Points: 3041 More actions · Answer 3

I use the following to attempt to grab my main data stored procs. However, more are used for parameters but I have a naming convention for both.

select c.ItemID,

c.Name as ReportName,

substring(CONVERT(varchar(max),CONVERT(varbinary(max),c.Content)), PATINDEX('% ssrs_%',CONVERT(varchar(max),CONVERT(varbinary(max),c.Content)))+13,

PATINDEX('% ssrs_%',CONVERT(varchar(max),CONVERT(varbinary(max),c.Content)))) as DataSource

from ReportServer.dbo.catalog c

where c.type = 2

Fishbarnriots SSCrazy Points: 2568 More actions · Answer 4

I have managed to get part way to a solution using the following query, The only issues are, if more than one Stored Proc/Query is used by the report only the first one is reported. Also whilst the name of the data source is returned the actual content of the data source (Server, database) eludes me.

-- LIST REPORTING PROCEDURES OR SQL CODE

SET NOCOUNT ON

GO

USE ReportServer

GO

SELECT DATALENGTH(Content) AS ContentLength,

ItemId,

Name,

Path,

CASE

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

Content))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

Content)), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

Content))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 7000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 7000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 7000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 14000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 14000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 14000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 21000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 21000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 21000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 28000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 28000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 28000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 35000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 35000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 35000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 42000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 42000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 42000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 49000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 49000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 49000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 56000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 56000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 56000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 63000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 63000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 63000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 70000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 70000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 70000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 77000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 77000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 77000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 84000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 84000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 84000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 91000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 91000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 91000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 98000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 98000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 98000, 8000)))) + 16, 100)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 105000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 105000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 105000, 8000)))) + 16, 100)

ELSE ''

END AS DataSource,

CASE

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

Content))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

Content)), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

Content))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 7000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 7000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 7000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 14000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 14000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 14000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 21000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 21000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 21000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 28000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 28000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 28000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 35000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 35000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 35000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 42000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 42000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 42000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 49000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 49000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 49000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 56000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 56000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 56000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 63000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 63000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 63000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 70000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 70000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 70000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 77000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 77000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 77000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 84000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 84000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 84000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 91000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 91000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 91000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 98000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 98000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 98000, 8000)))) + 13, 1000)

WHEN CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 105000, 8000)))) > 0 THEN SUBSTRING(CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 105000, 8000))), CHARINDEX(' ', CONVERT(CHAR(8000), CONVERT(VARBINARY(8000),

SUBSTRING(Content, 105000, 8000)))) + 13, 1000)

ELSE ''

END AS SQLCode

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), Content )) AS SQLCode01,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 8001, 8000))) AS SQLCode02,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 16001, 8000))) AS SQLCode03,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 24001, 8000))) AS SQLCode04,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 32001, 8000))) AS SQLCode05,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 40001, 8000))) AS SQLCode06--,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 48001, 8000))) AS SQLCode07,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 56001, 8000))) AS SQLCode08,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 64001, 8000))) AS SQLCode09,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 72001, 8000))) AS SQLCode10,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 80001, 8000))) AS SQLCode11,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 88001, 8000))) AS SQLCode12,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 96001, 8000))) AS SQLCode13,

-- CONVERT(CHAR(8000), CONVERT(VARBINARY(8000), SUBSTRING(Content, 104001, 8000))) AS SQLCode14

INTO #tmp1

FROM Catalog

WHERE Type = 2

UPDATE #tmp1

SET DataSource = SUBSTRING(DataSource, 1, CHARINDEX('</', DataSource) - 1)

WHERE CHARINDEX(' 0

UPDATE #tmp1

SET SQLCode = SUBSTRING(SQLCode, 1, CHARINDEX('</', SQLCode) - 1)

WHERE CHARINDEX(' 0

WHILE EXISTS (

SELECT *

FROM #tmp1

WHERE CHARINDEX(CHAR(13) + CHAR(10), SQLCode) > 0)

BEGIN

UPDATE #tmp1

SET SQLCode = SUBSTRING(SQLCode, 1, CHARINDEX(CHAR(13) + CHAR(10), SQLCode) - 1) + ' ' + SUBSTRING(SQLCode, CHARINDEX(CHAR(13) + CHAR(10), SQLCode) + 2, 1000)

WHERE CHARINDEX(CHAR(13) + CHAR(10), SQLCode) > 0

END

WHILE EXISTS (

SELECT *

FROM #tmp1

WHERE CHARINDEX(' ', SQLCode) > 0)

BEGIN

UPDATE #tmp1

SET SQLCode = SUBSTRING(SQLCode, 1, CHARINDEX(' ', SQLCode)) + SUBSTRING(SQLCode, CHARINDEX(' ', SQLCode) + 2, 1000)

WHERE CHARINDEX(' ', SQLCode) > 0

END

SELECT ContentLength,

ItemId,

CONVERT(VARCHAR(48), LTRIM(RTRIM(Name))) AS Name,

CONVERT(VARCHAR(88), LTRIM(RTRIM(Path))) AS Path,

CONVERT(VARCHAR(27), LTRIM(RTRIM(DataSource))) AS DataSource,

CONVERT(VARCHAR(763), LTRIM(RTRIM(SQLCode))) AS SQLCode

INTO #tmp2

FROM #tmp1

SELECT ContentLength,

ItemId,

Name,

Path,

DataSource,

SQLCode

FROM #tmp2

ORDER BY Name

DROP TABLE #tmp1

DROP TABLE #tmp2