Looping through SPList items

Development

Now this is something I’m guilty of time and time again. Within my PowerShell scripts I quite often query SharePoint lists and loop through them in the following way…

$site = New-Object Microsoft.SharePoint.SPSite( $url )
$web = $site.OpenWeb()
$list = $web.List

for ( $i = 0; $i < $list.Items.Count; $i++) {
   SPListItem listItem = activeList.Items[i];
   htmlWriter.Write(listItem["Title"]);
}

What I should be doing is this…

SPListItemCollection items = SPContext.Current.List.Items;

for(int i=0;i<100 && i<items.Count;i++) {
  SPListItem listItem = items[i];
  htmlWriter.Write(listItem["Title"]);
}

Invoke-SqlCommand

Development

For whatever reason, sometimes you just can’t upgrade the installed version of PowerShell on a server. In this case, I wanted to make use of the Invoke-SqlCommand command.

The most simple way around this limitation is to add a module to the server that includes a command that performs the same function.

See this post for a command that can be cut and pasted into a new PowerShell module file and easily accessed from your own scripts.

Simples!

Task Schedule: This task requires that the user account specified has Log on as batch job right

Server Administration

When creating a new Task within Task Scheduler and attempting to set the “When running the task, use the following user account” option, you may receive the following error message:

Task Schedule: This task requires that the user account specified has Log on as batch job rights

This can be resolved by ensuring the user account has the correct permissions on the server.

  1. Click the Start button.
  2. Within the Search field, type secpol and, when displayed, click Local Security Policy.
  3. Within the Local Security Policy window, expand the Local Policies node.
  4. Expand the User Rights Assignment node.
  5. In the right-hand panel, right-click Log on as a batch job and select the Properties option.
  6. In the Log on as batch job Properties window, click the Add User or Group… button and add the user or group you want to give access.
  7. Click the OK button to close the window.
  8. Click the OK button to close the Log on as batch job Properties window .

Extra: Task Scheduler Error and Success Constants

XSL and dumping XML

Development

When you’re attempting to construct an XSL template its always helpful to know the structure of the XML you’re transforming. When working with lists in SharePoint, this isn’t always obvious.

One quick solution is simply to dump the XML to the page so you can review it.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" />
<xsl:template match="/">
<pre>
<xsl:copy-of select="*"/>
</pre>
</xsl:template>
</xsl:stylesheet>

Simples!

Creating a SQL Server backup plan

Server Administration

This is something that is generally out of my area of responsibility but has to be done every now and then: creating and configuring a maintenance backup plan for a SQL database server.

This post from Kyle Laffoon outlines the steps needed to create a plan that executes full and differential backups, as well as looking after transaction logs and cleaning up after itself.

Create a maintenance backup plan in SQL Server 2008 R2 using the wizard

Bulks inserts and SSIS

Development

SQL Server Integration Services (SSIS) really isn’t my thing. I like to get my hands dirty with code. But every now and then (not often enough for me to remember anything of what I’ve previously done) I have to bite the bullet and open an SSIS package in Visual Studio.

Today has been one of those days.

The goal of the package was simple: to import the contents of two CSV files from a network share into two database tables on a SQL Server. After a day of struggling to reason why I was getting various “Access is denied” and “The file … does not exist” error messages, I finally discovered this blog post that helped to clear the mist.

Bingo! I now have a package with two Sequence Containers with green execution results.

The final SSIS package

Task Scheduler & PowerShell

Server Administration

To manage repeating tasks, its common for us (the team I work with 9-5) to automate things with a PowerShell script and then schedule it to repeat at specific times using a scheduled task.

When I create the task I use a specific set of parameters , which I always forget – so I’m noting them here for future reference.

PowerShell.exe -NoProfile -ExecutionPolicy ByPass -File C:\PowerShell\Get-SomethingNice.ps1