SQL SERVER GYAN

Open a browser window with Run as administrator permissions. From the Start menu, click All Programs, right-click Internet Explorer, and select Run as administrator.

Click Allow to continue.
Open Report Manager link
Click Tools.
Click Internet Options.
Click Security.
Click Trusted Sites.
Click Sites.
Add http://.
Clear the check box Require server certification (https:) for all sites in this zone if you are not using HTTPS for the default site.
Click Add.
Click OK.
In Report Manager, on the Home page, click Folder Settings.
In the Folder Settings page, click Security.
Click New Role Assignment.
Type your Windows user account in this format: \.
Select Content Manager.
Click OK.
Click Site Settings in the upper corner of the Home page.
Click security.
Click New Role Assignment.
Type your Windows user account in this format: \.
Select System Administrator.
Click OK.
Close Report Manager.
Re-open Report Manager in Internet Explorer, without using Run as administrator

Click to get more information:

http://msdn.microsoft.com/en-us/library/bb630430.aspx

Sunday, November 4, 2012

Type column description of ReportServer Database

Here is the Type column description of ReportServer.Dbo.Catalog Table :

1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)

Thanks,
Randhir

Usefull queries of ReportServer Database

http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c

Thanks,
Randhir

Date Source's Dependent Report

Use ReportServer

GO

SELECT
    C2.Name AS DataSourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.DataSource AS DS
        INNER JOIN
    ReportServer.dbo.Catalog AS C
        ON
            DS.ItemID = C.ItemID
                AND
            DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog
                        WHERE Type = 5) -- to  identifies data sources
        FULL OUTER JOIN
    ReportServer.dbo.Catalog C2
        ON
            DS.Link = C2.ItemID
WHERE
    C2.Type = 5
ORDER BY
    C2.Name ASC,
    C.Name ASC;

Thakns,

Randhir

Linked Report's Data Set ReportServer Reporting Service

Use ReportServer
GO

;WITH XMLNAMESPACES (

DEFAULT

'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rs

)

SELECT

LinkedReport,

x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,

x.value('CommandText[1]','VARCHAR(50)') AS CommandText

FROM (

SELECT

c1.Name AS LinkedReport, CAST(CAST(C2.content AS VARBINARY(MAX)) AS XML) AS reportXML

FROM dbo.Catalog c1

INNER JOIN dbo.Catalog AS c2 ON c1.LinkSourceID=c2.ItemID

WHERE

c1.Type = 4

) a

CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)

WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'

Thanks,
Randhir

Get only Linked Report from ReportServer database Reporting Services

Using Type 4 we can get all linked report from catalog table.

SELECT ItemID, Path, Name, ParentID, LinkSourceID

FROM         Catalog

WHERE Type = 4

Thanks,
Randhir