SQL script to show you a list of your SSRS subscriptions

At work we use SQL Server Reporting Services (SSRS) for our reporting needs. I like to have a few SQL Scripts to help me keep track of our subscriptions, when they ran and check on any errors.

Here’s a couple of SQL Scripts that I use when needed and I don’t have the website where I found these but if you know where they we’re originally posted, let me know so I can give credit where credit is due.

LIST OF SSRS SUBSCRIPTIONS

SELECT SubscriptionID ,
d.LastStatus ,
e.Name ,
e.Path ,
d.DeliveryExtension ,
d.LastRunTime ,
d.ExtensionSettings
FROM Subscriptions d
JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID

LIST OF SSRS SUBSCRIPTIONS ORDER BY MOST RECENTLY RAN REPORT

SELECT SubscriptionID ,
d.LastStatus ,
e.Name ,
e.Path ,
d.DeliveryExtension ,
d.LastRunTime ,
d.ExtensionSettings
FROM Subscriptions d
JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID
ORDER BY d.LastRunTime DESC;

LIST OF SSRS SUBSCRIPTIONS SETUP FOR FILESHARE DELIVERY

SELECT  SubscriptionID ,
        d.LastStatus ,
        e.Name ,
        e.Path ,
        d.DeliveryExtension ,
        d.LastRunTime ,
        d.ExtensionSettings
FROM    Subscriptions d
        JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID
WHERE DeliveryExtension = 'Report Server FileShare'

LIST OF SSRS SUBSCRIPTIONS SETUP FOR EMAIL DELIVERY

SELECT  SubscriptionID ,
        d.LastStatus ,
        e.Name ,
        e.Path ,
        d.DeliveryExtension ,
        d.LastRunTime ,
        d.ExtensionSettings
FROM    Subscriptions d
        JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID
WHERE DeliveryExtension = 'Report Server Email'

c# poco generator from database table using sql script

This is a simple SQL  script that will generate a c# poco class that you can use as a model in your Visual Studio projects.

I like to generate my tables manually when creating a new project. Having the tables setup with foreign keys along with a database diagram in SQL Server gives me the visual that I need when determining my database storage options.

If you’re wondering if I’m using “Migrations”, the answer is NO. I  find that it can get in my way but it’s very cool. I  haven’t figured out a way to incorporate “Migrations”  into my process yet but then again, I have a process that works for me to be productive and that’s the key, to be productive.

The only editing you need to do to this sql script is to change the table name you want to use at the top.

I did find this sql script somewhere on the interwebs and if anyone knows where, please share it with me so I can give credit to the person who wrote this. I couldn’t find it myself so at least I know it’s here for safe keeping.

Copy the code below into a new query window in your Microsoft SQL  Server Management Studio.

DECLARE @TableName sysname = 'YOUR_DATABASE_TABLE_NAME_GOES_HERE';
DECLARE @Result VARCHAR(MAX) = 'public class ' + @TableName + '
{';

SELECT @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
FROM ( SELECT REPLACE(col.name, ' ', '_') ColumnName ,
column_id ColumnId ,
CASE typ.name
WHEN 'bigint' THEN 'long'
WHEN 'binary' THEN 'byte[]'
WHEN 'bit' THEN 'bool'
WHEN 'char' THEN 'string'
WHEN 'date' THEN 'DateTime'
WHEN 'datetime' THEN 'DateTime'
WHEN 'datetime2' THEN 'DateTime'
WHEN 'datetimeoffset' THEN 'DateTimeOffset'
WHEN 'decimal' THEN 'decimal'
WHEN 'float' THEN 'float'
WHEN 'image' THEN 'byte[]'
WHEN 'int' THEN 'int'
WHEN 'money' THEN 'decimal'
WHEN 'nchar' THEN 'string'
WHEN 'ntext' THEN 'string'
WHEN 'numeric' THEN 'decimal'
WHEN 'nvarchar' THEN 'string'
WHEN 'real' THEN 'double'
WHEN 'smalldatetime' THEN 'DateTime'
WHEN 'smallint' THEN 'short'
WHEN 'smallmoney' THEN 'decimal'
WHEN 'text' THEN 'string'
WHEN 'time' THEN 'TimeSpan'
WHEN 'timestamp' THEN 'DateTime'
WHEN 'tinyint' THEN 'byte'
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'varbinary' THEN 'byte[]'
WHEN 'varchar' THEN 'string'
ELSE 'UNKNOWN_' + typ.name
END ColumnType ,
CASE WHEN col.is_nullable = 1
AND typ.name IN ( 'bigint', 'bit', 'date',
'datetime', 'datetime2',
'datetimeoffset', 'decimal',
'float', 'int', 'money',
'numeric', 'real',
'smalldatetime', 'smallint',
'smallmoney', 'time',
'tinyint', 'uniqueidentifier' )
THEN '?'
ELSE ''
END NullableSign
FROM sys.columns col
JOIN sys.types typ ON col.system_type_id = typ.system_type_id
AND col.user_type_id = typ.user_type_id
WHERE object_id = OBJECT_ID(@TableName)
) t
ORDER BY ColumnId;

SET @Result = @Result + '
}';

PRINT @Result;

What software do I have loaded on my Mac?

I like to have a list of what I load on my all my new macs. I don’t get new macs very often but I do have several of them and it’s nice to know/remember what I load on them. This list is living and will be pruned accordingly.

  • BROWSERS
    • Chrome, Firefox, Opera, Brave
  • PRODUCTIVITY
    • Evernote
  • GIT CLIENTS
    • GitKraken, Github Desktop, SourceTree
  • IDE CLIENTS
    • Webstorm, Brackets, Visual Studio Code, Xamarin Studio, Visual Studio for Mac, Coda 2
  • FTP
    • Filezilla
  • REMOTE DESKTOP
    • Microsoft Remote Desktop

not quite finished yet…