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.