jbmurphy.com – Page 32 – Tech ramblings by Jeffrey B. Murphy

Accessing SharePoint 2010 BCS lists via SOAP/WebServices for use in a jQuery autocomplete

So it seems that you can’t access BCS list data via REST, according to this article. But it seems that you can access the list data through SOAP. I used the code below to query a BCS list that points to a MSCRM 2011 backend (I know I could go right to CRM via REST, but then I would have XSS issues). I then take the results and use them for a jQuery autocomplete for an input box. Obviously in the code below, Contacts is a BCS “external List”


$('#ContactSearchTextbox').autocomplete({
source: function( request, response ) {
	$().SPServices({
	operation: "GetListItems",
	async: false,
	listName: "Contacts",
	CAMLViewFields: "<ViewFields><FieldRef Name='FullName' /><FieldRef Name='ContactId' /><FieldRef Name='ParentCustomerIdName' /></ViewFields>",
	CAMLQuery: "<Query><Where><Contains><FieldRef Name='FullName' /><Value Type='Text'>"+request.term+"</Value></Contains></Where></Query>",
	completefunc: function (xData, Status) {
	$('#SearchResults').html(xData.responseText);
	response($.map( $(xData.responseXML).SPFilterNode("z:row"), function( item ) {
	return {
	label: $(item).attr('ows_FullName') + "(" + $(item).attr('ows_ParentCustomerIdName') +")",
	value: $(item).attr('ows_ContactId')
		}
	}));
	}
	});	    
},
minLength: 4
});

Add an appointment/email/phone activity to CRM 2011 via PowerShell (REST/oDATA)

I am looking to move some older list data into CRM 2011, so I wanted a way to create some “activities” via powershell. Below is the code to do that. In the first part of the code, I create a PowerShell object and then I convert it to a JSON object via PowerShell 3’s new ConvertTo-JSON cmdlet.

$JsonObject = New-Object psobject -Property @{
ActivityTypeCode = "appointment"
Subject = "jbmurphy TEST"
Description = "This is a descritpion"
ScheduledStart = "2012-03-20T18:00:00Z"
ScheduledEnd = "2012-03-20T18:00:00Z"
Location = "Office"
}

# I am using PowerShell 3's ConvertTo-Json
$jsonEntity=$JsonObject | ConvertTo-Json
#$url="http://crmserver.company.com/OrgName/xrmservices/2011/OrganizationData.svc/EmailSet"
#$url="http://crmserver.company.com/OrgName/xrmservices/2011/OrganizationData.svc/PhoneCallSet"
$url="http://crmserver.company.com/OrgName/xrmservices/2011/OrganizationData.svc/AppointmentSet"

$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Accept", "application/json")
$http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
$http_request.send($jsonEntity)
$http_request.statusText

PowerShell script to search SharePoint Search WebService via SOAP

I wanted to copy all the files found in a SharePoint Search result for a scope that lived on a file share. So I wrote the following PowerShell code to query a SharePoint Search scope and find the url for each result.

#$KeywordQuery="Cows"
$SQLQuery="SELECT WorkId,Path,Title,Write,Author from Scope() WHERE `"Scope`"='NarrowScope' AND FREETEXT(defaultproperties,'Cows')"

$CountToReturn = 1000
$xmlDoc = new-object System.Xml.XmlDocument
$QueryPacket = $xmlDoc.CreateElement("QueryPacket")
$QueryPacket.SetAttribute("xmlns", "urn:Microsoft.Search.Query")
$Query = $xmlDoc.CreateElement("Query")
[void]$querypacket.AppendChild($Query)
$Context = $xmlDoc.CreateElement("Context")
[void]$query.AppendChild($Context)
$QueryText = $xmlDoc.CreateElement("QueryText")

#If you are Using the KeyWord method
#$QueryText.SetAttribute("type", "string") 
#$QueryText.set_InnerXMl($KeywordQuery)

#IF you are using SQL method
$QueryText.SetAttribute("type", "MSSQLFT")
$QueryText.set_InnerXMl($SQLQuery)

[void]$context.AppendChild($QueryText)
$Range = $xmlDoc.CreateElement("Range")
[void]$query.AppendChild($Range)
$Count = $xmlDoc.CreateElement("Count")
$Count.set_InnerXMl($CountToReturn)
[void]$range.AppendChild($Count)

$IncludeSpecialTermResults=$xmlDoc.CreateElement("IncludeSpecialTermResults")
$IncludeSpecialTermResults.set_InnerXML("false")
[void]$query.AppendChild($IncludeSpecialTermResults)

$PreQuerySuggestions=$xmlDoc.CreateElement("PreQuerySuggestions")
$PreQuerySuggestions.set_InnerXML("false")
[void]$query.AppendChild($PreQuerySuggestions)

$HighlightQuerySuggestions=$xmlDoc.CreateElement("HighlightQuerySuggestions")
$HighlightQuerySuggestions.set_InnerXML("false")
[void]$query.AppendChild($HighlightQuerySuggestions)

$IncludeRelevantResults=$xmlDoc.CreateElement("IncludeRelevantResults")
$IncludeRelevantResults.set_InnerXML("false")
[void]$query.AppendChild($IncludeRelevantResults)

$IncludeHighConfidenceResults=$xmlDoc.CreateElement("IncludeHighConfidenceResults")
$IncludeHighConfidenceResults.set_InnerXML("false")
[void]$query.AppendChild($IncludeHighConfidenceResults)

$Service = New-WebServiceProxy -UseDefaultCredential -uri http://sharepoint.search.com/Search/_vti_bin/Search.asmx
[ xml ]$Results = $Service.Query($QueryPacket.OuterXml)
write-host "Results=$($Results.ResponsePacket.Response.Range.Count)"
foreach ($Doc in $Results.ResponsePacket.Response.Range.Results.Document){
$PATH=$($Doc.Action.LinkUrl.("#text")).Replace("/","\").Replace("file:","")
write-host $PATH
#here is where you put your copy cmd
cp $PATH c:\Temp
}

SQL query to find number of WordPress posts per week

I wanted to know how many posts I have been creating each week. Here is a quick MySQL query to find out:

SELECT DISTINCT extract(week from date(post_date)) AS WeekNumber, count( * ) AS Posts 
FROM wp_posts where post_type = 'post' AND post_status = 'publish' AND post_date like '%2012-%' 
GROUP BY WeekNumber;

Returns:

+------------+-------+
| WeekNumber | Posts |
+------------+-------+
|          1 |     4 | 
|          2 |     3 | 
|          3 |     3 | 
|          4 |     3 | 
|          5 |     3 | 
|          6 |     2 | 
|          7 |     3 | 
|          8 |     1 | 
|          9 |     2 | 
|         10 |     2 | 
|         11 |     3 | 
|         12 |     2 | 
|         13 |     2 | 
|         14 |     3 | 
+------------+-------+

PowerShell how to create an object (Note to Self)

$test = new-object psobject -Property @{
Name = 'John Doe'
Age = 3
Amount = 10.1
MixedItems = (1,2,3,"a")
NumericItems = (1,2,3)
StringItems = ("a","b","c")
}

Another example:

$Object = New-Object PSObject -Property @{
        LineNumber       = $LineNumber
        Date             = $TodayDate
        ServerName       = $svr
        DatabaseName     = $Database
        UserName         = $user.name
        CreateDate       = $CreateDate
        DateLastModified = $DateLastModified
        AsymMetricKey    = $user.AsymMetricKey
        DefaultSchema    = $user.DefaultSchema
        HasDBAccess      = $user.HasDBAccess
        ID               = $user.ID
        LoginType        = $user.LoginType
        Login            = $user.Login
        Orphan           = ($user.Login -eq "")
    }

Second example taken from here

PowerShell to get all items in a SharePoint 2007 list via Web Services/SOAP

I wanted to get a list’s contents in a SharePoint 2007 site via PowerShell. I ran into only one issue – how to handle the pagination. When creating the Xml to include the next page, I was running into formatting issues because the text contained a “=”. This link suggested that I create the XML element first then add the innerText after. Worked after that!

TheBelow is my script to get the contents of a SharePoint 2007 list.

$listName = "List Name"
$xmlDoc = new-object System.Xml.XmlDocument
$query = $xmlDoc.CreateElement("Query")
$viewFields = $xmlDoc.CreateElement("ViewFields")
$queryOptions = $xmlDoc.CreateElement("QueryOptions")
$rowLimit = "50"
$service = New-WebServiceProxy -UseDefaultCredential -uri http://sharepoint2007.comapny.com/_vti_bin/lists.asmx?WSDL
$nextPage=$true
while($nextPage){
$list = $service.GetListItems($listName, "", $query, $viewFields, $rowLimit, $queryOptions, "")
$list.data.row | select ows_ID,ows_Created,ows_Title
if ($list.data.ListItemCollectionPositionNext){
$nextPage=@"
<Paging ListItemCollectionPositionNext="" />
"@
$queryOptions.set_InnerXml($nextPage)
$queryOptions.ChildNodes[0].Attributes["ListItemCollectionPositionNext"].InnerText = "$($list.data.ListItemCollectionPositionNext)"
}
else {
write-host "done"
$nextPage=$false
}
}

4500 visits in March!

Hope your vist was somewhat helpful!
PowerShell: redirect (System.Xml.XmlDocument).Save() to console

I saw the code below somewhere, and could not remember how to do it later when I needed it. System.Xml.XmlDocument has a built in method to save the document to a file. I wanted to simulate that, but redirect it to the console. I finally found the code how to do it.

$xml = new-object System.Xml.XmlDocument
$xml.Save([Console]::Out)

Now I know where to look to remind myself.