SSRS Subscriptions and looking up their SQL Server Agent job names and next scheduled run times

USE ReportServer
GO
WITH S AS
(
    SELECT
        SubscriptionID,
        ExtensionSettings = CAST(ExtensionSettings as xml)
    FROM ReportServer..Subscriptions
),
s2 AS (
SELECT
	S.SubscriptionID,
	TransmissionMethod = CASE ExtensionSettings.value('(/ParameterValues/ParameterValue/Name)[1]', 'varchar(50)')
		WHEN 'TO' THEN 'Email'
		WHEN 'PATH' THEN 'File Share'
		ELSE 'Unknown' END,
	EmailTo = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "TO"
		return data($value)') as nvarchar(4000)),
	FileSharePath = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "PATH"
		return data($value)') as nvarchar(4000)),
	[FileName] = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "FILENAME"
		return data($value)') as nvarchar(4000)),
    RenderFormat = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "RENDER_FORMAT" or $name = "RenderFormat"
		return data($value)') as nvarchar(4000)),
	EmailSubject = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "Subject"
		return data($value)') as nvarchar(4000))
	,ExtensionSettings
FROM S
)
SELECT *
INTO #es
FROM s2


SELECT
    Schedule.ScheduleID AS JobName,
    cat.[Name] AS ReportName,
    sub.[Description] AS SubDesc,
    --sub.ExtensionSettings,
    es.RenderFormat,
    sub.SubscriptionID,
    cat.[Path] AS ReportPath,
    StartDate,
    Schedule.LastRunTime,
    ja.next_scheduled_run_date
FROM Schedule
JOIN ReportSchedule ON Schedule.ScheduleID = ReportSchedule.ScheduleID
JOIN Subscriptions sub ON ReportSchedule.SubscriptionID = sub.SubscriptionID
JOIN [Catalog] cat ON ReportSchedule.ReportID = cat.ItemID
                  AND sub.Report_OID = cat.ItemID
LEFT JOIN #es es ON sub.SubscriptionId = es.SubscriptionId
LEFT JOIN msdb.dbo.sysjobs job ON CAST(Schedule.ScheduleID as sysname) = job.[name]
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = job.job_id
                                    AND ja.run_requested_date IS NOT NULL
                                    AND ja.start_execution_date IS NOT NULL
--WHERE RenderFormat LIKE 'Excel%'  -- Only subscriptions that generate Excel files
--WHERE Schedule.ScheduleID = '239DF50E-66F4-4F38-8378-426573E808B0'  -- SQL Agent Job Name

A Little on Trust

As I've gone through life, trust is one of those things that I know is important, but I had not thought about how important it was. Trust is the foundation for our personal relationships, business relationships and for almost every life decision. If you do not feel you can trust a person, a situation, a product or company, you will either not engage, or not allow yourself to be with that person, in that situation or use that product. Knowing that trust is important, it leads to the fact that you need others to trust you in order to attract and keep things that you desire in your life. I have always wanted to be someone who is trusted, but I never studied how to gain trust. One of the most important things to do to gain trust is to listen to others. When I really listen to others, they are likely to feel heard and understood. Because trust is an important part of the human experience, people know when they are being listened to. If I am obviously in a hurry, looking at my watch or phone, or cutting someone off, they know I am not really listening, and to a degree, not to be trusted. When someone feels that I am really listening, that helps that person to feel heard. To feel heard is one of the most important things in life. Feeling heard helps people to feel understood. It also defuses tension. MIT did a study between Palestinians and Israelis. They found that when the Palestinians' stories were shared with Israelis, it boosted the Palestinians' attitudes towards the Israelis. Being heard is powerful, and not feeling heard takes an emotional toll. Imagine a week when everyone you talk to doesn't listen and doesn't understand. It's miserable. And being or working with someone who repeatedly doesn't listen and doesn't understand wears away the foundation of the relationship. Trust. Another way to gain trust is to make your actions match what you say you will do. There is nothing that will break trust faster than someone who says one thing, but behaves in a different way. The words cannot be trusted to be turned into actions. To be able to be the person who does what he or she says, I need to know myself. What I mean is that in a situation, I should really think about what I'm saying I'm going to do, because I'm making a promise to that person. If I promise to finish a report or take out the trash before I go to bed, and I do not, that will erode trust. If I tell someone that my ego or feelings will not be hurt if they do something, and then later, it turns out that I was hurt, it actually erodes trust. If I take the time to evaluate what I will likely do, I'm better prepared to promise what I will actually deliver. I should also talk about how to show others that you trust them. The most important way to do this it to respect them. If you feel you do not respect someone, how could you possibly trust them? You may be thinking about someone who you trust but you do not think that you respect them. What is probably happening is that you respect them in some area, but not in another. Maybe you trust your mechanic to fix your car for a fair price, but you do not respect the way she treats her family, so you are not interested in spending more time with her. You can respect the mechanic for all the times she has fixed your car, and for that time she didn't even charge you for something that you knew another mechanic would overcharge you. I hope that you can see that trust is a critical foundation for the relationships all around you. Gaining and showing trust to others is an important part of life and necessary for those who want to have a successful personal and work life. Sources and other reading: https://lead2goals.com/developing-the-circle-of-trust/ https://www.vitalsmarts.com/resource/crucial-conversations-book/ https://www.psychologytoday.com/us/blog/compassion-matters/201506/5-ways-build-trust-and-honesty-in-your-relationship http://news.mit.edu/2012/conflict-resolution-0315

Create New Event Log Source

Create a new console app and then paste this code. The resulting exe must be run as administrator, and you need to pass in the name of the source. You probably will want to create a cmd or bat file to run the exe.

using System;
using System.Diagnostics;

namespace CreateEventLogSource
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length == 1 && !String.IsNullOrWhiteSpace(args[0]))
            {
                string eventSource = args[0];
                if (EventLog.SourceExists(eventSource))
                {
                    Console.WriteLine("Event Log Source already exists");
                }
                else
                {
                    // No exception yet means the user has admin privileges
                    try
                    {
                        EventLog.CreateEventSource(eventSource, "Application");
                        Console.WriteLine("Event log source created.");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("An exception occurred during CreateEventSource. Details follow.");
                        Console.WriteLine(ex);
                        return;
                    }

                    try
                    {
                        EventLog.WriteEntry(eventSource, "Test", EventLogEntryType.Information);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("An exception occurred during WriteEntry. Details follow.");
                        Console.WriteLine(ex);
                        return;
                    }
                }
            }
            else
            {
                Console.WriteLine("You must pass in the name of the Event Log Source");
            }
        }
    }
}

Outlook VBA to run code when a task reminder occurs and help with cleanup timeout emails

The first 4 items below help cleanup emails that have been categorized as "timeout" emails sent from our web server.

Below, the Application_Reminder event handler will fire whenever a reminder occur.



Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
  Dim olApp As Outlook.Application
  Dim olNs As Outlook.NameSpace
  Set olApp = Outlook.Application
  Set olNs = olApp.GetNamespace("MAPI")
  Set Items = olNs.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub Items_ItemAdd(ByVal Item As Object)
    'Debug.Print CStr(Now) & ": START: Items_ItemAdd"
    On Error GoTo ErrorHandler
    If TypeName(Item) = "MailItem" Then
        MoveTimeoutsFromInbox
    End If

ProgramExit:
  Exit Sub

ErrorHandler:
  MsgBox Err.Number & ": " & Err.Description
  Resume ProgramExit
End Sub

Public Sub MoveTimeoutsFromInbox()
    'Dim DeletedItemsFolder As Outlook.MAPIFolder
    Dim olNs As Outlook.NameSpace
    Dim Item As Object
    Dim InboxItems As Outlook.Items
    Dim MyMailItem As Outlook.MailItem
    Dim counter As Long
    Dim position As Integer
    Dim DateAWeekAgo As Date

    On Error GoTo ErrorInfo

    Set olNs = Application.GetNamespace("MAPI")
    'Set DeletedItemsFolder = olNs.GetDefaultFolder(olFolderDeletedItems)
    Set InboxItems = olNs.GetDefaultFolder(olFolderInbox).Items
    DateAWeekAgo = DateAdd("d", -7, Now())

    ' Loop through the Items in the folder backwards (most recent first)
    For counter = InboxItems.Count To 1 Step -1
        Set Item = InboxItems(counter)

        If Item.Class = olMail Then
            Set MyMailItem = Item

            If MyMailItem.SentOn < DateAWeekAgo Then
                Exit Sub ' If the email was sent over a week ago, stop processing because we've reached a week of emails
            End If

            ' If there is a category and the email was sent more than 75 minutes ago, move it to NoReply if it has Timeout category
            If MyMailItem.Categories <> "" Then
                Debug.Print CStr(Now) & ": MoveTimeoutsFromInbox: Mail has categories"
                position = InStr(MyMailItem.Categories, "Timeout")
                If position > 0 Then
                    Debug.Print "Timeout found. Minutes past since sent: " & CStr(DateDiff("n", MyMailItem.SentOn, Now()))
                    If MyMailItem.SentOn < DateAdd("n", -75, Now()) Then
                        Item.Move olNs.GetDefaultFolder(olFolderDeletedItems).Folders("NoReply")
                        Debug.Print "Timeout moved."
                    End If
                End If
            End If
        End If
    Next counter

ProgramExit:
    Set olNs = Nothing
    Set Item = Nothing
    Set InboxItems = Nothing

    Exit Sub

ErrorInfo:
    MsgBox "An unexpected Error has occurred." _
         & vbCrLf & "Error Number: " & Err.Number _
         & vbCrLf & "Error Description: " & Err.Description _
         , vbCritical, "Error!"
    Resume ProgramExit
End Sub

Public Sub Application_Reminder(ByVal Item As Object)
    If TypeOf Item Is Outlook.TaskItem Then
        Dim ReminderSubject As String
        Dim olTask As Outlook.TaskItem
        Set olTask = Item
        ReminderSubject = "Run Rule: noreply to Deleted Items - NoReply"

        'Run Rule: noreply to Deleted Items - NoReply
        Dim objRules As Outlook.Rules
        Set objRules = Outlook.Application.Session.DefaultStore.GetRules
   
        ' Refer to the subject of the specific task item
        If olTask.Subject = ReminderSubject Then
             ' The corresponding specific rule
             Dim objRule As Outlook.Rule
             Set objRule = objRules.Item("noreply to Deleted Items - NoReply")
             objRule.Execute ShowProgress:=True, Folder:=Session.GetDefaultFolder(olFolderDeletedItems), IncludeSubfolders:=False
             Set objRule = Nothing
             'MsgBox ("Deleted NoReply emails moved successfully")
        End If

        Set objRules = Nothing

        If LCase$(olTask.Subject) = LCase$(ReminderSubject) Then
            ' Set next reminder - "Snooze" for 5 hours
            olTask.ReminderTime = DateAdd("h", 5, olTask.ReminderTime)
            olTask.Save
        End If

        Set olTask = Nothing
    End If
End Sub

Avoid divide by zero errors in SQL Server Reporting Services (SSRS)

To do this, you just add a function to the report code (in the language VB.NET).

Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal
        If denominator = 0 Then
            Return 0
        Else
            Return numerator / denominator
        End If
End Function

Thanks to: http://williameduardo.com/development/ssrs/ssrs-divide-by-zero-error/

The following is the JavaScript code to display the ASCII character codes for each character of a given string.

function ascii(str) {
    return str
          .split('')
          .map(function (char) {
              return char + ": " + String(char.charCodeAt(0)) + "\n";
          })
          .reduce(function (current, previous) {
              return current + previous;
          });
}
 
alert(ascii("Hello World!")); 

Scripting the display of dependencies for an object for SQL Server 2008 R2

Scripting the display of dependencies for an object for SQL Server 2008 R2 is hard to know how to do. I've done a little research, and found that there is no one way to do it, so the best way to do it is to load the different ways into a temp table and display them that way.

If you want to see how Microsoft does it in SQL Server Management Studio (SSMS):
mssqltips.com/tipimages/1294_sqlcode.txt

DECLARE @TableName nvarchar(50)

DECLARE @TableNameSchema nvarchar(50)

DECLARE @TableNameDb nvarchar(50)

SET @TableName = N'Product'

SET @TableNameSchema = N'dbo.' + @TableName

SET @TableNameDb = N'DbNameHere.dbo.' + @TableName

CREATE TABLE #dependents (obj_name nvarchar(255), obj_type nvarchar(255), obj_source nvarchar(30))

CREATE TABLE #msdependencies (oType nvarchar(255), oObjName nvarchar(255), oOwner nvarchar(255), oSequence nvarchar(255))

INSERT INTO #msdependencies (oType, oObjName, oOwner, oSequence)

    EXEC sp_MSdependencies @TableNameSchema, null, 1315327

INSERT INTO #dependents (obj_name, obj_type, obj_source)

    SELECT

        oOwner + '.' + oObjName AS obj_name,

        CASE oType

            WHEN 1 THEN 'function'

            WHEN 4 THEN 'view'

            WHEN 8 THEN 'table'

            WHEN 16 THEN 'stored procedure'

            ELSE '(other - ' + CAST(oType as varchar) + ')'

            END AS obj_type,

        'sp_MSDependencies' AS obj_source

    FROM #msdependencies

DROP TABLE #msdependencies

INSERT INTO #dependents (obj_name, obj_type, obj_source)

    SELECT DISTINCT

        OBJECT_SCHEMA_NAME(so.id) + '.' + so.name,

        CASE so.[type]

            WHEN 'TF' THEN 'table function'

            WHEN 'IF' THEN 'inline function'

            WHEN 'FN' THEN 'scalar function'

            WHEN 'V' THEN 'view'

            WHEN 'P' THEN 'stored procedure'

            ELSE '(other - ' + so.[type] + ')'

            END AS obj_type,

        'sysComments'

    FROM syscomments sc

    INNER JOIN sysobjects so ON sc.id = so.id

    WHERE charindex(@TableName, text) > 0

INSERT INTO #dependents (obj_name, obj_type, obj_source)

    SELECT DISTINCT

        SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME,

        CASE ROUTINE_TYPE

            WHEN 'FUNCTION' THEN 'function'

            WHEN 'PROCEDURE' THEN 'stored procedure'

            ELSE '(other - ' + ROUTINE_TYPE + ')'

            END AS obj_type,

        'information_schema.routines'

    FROM information_schema.routines ISR

    WHERE CHARINDEX(@TableNameSchema, ISR.ROUTINE_DEFINITION) > 0

INSERT INTO #dependents (obj_name, obj_type)

    EXEC sp_depends @objname = @TableNameDb

UPDATE #dependents SET obj_source = 'sp_depends' WHERE obj_source IS NULL

-- Also, less useful, but could be added:

-- SELECT * FROM sys.dm_sql_referencing_entities (N'dbo.Product', N'OBJECT')

-- SELECT * FROM sys.dm_sql_referenced_entities (N'dbo.Product', N'OBJECT')

--SELECT DISTINCT obj_name, obj_type, obj_source FROM #dependents ORDER BY obj_name, obj_type, obj_source

SELECT

    obj_name,

    CASE WHEN MIN(obj_type) != 'function' THEN MIN(obj_type)

         ELSE MAX(obj_type) END AS obj_type,

    CASE obj_type

        WHEN 'inline function' THEN 'function'

        WHEN 'scalar function' THEN 'function'

        WHEN 'table function' THEN 'function'

        ELSE obj_type

        END AS obj_category

FROM #dependents

GROUP BY

    obj_name,

    CASE obj_type

        WHEN 'inline function' THEN 'function'

        WHEN 'scalar function' THEN 'function'

        WHEN 'table function' THEN 'function'

        ELSE obj_type END

ORDER BY obj_category, obj_name

DROP TABLE #dependents

Thanks to Greg Robidoux:
mssqltips.com/sqlservertip/1294/listing-sql-server-object-dependencies/

Thanks to Pinal Dave:
blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/