List objects in a SQL database

This base script for querying SQL Servers system tables is the sharpest tool in the shed.
I mainly use it as the best way to search for occurance of fields in tables but can be modified in a multitude of ways. The sysobjects.xtype field is the key and I have listed its values below
pt.sql

-- C : CHECK constraint
-- D : Default or DEFAULT constraint
-- F : FOREIGN KEY constraint
-- L : Log
-- P : Stored procedure
-- PK : PRIMARY KEY constraint (type is K)
-- RF : Replication filter stored procedure
-- S : System tables
-- TR : Triggers
-- U : User table
-- UQ : UNIQUE constraint (type is K)
-- V : Views
-- X : Extended stored procedure
-- TF : Functions
USE master -- only an example - run local to the database you are searching
SELECT
 o.name AS TableName,
 o.type AS ObjectType,
 c.name AS FieldName,
 s.name AS FieldType,
 c.prec,
 c.scale,
 c.isnullable
FROM
 sysobjects AS o
   INNER JOIN syscolumns AS c
    ON c.id = o.id
       INNER JOIN systypes AS s
       ON c.xtype = s.xtype
WHERE
--select all user tables
o.type = 'U'
--other example select all user tables containing the field SalesPersonId
--o.type = 'U' and c.name = 'SalesPersonId'

Reporting Services - Exporting reports

I keep getting asked how to get a report off a report server. It is not very intuitive but here is a comppilition various email I have had to write.

Exporting the report
This describes how to export report server reports from one server to another and/or how to export the source code of existing reports
1. Go to the folder with the existing report you wish to export and click on the “Show Details” button.
2. Click on the Edit Properties for the report
3. Click on the Edit link under the Report Definition sub heading
4. Choose the Save option – this will export the report in rdl format

Import Directly of Extract and Build the code
At this point you can go to Reporting Services 2005 and import the rdl file directly or else you can rebuild the report in Visual Studio so that you will have access to the source for modifications.

Import Directly
1. Open SQL Server 2005 Management Studio
2. Choose Reporting Services as the server type and log onto the server you want to upload the report to
3. Move to the directory to import the file to
4. Right click -> Import file – choose the .rdl file exported previously
5. Set up appropriate data sources and permissions using the Report Manager Web interface
Extract and Build the Code
1. Open either:
a. SQL Server Business Intelligence Dev Studio (Programs->Microsoft SQL Server 2005 ->)
b. MS Visual Studio 2005 (Programs-> MS Visual Studio 2005 ->)
2. File->New->Project->Report Server Project
3. Name the project the same as the original

Tip Using SQL Profiler - run as log then load into a table

When running a trace on an Profiler it is more convenient to manipulate the data in a table but you incur lower overheads saving to a file.

The following tip is from the microsoft article http://msdn.microsoft.com/en-us/library/ms979207.aspx?ppud=4

Run the trace as a file then import into a table
SELECT * INTO trace1108 FROM ::fn_trace_gettable('C:\MyTrace.trc', default)

Setting filters in SQL Profiler is a pain in the bottom - expect to get it wrong and have to fiddle with your settings. Often it is better just to run what you can, load it into a table and run a few deletes to get rid of records that are known not to be needed

Script to Delete Reporting Services Subscriptions

I wrote this script to delete all subscriptions against a reporting services report.

SQL 2000 offers fairly poor management tools for Reporting Services and I had a situation where automatically generated subscriptions were piling up on a 2000 box to the point where they could not be deleted manually.

To run - create a console program (note - console program) in Visual Studio and put the following code in a module. You will probably need to set up a few dependencies in the references.

I don't know if anyone will have as specific requirements as this but it might be a help if you are looking at programming against the reporting services object model.

Imports System
Imports System.Web.Services.Protocols
Imports RSManagement_Unsubscribe.ReportingServices

'Gerard Conlon 18 Sept 2008
'Program to delete all report subscriptions against a given ReportingServices report
'Written for compilition as a console application in Visual Studio


Module modRsManagement

   Sub Main()
       Dim strReportOwner As String
       Dim strReportPath As String

       Dim strParams() As String
       Dim i As Integer

       'the parameters are passed in the form of RSManagement_Unsubscribe.exe "ReportPath|ReportOwner
       'they are wrapped in double quotes and seperated by the pipe |
       'ie called from the command line as
       ' RSManagement_Unsubscribe.exe "/MyReportsDir/ReportName|MyServer\ASPNET"
       'ReportPath - full report path from the top level directory and including the report name
       'ReportOwner - usually the name of the server hosting reporting services and running under the ASPNET user ie MyServer\ASPNET"

       'Get the passed parameters
       If Len(Command()) > 0 Then
           strParams = Split(Mid$(Command(), 2, Len(Command()) - 2), "|")
           For i = 0 To UBound(strParams)
               If i = 0 Then strReportPath = strParams(0)
               If i = 1 Then strReportOwner = strParams(1)
           Next i
       End If


       'check to see if parameters are passed and if not give descriptive error
       If Trim(strReportPath) = "" Or Trim(strReportOwner) = "" Then
           Console.WriteLine("")
           Console.WriteLine("The Report Path and The Report owner must be specifed as parameters")
           Console.WriteLine("")
           Console.WriteLine("Parameters are seperated by the pipe char (|) and need to be in double quotes")
           Console.WriteLine("")
           Console.WriteLine("Example: RSManagement_Unsubscribe.exe " + """" + "/PrePostTimes/CMS Pretimes|AUS-DB1\ASPNET" + """")
           Console.WriteLine("")
           Console.WriteLine("Press any key to continue.......")
           Console.ReadLine() 'hold the screen open so message can be read

       Else
           Console.WriteLine(fDeleteSubscriptions(strReportPath, strReportOwner))

       End If

   End Sub


   Function fDeleteSubscriptions(ByVal sReport As String, ByVal sOwner As String) As String
       Dim rs As New ReportingService
       Dim subscriptions As Subscription() = Nothing
       Dim schedules As Schedule() = Nothing
       Dim i As Integer
       Dim cnt As Integer

       Console.WriteLine(" Searching for Subscriptions ..........")
       rs.Credentials = System.Net.CredentialCache.DefaultCredentials
       Console.WriteLine(sReport)
       Console.WriteLine(sOwner)
       rs.Timeout = 600000
       Try
           fDeleteSubscriptions = "0"
           cnt = 0
           Console.WriteLine("Deleting Subscription ..........")
           subscriptions = rs.ListSubscriptions(sReport, sOwner)
           'loop through and delete the subscriptions
           If subscriptions.GetLength(0) > 0 Then
               For i = 0 To subscriptions.GetLength(0) - 1
                   'rs.BeginListSchedules()
                   Console.WriteLine("Deleting " + subscriptions(i).Description)
                   rs.DeleteSubscription(subscriptions(i).SubscriptionID)

                   cnt = cnt + 1

               Next
               fDeleteSubscriptions = "Deleted " + CStr(cnt) + " Subscriptions"
           Else
               fDeleteSubscriptions = "Nil (0) Subscriptions"
           End If

       Catch e As SoapException
           fDeleteSubscriptions = fDeleteSubscriptions + (e.Detail.OuterXml)
       End Try
   End Function
End Module