#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”

Enable SQL protocol by using PowerShell

A stand-alone installation of SharePoint usually utilizes SQL Server Express. The instance name is SHAREPOINT. (This is true for SharePoint 2013). You must enable TCP protocol to access the SharePoint back-end. PS> function EnableSQLProtocol($instance, $proto) { [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null $smo = ‘Microsoft.SqlServer.Management.Smo.’ $wmi = new-object ($smo + ‘Wmi.ManagedComputer’). $uri = “ManagedComputer[@Name=’$(hostname)’]/ServerInstance[@Name=’$instance’]/ServerProtocol[@Name=’$proto’]” $pContinue reading “Enable SQL protocol by using PowerShell”