Archive for April, 2008
Which version, level, edition of Sql Server am I using?
SELECT
SERVERPROPERTY('productversion'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
Programmatically Listing Reports/Items Dependent on a given dataSource – SSRS
How do I see a list of reports pointing to “ODS” database. This is fine if there was only one datasource for all the reports: you can simply browse to ReportServer website, goto the data source in question and select dependent items link. The following code loops through items on the ReportServer, looks for DataSources, extracts Datasource definition, retrieves ConnectString from the definition, looks for “ODS” in the Connection string and then calls the ListDependentItems function of ReportServer webservice to get a list if reports/models(It writes the list to your visual studio output window)
Note: you need to add a web reference to your ReportServer webservice
(eg: http://myreportserver/reportserver/reportservice2005.asmx?wsdl )
‘add the following imports
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Xml
Imports RSUtilities.SSRSWS ‘ (This is your ProjectName.WebServiceName)
Imports System.IO
Imports System.Net
Imports System.Web.Services.Protocols ‘add a button to your form and copy the following code
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim reportName As String
Dim fromServer As String = “myserver”
Dim fromFolder As String = “/UAT/Reports” ‘”/” for root folder
Dim items As CatalogItem() = Nothing
Dim item As CatalogItem = Nothing
Dim DependentItems As CatalogItem() = Nothing
Dim DependentItem As CatalogItem = Nothing
Dim definition As New DataSourceDefinition
Dim strConn As String
Dim fromRS As New SSRSWS.ReportingService2005
fromRS.Url = “http://” + fromServer + “/reportserver/reportservice2005.asmx?wsdl”
fromRS.Credentials = System.Net.CredentialCache.DefaultCredentials
items = fromRS.ListChildren(fromFolder, True)
For Each item In items
If item.Type = ItemTypeEnum.DataSource Then
definition = fromRS.GetDataSourceContents(item.Path)
strConn = definition.ConnectString.ToLower
If strConn.Contains(“ods”) Then
DependentItems = fromRS.ListDependentItems(item.Path.ToString)
If DependentItems.Length > 0 Then
Console.WriteLine(item.Path.ToString)
End If
For Each DependentItem In DependentItems
reportName = DependentItem.Path.ToString
Console.WriteLine(“REPORT — ” + reportName)
Next
End If
End If
Next
MsgBox(“Done”)
fromRS = Nothing
End Sub