Getting Data source (stored Procedure) from Reports DB?

[checkai]

Is it possible to read the report definition of where it is grabbing data from, from the Reports database?

All of our reports are based on stored procedures and it would be nice to be able to query that database to see all stored procedures in use.

any ideas? Thanks.

 

[xlbo]

That info is held in the RDL file, not in the report server...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[checkai]

But isn't the rdl definition in one of the columns of the catalog table? If it isn't do you know where the .rdl's are held?

 

[xlbo]

Look in the "DataSource" table. Links to the "Catalog" table via ItemID

Take a copy of the database 1st though cos this is a live db

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[checkai]

DIdn't find anything there that looked like it would show the datasource. I was wondering if it was in the [Content] field within the Catalog table; but that is an Image data type, and I'm unsure how/if you can read that.

 

[xlbo]

There is a "connectionstring" field in the DataSource table. It is an OLE object however...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[checkai]

It looked like only the actual data sources had data in that field however. Not all reports.

 

[xlbo]

If you are looking for the query rather than the data source then that is definitely only held in the rdl. Don;t know where the actual rdls are stored though...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[checkai]

The .rdl information is in the CONTENT field of the CATALOG table; I found it by running this query:

select substring(CONVERT(varchar(max),CONVERT(varbinary(max),c.Content)), PATINDEX('%<CommandText>ssrs_%',CONVERT(varchar(max),CONVERT(varbinary(max),c.Content)))+13, 
		PATINDEX('%</CommandText>%',CONVERT(varchar(max),CONVERT(varbinary(max),c.Content)))-13 - PATINDEX('%<CommandText>ssrs_%',CONVERT(varchar(max),CONVERT(varbinary(max),c.Content)))) as DataSource	
from Catalog

Note: this will only return the first instance of your datasets...so if you have multiple per report you will only get one.

 

[checkai]

also, my Stored Procs all start with "ssrs_", that will need to be omitted for others.