#SQL: Update xml field from value from other table

We are going to update Title field in XML below. Assume we have incorrect values there in our Library table. We will find the correct values by using Title field from AllBooks table.

<Book xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <Title>Incorrect Title</Title>
UPDATE Library SET info.modify('replace value of (/Book/Title/text())[1] with sql:column("Title")')
FROM Library 
LEFT OUTER JOIN AllBooks ON Library.BookID=AllBooks .ID
WHERE info.value('data((/Book/Title)[1])','nvarchar(max)') like 'Incorrect Title%'

#PowerShell: Register AWS EC2 instances in Amazon Route53 (new way)

Amazon Route 53 is a highly available and scalable Domain Name System (DNS) web service.
Just in case – Amazon EC2
I use this script https://github.com/konstantinvlasenko/cloud/blob/master/Register-CNAME.ps1 to provide meaningful names for them.
Simple usage:

$config = @{ DomainName = 'mylab.com' }
$name = "www.$($config.DomainName)"
# get instances
$instance = (Get-EC2Instance $InstanceId).RunningInstance
# update R53
.\Register-CNAME.ps1 $config $name $instance.PublicIpAddress

Advanced usage (register in another account; registering A record):

$config = @{ DomainName = 'mylab.com'; AssumeRoles = @{ R53 = @{ ARN = 'arn:aws:iam::600021112340:role/Route53'; SessionName = 'Friends' } }; }
$name = "www.$($config.DomainName)"
# get instances
$instance = (Get-EC2Instance $InstanceId).RunningInstance
# update R53
.\Register-CNAME.ps1 $config $name $instance.PublicIpAddress 'A'

#SharePoint 2013 #PowerShell: How to get user permissions report

function Get-SPPermissionsReport($web, $recursive)
  $web | Get-SPUser | % { New-Object PSObject -Property @{
    UserLogin = $_.UserLogin
    'Roles given explicitly' = $_.Roles
    'Roles given via groups' = $_.Groups | %{$_.Roles}
    Groups = $_.Groups
    Url = $web.Url
  if($recursive) { $web.Webs | % { Get-SPPermissionsReport $_ $recursive } }
$web = Get-SPWeb http://yoursharepoint/sites/department
Get-SPPermissionsReport $web $true | Sort-Object UserLogin | Out-GridView

Then you can apply an additional filter by user or url right in the GridView.
You can add an additional matching criteria in the GridView: e.g. match by Role/Group name

Apply GeoTrust certificate to AWS ELB

Here is the tool which I used to verify correctness of my AWS ELB SSL configuration.


  • Certificate Name – put here whatever you want
  • Private Key – copy and past content of server.key file
  • Public Key Certificate – copy and paste content of your_site_name_ee.cer file

This will be enough to pass validation by the tool. But you will get one warning. Because GeoTrust  is quite new player on the market. Old browser doesn’t have information about GeoTrust. So GeoTrust provides intermediate certificates. You need to set Certificate Chain field if you care about old browsers.

  • Certificate Chain – copy and paste content of GeoTrust Extended Validation SSL CA – G2.txt and then copy and paste content of GeoTrust Primary Certification Authority.txt