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 

Custom Errors Web config

Generic Runtime Error (unknown error with no description or clue of what just happened):

Add the following custom errors element to web.config (inside system.web):

< customErrors mode="Off"/ >

Now more information will be displayed:

Git rewrite history to Github

To remove the last commit from the master branch at Github but "saving" that last commit to a branch named mein-bak

[master]> git checkout -b mein-bak
[mein-bak]> git push origin mein-bak
[mein-bak]> git checkout master
[master]> git reset --hard HEAD~1
[master]> git push --force origin master

Before applying changes this is how master looks at Github:

a-b-c-d-e-f-g [master]

And this is how it looks after applying those changes:

a-b-c-d-e-f [master]
           \
            g [mein-bak]

Run program as different user - runas command

Open a command prompt console and execute the following:

C:\Users\jdoe>runas /user:loremipsum\jsmith notepad

Where loremipsum is the machine's name (or the domain name) and notepad the program you want to execute as jsmith.

The command prompt will ask for jsmith's password and if entered correctly (and if such user has sufficient permissions) the program will be executed/open.

Send email PowerShell

Create a script like the following and save it as send_email.ps1.

$from = New-Object System.Net.Mail.MailAddress "from@domain.com" 

$to = New-Object System.Net.Mail.MailAddress "jdoe@gmail.com" 

$messge = new-object system.Net.Mail.MailMessage $from, $to

$messge.IsBodyHTML = $true
$messge.Subject = "Test email to jdoe"
$messge.body = "Lorem ipsum dolor sit amet. Regards."

$smtpServer = 'localhost'
$port = '25'

$smtp = new-object Net.Mail.SmtpClient($smtpServer, $port)

$smtp.Send($messge);

Open PowerShell, go to the directory where the script was saved and execute it.

If you have permissions to execute scripts in Powershell, and the SMTP server is correctly configured, and the recipient email account exists, then you should get the email.

sp_who and sp_who2 Blocking processes

From
http://stackoverflow.com/questions/2234691/sql-server-filter-output-of-sp-who2 and
http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

With sp_who and sp_who2 we can determine what process is blocking a table for example. We can use the following script to filter out the sp_who2 output:

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE ....

Another way is to use a custom sp_who3 sp for this:

CREATE PROCEDURE [dbo].[sp_who3] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    SPID                = er.session_id
    ,BlkBy              = er.blocking_session_id      
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes     
    ,Executions         = ec.execution_count  
    ,CommandType        = er.command         
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )        
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY 
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
ORDER BY
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id
 
END

SQL Assembly Error

Exception:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues

Possible solution:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE database_name SET TRUSTWORTHY ON 

USE database_name
GO

EXEC sp_changedbowner 'sa'

Create CLR Assembly SQL Function

Create a cs code file with the desired functionality. Example:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class TimeZoneHelper
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime MountainToUtc(DateTime mountainDateTime)
    {
         TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById("Mountain Standard Time"); // "Mountain Standard Time" it's the ID regardless of daylight saving time (i.e. no matters if right now is daylight saving time, the ID name remains the same)

         DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(mountainDateTime, zone); // it automatically takes care of daylight saving time

         return utcTime;
    }
      
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime PacificToUtc(DateTime pacificDateTime)
    {
         TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time"); // "Pacific Standard Time" it's the ID regardless of daylight saving time (i.e. no matters if right now is daylight saving time, the ID name remains the same)

         DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(pacificDateTime, zone); // it automatically takes care of daylight saving time

         return utcTime;
    }
}

Save the file as TimeZoneHelper.cs
Open the Visual Studio 2008 Command Prompt (I also tried the 2010 version but it didnt work) and run the following:

csc /target:library C:\Users\jon.connor\Downloads\TimeZoneHelper.cs

This will create a TimeZoneHelper.dll in the same directory where the source file is in.
In SQL Server run the following to enable CLR code to run:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE Northwind SET TRUSTWORTHY ON;
GO

You also have to grant permissions to a SQL Server login:

GRANT UNSAFE ASSEMBLY TO some_user;
GO

Now you can create the assembly in the Northwind database

CREATE ASSEMBLY TimeZoneHelperAssembly 
FROM 'C:\Users\jon.connor\Downloads\TimeZoneHelper.dll' 
WITH PERMISSION_SET = UNSAFE
GO

Finally create functions based on the assembly methods.

CREATE FUNCTION dbo.MountainToUTC(@mountainDateTime datetime) 
RETURNS datetime 
AS EXTERNAL NAME 
[TimeZoneHelperAssembly].[TimeZoneHelper].[MountainToUtc]
GO

CREATE FUNCTION dbo.PacificToUTC(@pacificDateTime datetime) 
RETURNS datetime 
AS EXTERNAL NAME 
[TimeZoneHelperAssembly].[TimeZoneHelper].[PacificToUtc]
GO

And now you can start using these functions as you would normally do:

select dbo.MountainToUTC('2012-04-13 16:00:00'), 
       dbo.PacificToUTC('2012-04-13 16:00:00')

Thanks to http://goo.gl/W4u42

WCF MSMQ Message Retries

Using this settings (maxRetryCycles="50", retryCycleDelay="00:01:30", receiveRetryCount="0") it will retry the message 50 times with a delay of 90 seconds between every retry.

< binding name="DasBinding"
 exactlyOnce="true"
 durable="true"
 maxRetryCycles="50"
 retryCycleDelay="00:01:30"
 receiveRetryCount="0"
...

In this page (http://msdn.microsoft.com/en-us/library/ms731380.aspx) there's more information about the settings for the MSMQ binding in WCF. Here I list a few of them and their descriptions:

maxRetryCycles

An integer that indicates the number of retry cycles used by the poison-message detection feature. A message becomes a poison message when it fails all delivery attempts of all cycles. The default is 3. For more information, see MaxRetryCycles.

retryCycleDelay

A TimeSpan value that specifies the time delay between retry cycles when attempting to deliver a message that could not be delivered immediately. The value defines only the minimum wait time because actual wait time can be longer. The default value is 00:10:00. For more information, see RetryCycleDelay.

receiveRetryCount

An integer that specifies the maximum number of times the queue manager should attempt to send a message before transferring it to the retry queue. The maximum number of immediate delivery attempts. The default is 5.

Nullable int in Xml Schema

If you want to be able to submit something like

< Counter1 >0< / Counter1 >
< Counter2 / >

then you need to define the following in the XSD file:

< xs:element name="Counter1" type="xs:integer" minOccurs="0" default="0" / >
< xs:element name="Counter2" type="xs:integer" minOccurs="0" default="0" / >

Group by Date SQL Query

From: http://goo.gl/LfvLO
The following queries group the result set by month:

select Year(CreatedOnUTC), Month(CreatedOnUTC), COUNT(1) cont 
from ServiceLog 
group by Year(CreatedOnUTC), Month(CreatedOnUTC)
order by Year(CreatedOnUTC) desc, Month(CreatedOnUTC) desc

select dateadd(month, datediff(month, 0, CreatedOnUTC),0), COUNT(1) cont 
from ServiceLog
group by dateadd(month, datediff(month, 0, CreatedOnUTC),0)
order by dateadd(month, datediff(month, 0, CreatedOnUTC),0) desc

Log4net Exclude messages from log

In the config file add a StringMatchFilter with the string to discard/filter.

Example:

...

 
  
  
 
 
  
  
 
 
...

Undo TFS changes

Note: You must be an administrator in the repository where you want to perform the following commands.

1. Open a Visual Studio Command Prompt console.
2. Run tf undo {file path} /workspace:{workspace};{username} /server:{TFS name/IP}
Example:
tf undo $/Dorms/Develoment/src/Mein.Solution/Mein.Solution.vsmdi /workspace:"Jon's PC";jon.connor /server:maintfs-dev01

Note: The user and workstation info can be found by right-clicking on the file in the TFS' Source Control Explorer and then selecting Properties. A window will appear and the user and Workstation info will be listed under the Status tab.

Linq NOT IN clause

To emulate a SQL "NOT IN" clause like the following:

SELECT * FROM Clients WHERE ClientID NOT IN (select ClientID from AssignedClients)

We can do the following in Linq:

IEnumerable< Client > assignedClients  = _repo.GetAssignedClients();
IEnumerable< Client > allClients       = _repo.GetAllClients();
IEnumerable< Client > availableClients = from c in allClients
                                         where !(assignedClients.Any(a => a.ClientID == c.ClientID))
                                         select c;

Where Client has the following definition:

public class Client {
    public int ClientID { get; set; }
    public string ClientName { get; set; }
    public bool IsActive { get; set; }
}

Another example:

var clientids = new int[] {1, 2, 3}; // IEnumerable of integers
var records = new Record[] { new Record{ClientId = 1}, new Record{ClientId = 9}, new Record{ClientId = 8}, new Record{ClientId = 2} };

var theRest = from r in records
              where !(clientids.Any(id => id == r.ClientId))
              select r;

And this is another way of obtaining the same

var theRest = records.Where(r => !clientids.Contains(r.ClientId));

They both will return the records whose ClientId is not in the list of clientids.