Category Archives: SharePoint

SharePoint 2010 – Exposing SQL Data Using BCS, External Content Types and External Lists

SarePoint developers are frequently asked to expose SQL data as a SharePoint list, but doing so in MOSS 2007 was a difficult chore. Using the old Business Data Catalog (BDC) required the tedious creation of XML files and cumbersome work in Central Admin. Fortunately, SharePoint 2010’s Business Connectivity Services (BCS) has made the process almost painless. This article gives you step-by-step instructions for creating, reading, updating and deleting SQL table rows using a SharePoint external list.

Suppose you want to display and edit this SQL table’s data as a SharePoint list.

First, we need to set up an Active Directory user and group that will handle the permissions for connecting to the SQL database. Let’s create an AD service account with a non-expiring password that cannot be changed. Then we’ll create an AD group whose members are the new service account we just created and the other users and groups who are to be granted access to the SQL table through the SP list. BCSUser is the service account and BCSUsers, with an “s”, is the group.

We also need to give the service account permissions to the SQL database.

Next, open central admin and make sure the Business Connectivity Service is started.

Now we’ll create a Secure Store Target Application so we can map the members of the BCSUsers group to the credentials of the BCSUser whenever they access the SQL table through our soon to be created external list. Go to Central Admin -> Manage Service Applications -> Secure Store Service -> New



Give your SSTA an ID, display name and a contact email address. Since we want to map an AD group to the BCSUser, select Group as the Target Application Type. Click Next.

The next page allows you to configure the fields that may be required when supplying credentials to the external data source. We’ll just pass through the user’s Window’s credentials so the defaults are okay. Click Next.

Now enter at least one Target Application Administrator and the AD group we want to map to the BCSUser credentials.

Next, we need to tell the SSTA which user’s credentials we are going to use for mapping. Choose Set Credentials from the Target Application’s drop down.

Enter the BCSUser’s credentials and click OK.

We’ve completed our preliminary work in Central Admin and will now use SharePoint Designer to create an External Content Type. (You can also use Visual Studio to create External Content Types)

Open your target site in SP designer and create a new External Content Type.

Name your External Content Type and then “Click to discover external data sources and define operations”.

Add a SQL Server type connection.

Specify the SQL Server and the database name. Select “Connect with Impersonated Windows Identity” and enter the name of your SSTA.

Enter the BCSUser credentials to connect.

The database is now shown in an explorer view.

Right click on the table we want to use as a list and select “Create All Operations”. This will add all operations required to create, read, update and delete items from our list.

The only configuration you must do is to specify a unique identifier. The ID field of our SQL table will work nicely. Check the box to map it to the identifier and then click Finish.


Save the External Content Type.


Now we need to set or confirm permissions for the new External Content type in Central Admin. Go to Manage Service Applications -> Business Data Connectivity Service and select Set Permissions from your new content type’s drop down.



All authenticated users are given the minimum permissions for using the new content type. At least one user is required to have all permissions. Here, I’ve set that for the administrator. (not shown)

Finally, we can create an External List based on the new External Content Type.




We have our External List! Let’s try it out.

Confirm that the list is editable.


Try logging on as different users from the BCSUsers group to check their ability to see the list.

Now try a user that is not in the BCSUsers group.


In this post, we’ve used the most common BCS method for surfacing a SQL table in SharePoint 2010. As you probably gathered from working through these steps, SP 2010 gives us several options for authenticating users, passing credentials and connecting to a SQL database. I hope this example helps you to further explore the BCS and find the configuration that works best in your environment.

SMTP setup for CloudShare Sharepoint Foundation 2010 SP1 W/ Dev Tools Environment

As a developer, server administration is definitely not my area of expertise. Nonetheless, this post details how I set up SMTP and configured SharePoint’s out going emails on a CloudShare Sharepoint Foundation 2010 SP1 (Windows Server 2008 R2) with Developer Tools VM environment.

First, if you don’t already have one, get a gmail account. Then, start Server Manager.

Start Server Manager

Select Features, then SMTP Server and click Add Required Role Services.

This takes you back to the Add Features Wizard with SMTP Server checked. Click Next.

Click Next again.

Make sure all the IIS Management Tools are installed or checked to be installed. Then click Next followed by Install and Close.

Now open IIS 6.0 Manager. (Use IIS 6 Manager even though 7 is also installed.)

Right click the SMTP Virtual Server and select Properties.

Enable logging on the General tab.

Go to the Access tab and Authentication. Check all the options.

Go to the Delivery tab and select Outbound Security.

Check Basic Authentications, TLS and enter your gmail account credentials.

Click Okay and then select Outbound Connections. Change the TCP port to 587 for gmail.

Say Okay and click the Advanced button. Enter your CloudShare environment’s domain (computer) name and smtp.gmail.com as the Smart host.

Go back to the Access tab and select Connections. Click Only the List Below and Add.

Check Single Computer, click DNS Lookup, enter the environment’s domain name and click Okay.

The IP address will be populated. Click Okay twice.

Now, do the same thing for Relay on the Access tab. It is very important to restrict the computers that can relay! Be sure “Allow all computers…” is NOT checked and click Okay.

Next, check the security tab. I did not have to make any changes from those already specified:

Start Server Manager

Click Okay to close the properties box and then close the IIS 6.0 Manager.

Now, Open Services.

Right click on Simple Mail Transfer Protocol and select Properties.

Make the Start Up Type Automatic and click Okay. Close Services.

Now, go to Central Admin and System Settings.

Then Configure Outgoing Email Setting.

Enter your server name, gmail account and click Okay.

Finally, send yourself an alert (or other email) from your SharePoint site. You will receive an email from Google similar to this:

Someone recently tried to use an application to sign in to your Google Account – mygmail@gmail.com. We prevented the sign-in attempt in case this was a hijacker trying to access your account. Please review the details of the sign-in attempt:

Thursday, January 17, 2013 6:38:11 PM UTC IP Address: 11.111.111.111 (computername)
Location: Somewhere, USA

If you do not recognize this sign-in attempt, someone else might be trying to access your account. You should sign in to your account and reset your password immediately.

If this was you, and you want to give this application access to your account, complete the troubleshooting steps listed at http://support.google.com/mail?p=client_login

Note: This email address cannot accept replies.

Sincerely,
The Google Accounts Team

Just follow the instructions in the troubleshooting link and send another email from your SharePoint site. Gmail should relay the email and you’ll receive the task or alert in the inbox specified for the SharePoint user.

JavaScript, jQuery and Ajax for reporting on SharePoint lists

In this post, I will show you how to query two SharePoint lists using jQuery and Ajax. We will then combine the results into a summary report. I originally created this example for SP 2007 with the requirement it be completed using only client side capabilities. In addition, the customer wanted a graphical representation of the data and a drill down to details for each record, without the use of custom web parts.

The first list contains master project data.

The second list is a project status log which is populated by a workflow on the Projects List. Each time the project status is changed, an item is created in this project status list.

Now we’ll create our report using client-side scripting. I used SharePoint Designer 2007 and created a new web page at the root of the site. But you can also use a Content Editor Web Part to hold your script.

First, add a <div> to your page that will display a loading image and notification while your Ajax runs. We’ll hide this <div> once it’s done.

<div id="wait">
<p align="center"><img src="GEARS_AN.GIF" alt="Please wait..." />
<br/>Generating report..
</p>
</div>

Then add another <div> where you will display your results. I wrapped mine in a <table> so I can easily add rows with more information later.

<table class="ms-vb" cellpadding="1" border="0">
<tr><td>
<div id="listLinks"> </div>
</td></tr> 
</table> 

Now for the script. Define two JavaScript objects. One for the project list items and one for the statuses. Add an .ajaxStop function which will run when all Ajax calls complete. The .unbind function prevents it from running again if a subsequent Ajax call is made after the document loads. Finally, define what should happen when the document is ready.

<script type="text/javascript">

 var myProjArray = new Object();
 var myStatusArray = new Object();

 $(document).ajaxStop(function() {
  $(this).unbind("ajaxStop");
  createprojectstable();
 });

 $(document).ready(function() {
  getProjects();
 });

When the document is ready getProjects() runs. Since CAML joins are only supported in SharePoint 2010+, I have to make an extra Ajax call for each project to get the status items. A single call could have been made to return all status items rather than making a call for each project returned by the first. However, that list may have been much larger than needed if we were to restrict the projects returned in the first CAML query by using a WHERE clause. Your particular circumstance will dictate which approach to take.

//****************************************************************************************
//get projects from the Projects List
function getProjects(){

 var thisurl = window.location.href;
 var i = thisurl.lastIndexOf("/");
 var asmxlocation = thisurl.substr(0,i)
 asmxlocation = asmxlocation + "/_vti_bin/lists.asmx";


 var soapEnv = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
     <soapenv:Body> \
     <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
      <listName>Projects List</listName> \
      <viewFields> <ViewFields> \
        <FieldRef Name='ID' /><FieldRef Name='Title' /> <FieldRef Name='ProjStatus' /> <FieldRef Name='Created' /> <FieldRef Name='Team' /> \
      </ViewFields> </viewFields> \
      <rowLimit>2000</rowLimit> \
      <query> <Query> \
        <OrderBy><FieldRef Name='Team' Ascending='TRUE' /><FieldRef Name='Title' Ascending='TRUE' /></OrderBy> \
      </Query> </query> \
     </GetListItems> \
     </soapenv:Body> \
     </soapenv:Envelope>";

$.ajax({
url: asmxlocation,
type: "POST",
dataType: "xml",
data: soapEnv,
async: true,
complete: processResult,
contentType: "text/xml; charset=\"utf-8\""
});

}

//**************************************************************************************** 
//process results from projects query
function processResult(xData, status) {

$(xData.responseXML).find("z\\:row").each(function() { 

myProjArray[$(this).attr("ows_ID")] = [$(this).attr("ows_Title"),$(this).attr("ows_ProjStatus"),$(this).attr("ows_Created"),$(this).attr("ows_Team") ]; 

//CAML Joins are only available in SP 2010+ so we got to loop
getProjectStatus($(this).attr("ows_ID")); 

});
} 

//****************************************************************************************
//get status records for a specific project
function getProjectStatus(ProjID){

var thisurl = window.location.href;
var i = thisurl.lastIndexOf("/");
var asmxlocation = thisurl.substr(0,i)
asmxlocation = asmxlocation + "/_vti_bin/lists.asmx";


var soapEnvZ = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
    <soapenv:Body> \
    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
      <listName>Project Status Log</listName> \
      <viewFields> <ViewFields> \
         <FieldRef Name='ID' /> <FieldRef Name='ProjectID' /> <FieldRef Name='Status' /> <FieldRef Name='Created' /> \
      </ViewFields> </viewFields> \
      <rowLimit>1000</rowLimit> \
      <query> <Query> \
        <Where> <Eq> <FieldRef Name='ProjectID' /> <Value Type='Counter'>" + ProjID + "</Value> </Eq> </Where> \
        <OrderBy><FieldRef Name='Created' Ascending='TRUE' /></OrderBy> \
      </Query> </query> \
    </GetListItems> \
    </soapenv:Body> \
    </soapenv:Envelope>"; 

$.ajax({
url: asmxlocation,
type: "POST",
dataType: "xml",
async: true,
data: soapEnvZ,
complete: processResultZ,
contentType: "text/xml; charset=\"utf-8\""
});
}

//****************************************************************************************
//process results from the status query
function processResultZ(xData, status) {
var statusarray = new Array();
var arraycounter = 0
var projID;

$(xData.responseXML).find("z\\:row").each(function() { 
   statusarray[arraycounter++] = [$(this).attr("ows_Status"),$(this).attr("ows_Created")];
   projID = parseInt($(this).attr("ows_ProjectID"));
   myStatusArray[projID] = statusarray;
}); 

}

Now that we have the projects and statuses objects with a common property, the ID of the project, we can display the data any way we wish. createprojectstable() runs when the Ajax calls have completed and creates the HTML and JavaScript for the reports. I created a horizontal bar representing the length of time in each status for each project. There is also a drop down text area of the status details for each project. This is just another table row that is hidden and made visible by clicking the table row containing the graph. Click on the different elements below to see a live demo.

 

Here’s the interesting parts of creating the report – looping through the properties of the project and statuses objects to build the output HTML. (Note that, even though they are named “array”, they are actually objects.) And finally, we’ll hide the loading image and notification <div>.

function createprojectstable(){

var outHtml = "..."

//create report headings

for (prop in myProjArray) { 
  if (!myProjArray.hasOwnProperty(prop)) { 
    continue; //The current property is not a direct property of p, so goto the next one. i.e. it is not a poperty we created but a built-in JavaScript     property 
  } 

  //create HTML for each project

  if (myStatusArray[prop] !== undefined){
    //and now create the status details for the project
  }
  outHtml += "..."
  $("#listLinks").append(outHtml); 
  $("#wait").hide(); 

  //create report footers and initiate any other report options
}

You can download the source of a working example. This one reads XML files instead of SharePoint lists but is essentially the same. I’ve only tested the code in IE 8 and Firefox 5 but it would probably be quick work to make it cross-browser compatible.

XSLT and XPath 1.0 Date Arithmetic Templates

SharePoint 2007 and 2010 only support XPath 1.0 which lacks date arithmetic functions.1 To manipulate dates in your DVWP or XLV in SharePoint Designer, you can create your own XSL templates. Great templates are already available like those found in Andy Lewis’ post Filtering and Formatting with Date Values. However, none of the templates I found were a perfect fit for my requirements. So I created my own.

These templates use seconds since the Epoch and are leap year accurate. They are similar to VB.NET’s DateDiff and DateAdd functions and give the same or more precise results. You can try a working example and download the source files from my development examples site.

To use in SharePoint 2007 and 2010, import the date_arithmetic.xsl style sheet.

<XSL><xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
<xsl:import href="Styles/date_arithmetic.xsl" />
<xsl:output method="html" indent="no"/>

Then call the templates using a date formatted as yyyymmdd. This example is for SP 2007 which uses ISO date format (YYYY-MM-DDTHH:MM:SSZ) by default.

<xsl:template name="dvt_1.rowview">
<tr>
...
<td class="ms-vb">
<xsl:call-template name="DateAdd" >
<xsl:with-param name="yyyymmddDate" select="translate(substring-before(@Created,'T'),'-','')" />
<xsl:with-param name="dateInterval" select="'d'" />
<xsl:with-param name="numberToAdd" select="-120" />
</xsl:call-template>
</td>

</tr>
</xsl:template>

Resulting in:
Transformation Results

And this is an example for an XLV in SP 2010:

...
<xsl:variable name="created">
<xsl:apply-templates select="." mode="PrintFieldWithECB">
<xsl:with-param name="thisNode" select="$thisNode" />
</xsl:apply-templates>
</xsl:variable>
<xsl:variable name="thedate">
<xsl:call-template name="DateAdd">
<xsl:with-param name="yyyymmddDate" select="ddwrt:FormatDateTime(substring-before($created,' '),1033,'yyyyMMdd')" />
<xsl:with-param name="dateInterval" select="'d'" />
<xsl:with-param name="numberToAdd" select="-121" />
</xsl:call-template>
</xsl:variable>
<xsl:value-of select="$thedate" />
...

Resulting in:
2010Transformation Results
The top webpart is a DVWP where -120 is added to the create date. The lower is an XLV with -121 added to the create date.

The templates are not comprehensive and, as of yet, do not support time. But I hope they will give you a head start on your way to creating your own date templates for SP 2007/2010.


1. Recently, Marc D. Anderson posted an article about an undocumented ddwrt function – DateTimeTick – that you can use to find the difference between dates: Calculate Days between Two SharePoint List Dates in XSL Using ddwrt:DateTimeTick