Create EC2 instance via Terraform

Powershell Script to Hard Rebuild VS Solution

GitHub Gist: https://gist.github.com/fcmendoza/c436555063d3233b68332a2e46c39197

XML SQL Input Parameter With Element Attributes

Github Gist =)

Cool!

HTTP SSL Certificate for Local IIS Development

Thanks to Rob Bagby's excellent blog post and screencast on Channel9, and to Scott Hanselman's blog post about IIS Express and SSL.

1. Get selfssl.exe (the first link above points to the download location of this tool)

2. Open a command prompt as an administrator and:

C:\system32> cd C:\downloads
C:\downloads>selfssl /N:cn=localhost /V:99999 /S:1

3. Export the newly created certificate using IIS (as described in the first link)

4. Add the certificate to Local Computer (as described in the first link) to make the computer (and web browsers and other services) trust our self-signed cert and avoid getting that SSL warning/error.

Note: for IIS Express you just need to set your project to use SSL on the project's properties window (set the SSL Enabled property to True) and then add the existing IIS Express certificate (the one named IIS Express Development Certificate) to the Local Computer (following the instructions found on the first link above)

Note 2: After you added the IIS Express certificate as indicated above, it can take a little for these changes to take effect; so it's possible that you still get the SSL warning on the web browser. Just wait a little (this happened to me and I thought there was an error with the setup, then a few minutes later it started working).

Note 3: Note 2 is not true --I still get that SSL warning.

SQL Server Pivot

Example (thanks to http://goo.gl/oCM0q):

declare @data as table (Product varchar(10), Name varchar(20), Value varchar(10))

insert into @data values ('Beer', 'ProductCode',    'MSTF')
insert into @data values ('Beer', 'LocationScript', 'Office')
insert into @data values ('Milk', 'ProductCode',    'MSTF')
insert into @data values ('Milk', 'ProductSource',  'c.60')
insert into @data values ('Milk', 'LocationScript', 'Office')
insert into @data values ('Soda', 'ProductCode',    'APPL')
insert into @data values ('Soda', 'ProductSource',  'c.60')
insert into @data values ('Soda', 'LocationScript', 'Industry')
insert into @data values ('Wine', 'ProductSource',  'c.90')
insert into @data values ('Wine', 'Alias',  'Lorem')

select * from @data

SELECT Product, ProductCode, ProductSource, LocationScript, Alias
FROM (
 SELECT Product, Name, Value
 FROM @data
) dt
PIVOT (MAX(Value) FOR Name IN (ProductCode, ProductSource, LocationScript, Alias)) AS pv
ORDER BY Product

Result:

Product    Name                 Value
---------- -------------------- ----------
Beer       ProductCode          MSTF
Beer       LocationScript       Office
Milk       ProductCode          MSTF
Milk       ProductSource        c.60
Milk       LocationScript       Office
Soda       ProductCode          APPL
Soda       ProductSource        c.60
Soda       LocationScript       Industry
Wine       ProductSource        c.90
Wine       Alias                Lorem

(10 row(s) affected)

Product    ProductCode ProductSource LocationScript Alias
---------- ----------- ------------- -------------- ----------
Beer       MSTF        NULL          Office         NULL
Milk       MSTF        c.60          Office         NULL
Soda       APPL        c.60          Industry       NULL
Wine       NULL        c.90          NULL           Lorem

(4 row(s) affected)

Single vs SingleOrDefault

If no element is found Single throws an exception, but SingleOrDefault returns null.

Both methods throw an exception if there's more than one element in the sequence.

For example if there's more than one element SingleOrDefault() throws something like the following: System.InvalidOperationException: Sequence contains more than one element

NOTE: FirstOrDefault should be used when we have more than one element in the sequence and want only one element from it (in this case -as the name indicates- the first one) FirstOrDefault returns null when founds nothing (similar to SingleOrDefault).

public Transaction GetTransaction(Transaction transaction) {
    var transactions = GetTransactions(from: transaction.Date, to: transaction.Date);
    return transactions != null 
        ? transactions.Where(t => t.ID == transaction.ID).FirstOrDefault()
        : null;
}

Cannot drop the database because it is being used for replication

The database used to be part of a replication but apparently something remained somewhere else.

The following commands helped:

use master
go

exec sp_helpreplicationdb 
exec sp_removedbreplication 'Northwind'

ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
ALTER DATABASE Northwind SET MULTI_USER

DROP DATABASE Northwind