Posts Tagged ‘Reporting Services’

h1

SQL SERVER How to Get Stored Procedure Names used by Reports, SQL Reporting Services

02/21/2009

We are using SQL Reporting Services for Reports.

Today we need to identify Stored Procedures used by Reports, so we want to transfer those SPs to Replication server.
I found very good query on Jacob Sebastian Blog.

;WITH xmlnamespaces (
    default
    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/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)
Advertisements
h1

Configure SQL Reporting Service, to Access Reports using External IP With IIS 7

01/06/2009

Hi All, Recently I come to situation where I need to Configure Reporting services on server having configuration: Window server 2008 and IIS 7.

I need to configure reports in manner, so User can access this reports thru External IP Address.

I found one interestign thing with IIS 7.0, so user can access reports externally.

To able to access the reports externally, do the steps as mentioned below:

1. Create Virtual Directory in IIS 7.0 :

Configure Reporting Services with IIS 7

2. Go to Handler Mappings :

Configure Reporting Services with IIS 7.0

3. Double Click on “Handler Mapping”. You will get screen like the given below:

Configure Reporting Service with IIS 7.0

4. Now see Top Right Corner: Click On “Add Managed Handler”. Here you need to fill up the info like:

Configure Reporting Services with IIS 7.0

Fill this info as:

Request path:      Reserved.ReportViewerWebControl.axd
Type:                    Microsoft.Reporting.WebForms.HttpHandler
Name:                  Reserved-ReportViewerWebControl-axd

Thats it. Now try with External IP.

You can access the reports with  External IP too.