jbmurphy.com

How we recovered from a Microsoft SQL 2005 suspect msdb database
I am not a MS SQL Server guru. Honestly, it is a “set it and forget it” technology for me. After our last patching episode, we ended up with a SQL Server Agent offline, and our msdb file was listed as suspect. Seems like there are 2 ways to recover from this. 1 restore a backup, or two recreate with this method. Since we had a backup from the night before, we started with this method.
1. First, we restored the msdb file from Symantec Backup Exec to another SQL server as a user database. Since it was the middle of the day, and we could not take down the SQL server, we wanted to get a copy of the the MSDBData.mdf and MSDBlog.ldf files. This was the quickest way to do it.
2. We stopped the SQL server, moved the suspect msdb files aside, and placed the restored versions in the right place.
3. Started the SQL Server, and SQL Server Agent came back online. All seems happy
Using cURL to pull Google Reader starred items – Part 2 UnStaring in Google Reader
In the first part of this tutorial, I pulled a couple of variables from the XML feed of my Google Reader’s “starred items”. Now I wanted to “process” the item and UnStar it. This was not easy for me to put together – it was my first attempt at working with the Google API.

First I needed to get authenticated against Google Services for accessing Google Reader. Here is the shell script to do this:
```
stty -echo
read -p "Password: " password; echo
stty echo
RESULT=$(curl -s https://www.google.com/accounts/ClientLogin \
--data-urlencode [email protected] --data-urlencode Passwd=$password \
-d accountType=GOOGLE \
-d source=MyAppName \
-d service=reader)
```
This returned some html that included the AUTH code that I needed to add to the header of each Google Reader Request. I used this AUTH to get a Token (my understanding is that if I wanted to edit an item, I needed the token too). Here are the two pieces of code to parse the AUTH get the Token:
```
AUTH=$(echo "$RESULT" | grep 'Auth=' | sed  s/Auth=//)
TOKEN=$(curl -s --header "Authorization: GoogleLogin auth=$AUTH" http://www.google.com/reader/api/0/token)
```
Putting the AUTH together with the Token, and the Source and id from this post, you end up with a cURL command that can mark an item as UnStared:
```
curl -s http://www.google.com/reader/api/0/edit-tag?client=MyAppName --request POST --header "Authorization: GoogleLogin auth=$AUTH" \
--data-urlencode r=user/-/state/com.google/starred  \
--data-urlencode async=true \
--data-urlencode s=$SOURCE \
--data-urlencode i=$OBJID \
--data-urlencode T=$TOKEN
```
I was surprised when i got this to work. I am still scared of APIs/REST.
Using cURL to pull Google Reader starred items – Part 1 xpath
A while ago, I wrote a post about using ruby to parse the xml feed of shared starred items in Google Reader. One thing that I did not like about this solution was that I could not get the URL AND mark the item as un-starred. Since I had been playing with REST in these two prior posts, I figured I could re-write my code to pull down starred item’s URLs, and mark them as un-starred. I wanted to eliminate ruby, as I just don’t use it that often and I feel like I am re-inventing the wheel every time. This too a lot longer than I thought it would, but I figured it out (I think)

As this article explains, you can share out your Google reader starred items. First step was the find the needed values from this XML feed and put the values into variables. I turned to xpath (xpath is installed by default on OS X, it is part of the Perl library XML::XPath).

For the second part of this tutorial, where we mark the item as “un-starred”, we need 2 variables form the Xml feed: id & source. My end goal was to put these URLs into Together.app, so I needed the URL too. Title was just for fun.

Here is the non ruby code to pull the variables that I needed out of the XML of the shared starred items in Google Reader.
```
XML=$(curl -s http://www.google.com/reader/public/atom/user/YOURUSERID/state/com.google/starred?n=1 | xmllint --format -)
OBJID=$(echo "$XML" | xpath "//entry/id" 2>/dev/null | awk -F"[<>]" '{print $3}')
TITLE=$(echo "$XML" | xpath "//entry/title" 2>/dev/null | awk -F"[<>]" '{print $3}')
URL=$(echo "$XML" | xpath "//entry/link/@href" 2>/dev/null | sed 's/\"//g' | sed 's/href\=//g' | sed 's/\ //g')
SOURCE=$(echo "$XML" | xpath "//entry/source/@gr:stream-id" 2>/dev/null | sed "s/gr:stream-id=//g" | sed "s/\"//g" | sed 's/\ //g')
```
After getting those variables, I wanted to put the URL in together.app (this has not changed since the previous post). Here is that code:
```
echo "Adding $TITLE to Together"
osascript << EOT
tell application "Together" to import url "$URL" as web PDF
EOT
```

PowerShell code to update a CRM 2011 field (using REST/oData)

In this earlier post I showed how to loop through all the contacts in CRM 2011. Next thing I wanted to do was to update a field on each Account. So I needed to figure out how to update data, not just read it. Here is the code to do that:

	$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
	$url="http://your.crm.com/Instance/xrmservices/2011/OrganizationData.svc/AccountSet(guid'GUIDofAccount')"
	$webclient = new-object System.Net.WebClient
	$webclient.UseDefaultCredentials = $true
	$webclient.Headers.Add("Accept", "application/json")
	$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
	$webclient.Headers.Add("X-HTTP-Method", "MERGE")
	$stringToUpload="{`"AccountNumber`":`"123456`"}"
	$resultString=$webclient.UploadString($url,$stringToUpload)

PowerShell, JSON, oData and CRM 2011 (or SharePoint 2010)

I am working on how to consume data from/to SharePoint 2010 and from/to CRM 2011. I decided to try and see if I can get the data to display in PowerShell, figuring if I can get it there, I should be able to get it anywhere? Here is the code to loop through all the Contacts in a CRM 2011 deployment.

Took me a while to figure this out. Should work with any oData source?

$url="http://your.crm.server/Instance/XRMServices/2011/OrganizationData.svc/ContactSet"
$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
while ($url){
	$webclient = new-object System.Net.WebClient
	$webclient.UseDefaultCredentials = $true
	$webclient.Headers.Add("Accept", "application/json")
	$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
	$dataString=$webclient.DownloadString($url)
	$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
	$data=$json.DeserializeObject($dataString)
	foreach ($result in $data.d.results){
		write-host "$($result.FullName) , $($result.EMailAddress1)"
	}
	Write-Host "Press any key to continue ..."
	$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
	if ($data.d.__next){
		$url=$data.d.__next.ToString()
	}
	else {
		$url=$null
	}
}

To loop through the items of a SharePoint 2010 list, you would change $url to:

$url=”http://sharepoint2010.server.com/_vti_bin/listdata.svc/Announcements”

Not sure if this would be valuable to anyone, but here it is!

How to tell if your PowerShell session is remote
I wanted to write a conditional to prevent certain things from runing if in a remote PSSession.

If you are in a standard PowerShell session the following is returned:
```
[Environment]::GetCommandLineArgs()[0] = C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
```
If you are in a remote PSSession:
```
[Environment]::GetCommandLineArgs()[0] = C:\Windows\system32\wsmprovhost.exe 
```
How to add custom CSS code to all SharePoint 2010 pages (CustomAction)
It occurred to me that you can add a CSS link to the top of each page using the same method that I showed in this post. The only thing you change is the code in “CustomAction”. The code would be”
```
<CustomAction
  Location="ScriptLink"
  ScriptBlock="document.write('<link rel="stylesheet" type="text/css" href="/Relative/PathTo/file.css"></link>');"
  Sequence="XX" />
```
How to add custom JavaScript code to all SharePoint 2010 pages (CustomAction)
There are plenty or articles on how to do this. This is more of a note for myself, as I have to “re-learn” this every time I need to customize SharePoint. There are 2 ways (that I know of) that you can add code to every page in SharePoint 2010, 1 by the AdditionalPageHead delegate control, or ,2 by Custom Action. This article is about #2 using a Custom Action. This article is about #1 – Delegate Controls

To add JavaScript to every page via CustomAction:
1. Start Visual Studio, and create a new Empty SharePoint Project (uncheck the Create Directory for Solution because you salways create the destination directory yourself)
2. Deploy as a Farm Solution (I have not figured what you can and can’t do with sandboxed solutions yet)
3. RightCLick the Project and select Add Module and name the Module at the bottom (I name the Module the name of the Document Library where I want to put the file)
4. Delete Sample.txt and add the javascript file you want (I usually have to go to the correct folder, put the file there, right click the ProjectName and select “Show all files”, then I can include the file in the project).
5. Chop up the elements.xml file. The final should look like this below:
```
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Module Name="Test" Url="Test">
    <File Path="Test\jquery-1.7.min.js" Url="jquery-1.7.min.js" Type="GhostableInLibrary" />
  </Module>
</Elements>
```
Now you need to the code to add the file to the top of every page, add the “CustomAction” stuff below
```
  <CustomAction
    ScriptSrc="~SiteCollection/Test/jquery-1.7.min.js"
    Location="ScriptLink"
    Sequence="11">
  </CustomAction>
```
Final looks like this:

Right click the project and package it up.

Common PowerShell commands to work with solutions are (you must create the destination Document Library before you try to install this code):
```
To add/install
Add-SPSolution H:\Path\To\wsp\Test.wsp
Install-SPSolution Test.wsp -GACDeploy
Update-SPSolution -literalpath  H:\Path\To\wsp\Test.wsp -identity Test.wsp -GACDeploy

To remove
Uninstall-SPSolution Test.wsp
Remove-SPSolution Test.wsp
```
This results in a new Feature in manage features (Active it!)

A new file in the document library, and the following in the source code for the page. Done!: