#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&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema”&gt; <Title>Incorrect Title</Title> </Book> 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)’) likeContinue reading “#SQL: Update xml field from value from other table”

#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.ps1Continue reading “#PowerShell: Register AWS EC2 instances in Amazon Route53 (new way)”

#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-SPPermissionsReportContinue reading “#SharePoint 2013 #PowerShell: How to get user permissions report”

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.Continue reading “Apply GeoTrust certificate to AWS ELB”

Backup you on-prem/local #PostgreSQL database to cloud #AWS #RDS snapshot

#PostgreSQL #MySQL backup for $0.026 + $0.125 per GB-month Total costs of the backup operation: $0.026 + $0.125 per GB-month Script below will do the following steps: create PostgresSQL AWS RDS t1.micro instance wait till instance started by using aws rds describe-db-instances get AWS RDS instance address copy database create AWS RDS instance snapshot andContinue reading “Backup you on-prem/local #PostgreSQL database to cloud #AWS #RDS snapshot”

Doctrine 1.x log all queries

To log all queries we need to use Event Listeners class QueryDebuggerListener extends Doctrine_EventListener { public function preStmtExecute(Doctrine_Event $event) { $q = $event->getQuery(); $params = $event->getParams(); while (sizeof($params) > 0) { $param = array_shift($params); if (!is_numeric($param)) { $param = sprintf(“‘%s'”, $param); } $q = substr_replace($q, $param, strpos($q, ‘?’), 1); } error_log($q); } } $queryDbg =Continue reading “Doctrine 1.x log all queries”

Restore #AWS RDS instance from a database snapshot by using AWS CLI

The AWS Command Line Interface is a unified tool to manage your AWS services. I am using AWS RDS PostgreSQL for my application. I don’t need to run this application 24/7/365. So I have the ability to stop periodically my instances to save some money in my pocket. Nobody wants to do repeated tasks manually.Continue reading “Restore #AWS RDS instance from a database snapshot by using AWS CLI”