Archive for April, 2008

Which version, level, edition of Sql Server am I using?

SELECT
	SERVERPROPERTY('productversion'),
	SERVERPROPERTY ('productlevel'),
	SERVERPROPERTY ('edition')

Tags: , ,

Friday, April 25th, 2008 SQL Server No Comments

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

Tags: , ,

Saturday, April 12th, 2008 SSRS No Comments