automatically make your web.config file force from HTTP to HTTPS for SSL Security

I know this answer is out there in some form or fashion but I like to have it here for quicker access. This is the section of code that I use to force all of my websites to roll over to SSL in my web.config file.

<system.webServer>
	<rewrite>
		<rules>
			<clear />
			<rule name="Redirect to https" stopProcessing="true">
				<match url=".*" />
				<conditions>
				<add input="{HTTPS}" pattern="off" ignoreCase="true" />
				</conditions>
				<action type="Redirect" url="https://{HTTP_HOST}{REQUEST_URI}" redirectType="Permanent" appendQueryString="false" />
			</rule>
		</rules>
	</rewrite>
</system.webServer>

How to check on your SSRS Execution Log History using a SQL Script

Using this sql script I’m able to run it and get a quick list of what SSRS reports just ran. The script also gives me details like the username, format of report, the report name and what parameters we’re used in creating the SSRS report.

It’s a pretty handy SQL script for seeing data quickly. I also found this online and modified it for my use but if anyone knows of the originator of this, let me know.

Happy to give credit for all these. I found lots of scripts like these online and because SSRS isn’t super popular, maybe that’s why I can’t find them as easily anymore.

SELECT  ex.UserName ,
        ex.Format ,
        ex.TimeStart ,
        cat.Name ,
        ex.Parameters ,
        CONVERT(NVARCHAR(10), ex.TimeStart, 101) AS rundate
FROM    ExecutionLog AS ex ,
        Catalog AS cat
WHERE   ex.ReportID = cat.ItemID
ORDER BY ex.TimeStart DESC;

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;