Author Archives: Dedra

Excel: Using Regional Settings to Localize Date with Time Formats

Excel date and time formats will automatically localize based on the user’s system setting – if the format is set to a specifier denoted by an asterisk in the cell format dialog.

To format a date with time, we’d typically set a custom format like “mm/dd/yyyy hh:mm:ss AM/PM”. But this format will not fully use the regional settings. If we want to automatically localize a date with time format, we have to do something different. I tried a lot of things but the following “trick” was the only way I found that worked for all regional settings including the AM/PM indicator.

First, add the following VBA to the Workbook_Open event. Column A contains the date values we want to localize. Cell D1’s NumberFormat is set the the default date format and cell E1 to the default time format.

Private Sub Workbook_Open()

    Range("D1").NumberFormat = "m/d/yyyy" 'set to default date format
    Range("E1").NumberFormat = "h:mm:ss AM/PM" 'set to default time format
    Columns("A:A").NumberFormatLocal = Range("D1").NumberFormatLocal + _
            " " + Range("E1").NumberFormatLocal

End Sub

Enter date/time data in column A. Column B contains the same date values as A and is set to the regional date format. I did this just to compare to our localized date/time to see if it is formatted correctly. You may want to create a regional time formatted column as well.

Column C shows the date/time custom formatted with “m/d/yyyy h:mm:ss AM/PM”.

Now, open Control Panel and change your systems regional language setting.

Close your woorkbook and re-open. Here’s the result for Sanskirt (India). Notice how the custom format of column C uses the localized m/d/yyyy separators but does not use the local AM/PM or the correct order for day followed by month:

Spanish (Columbia):

Yi (PRC):

Yoruba (Nigeria):

Russian (Russia):

English (United Kingdom)

Chinese (Traditional, Taiwan):

Linear Algebra Done Right 2nd Edition – Chapter 1 Exercises 5(b), 5(c), 5(d), 6, 7

Chapter 1

5.(b)Determine if U = {(x_1,x_2,x_3) \in \textbf{F}^3:x_1 + 2x_2 + 3x_3 = 4} is a subspace of \textbf{F}^3 .

Solution: Since 0 + 0x_2 + 0x_3 \neq 4, (0,0,0) \notin U. Thus U is not a subspace of \textbf{F}^3 .

5.(c)Determine if U = {(x_1,x_2,x_3) \in \textbf{F}^3:x_1x_2x_3 = 0} is a subspace of \textbf{F}^3 .

Solution: Since x_1x_2x_3 = 0 at least one of x_i = 0. Consider (0,1,1) and (1,0,1). Both are in U but (0,1,1) + (1,0,1) = (1,1,2) \notin U. Thus, U is not a subspace of \textbf{F}^3 .

5.(d)Determine if U = {(x_1,x_2,x_3) \in \textbf{F}^3:x_1 = 5x_3} is a subspace of \textbf{F}^3 .

Solution:

Clearly (0,0,0) \in U.

Consider (a,b,c) and (x,y,z) both in U.
Then (5c,b,c) + (5z,y,z) =  (5c+5z, b+y, c+z) = ( 5(c+z), (b+y), (c+z) ) \in U

Let (x,y,z) = (5z, y, z) \in U and a \in \textbf{F}
Then a(x,y,z) = (a5z, ay, az) = (5az, ay, az) \in U.

U is a subspace of \textbf{F}^3

6. Give an example U of \mathbb{R}^2 such that U is closed under addition and under taking additive inverses (meaning -u \in U whenever u \in U), but U is not a subspace of \mathbb{R}^2.

Solution 1:

Let U = \{ (x,y) \in \mathbb{R}^2 : x,y \text{ are rational } \}

U \neq  \emptyset since (0,0) \in U.

If (a,b), (c,d) \in U then (a+c, b+d) \in U since the sum of rationals is rational. Thus, U is closed under addition.

Also, -a, -b are are rational since a, b, -1 are rational and the product of rationals is rational. Then (-a,-b) \in U. So U is closed under taking additive inverses.

Now, let a \in \mathbb{R}, a \text{ is irrational } . Consider (1,1) \in U. Then a(1,1) = (a,a) \notin U since the product of an irrational and a non-zero rational is an irrational. Thus, U is not closed under scalar multiplication.

U is not a subspace of \mathbb{R}^2

Solution 2: This is the one in the solution manual.

Let U = \{ (x,y) \in \mathbb{R}^2 : x,y \in \mathbb{Z} \}

U \neq  \emptyset since (0,0) \in U.

Given (a,b) \in U and (c,d) \in U then a+c \in \mathbb{Z} and b+d \in \mathbb{Z} and (a+c,b+d) \in U.
Thus, U is closed under addition.

Also, -a, -b are integers since a, b are integers. Then (-a,-b) \in U.
So U is closed under taking additive inverses.

Consider (1,1) \in U and \frac{1}{2} \in \mathbb{R}.
Then \frac{1}{2} (1,1) = (\frac{1}{2},  \frac{1}{2}) \notin U.
Thus, U is not closed under scalar multiplication.

U is not a subspace of \mathbb{R}^2

7. Give an example of a nonempty subset U of \mathbb{R}^2 such that U is closed under scalar multiplication, but U is not a subspace of \mathbb{R}^2.

Solution:

Let U = \{ (x,y) \in \mathbb{R}^2 : \text{ if } x = 0, y = 0 \text{ otherwise } y \neq 0 \}

Just for fun, this is a different subset than the one found in the solution manual.

U \neq  \emptyset since (0,0) \in U.

Let (x,y) \in U and a \in \mathbb{R}. If a = 0 or (x,y) = (0,0), then a(x,y) = (0,0) otherwise a(x,y) = (ax, ay) : ay \neq 0. So a(x,y) \in U. Thus, U is closed under scalar multiplication.

Consider (10, 1) and (-5, -1). Both are in U. But (10, 1) + (-5, -1) = (5, 0) \notin U.

U is not a subspace of \mathbb{R}^2.

jQuery UI Autocomplete Widget Extension

jQuery UIMany of my personal and professional projects use the jQuery UI Autocomplete widget. Customers love being able to type a search term and have their choices limited to only those items in which they’re interested. The base widget, however, doesn’t provide all the functionality I need and ad hoc customizations have become tedious. So, I’ve decided to write an extension using the Widget Factory that brings together the features I use most often. My AutocompletePro widget is definitely a work in progress, but you can grab the latest version from GitHub.


Live Demo!

This is a jQuery UI widget and may not be suitable for some mobile devices. A jQuery mobile version will soon be posted to my development site!

Delete the current input text and start typing the title of one of your favorite jazz albums, perhaps “Jazz Samba”. You can also type in an artist’s name like Miles Davis or the year 1958. Then make a selection from the available recordings.


AutocompletePro adds several new options to the base Autocomplete:

  • autodrop: defines if the menu should open on focus
  • categoryfield: what field to use as the category
  • categorize: defines if the menu should be categorized
  • defaultvalue: sets the initial value
  • datasource: replaces the base source option
  • filter: sets a filter on the data source records
  • filtereddatasource: read only – the datasource that is being used by the widget
  • itemsshown: limits the number of hits returned
  • searchfields: array of fields names to be searched

And two new methods:

  • value: returns the selected item object
  • selectitem: selects an item given a set of elements

Demo Code GitHub

The datasource for this demo is structured as follows:

 
var data.items = [{ "category": "Traditional", "artist": "Benny Goodman", "label": "Live at Carnegie Hall 1938 [Live]", "year": "1950" },
  ...
{ "category": "Bebop", "artist": "Charlie Parker", "label": "Best of the Complete Savoy and Dial Studio Recordings [Compilation]", "year": "2002" }]

And the widget is created with these options:

$("#widgetbox").autocompletepro({
        autoFocus: true,
        itemsshown: 10,
        datasource: data.items,
        autodrop: true,
        searchfields: ["label", "artist", "year"],
        categorize: true,
        defaultvalue: [{ "artist": "Miles Davis", "label": "Kind of Blue" }],
        categoryfield: "category",
        filter: [{ "category": "Traditional" }, { "category": "Latin" }, { "category": "Cool" }, { "category": "Fusion" }, { "category": "Bebop" }]
});

The Basics on Using Oracle Data Provider for .NET

Recently I had to convert a classic ASP application that had an Oracle back-end to .NET 4.5. Since the types in System.Data.OracleClient were deprecated in .Net 4.0, Microsoft recommends that you use a third-party Oracle provider. I downloaded the latest Oracle Data Provider for .NET (ODP.NET).

My target system was 64 bit, so after unzipping the download, I ran the configuration batch file found at \ODP.NET_Managed121010\odp.net\managed\x64\configure.bat. Then I added a reference to the Oracle.ManagedDataAccess assembly.

I needed to retrieve data from the Oracle database three ways: a static select statement [13-26], a select with parameters [29-40] and a stored procedure with parameters [43-49]. For simplicity, I’m returning my records in a DataTable.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;

namespace TestODP.NET
{
    public class GetMyOracleData
    {
        //Basic select 
        public DataTable GetAccounts() 
        { 
         StringBuilder mySQL = new StringBuilder();
         mySQL.Append("SELECT DISTINCT rtrim(acct_name) as accountName, ");
         mySQL.Append("rtrim(s_addr) as address, ");
         mySQL.Append("rtrim(s_city) as city, ");
         mySQL.Append("rtrim(s_state_cd) as state, ");
         mySQL.Append("sales_id as salesmanID, ");
         mySQL.Append("FROM adb.t_acct ");
         mySQL.Append("WHERE acct_id in ('1','2','3')");

         return MyOracleData.Accounts(mySQL.ToString());

        }

        //Select with parameters
        public DataTable GetCustomers()
        {
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("SELECT DISTINCT rtrim(cust_name) as custName, ");
            mySQL.Append("FROM adb.t_cust ");
            mySQL.Append("where acct_id = :ACCTID ");
            mySQL.Append("AND cust_id = :CUSTID ");
            mySQL.Append("order by cust_name");

            return MyOracleData.Customers(mySQL.ToString(), "0", "1");

        }

        //Stored procedure with parameters
        public DataTable GetCustomerDates()
        {
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("adb.getcustomerdates");

            return MyOracleData.CustomerDates(mySQL.ToString(), "0", "01/01/2000", "1");
        }
    }
}

Now for the actual calls to the Oracle database. Each class is quite self-explanatory. The most interesting is the last example that has several input parameters and returns a record set in an output parameter.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Oracle.ManagedDataAccess;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace TestODP.NET
{
    public class MyOracleData
    {
        //Connection string of choice
        public static string ConnectionString()
        {
            return "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=myportnumber)))(CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;";
        }

       //Run a generic SQL select statement that returns a recordset
       public static DataTable Accounts(string mySQLSTR)
        {
            OracleConnection connection = new OracleConnection(ConnectionString());
            DataTable returnTable = new DataTable();
            try
            {
                connection.Open();
                using (OracleCommand command = new OracleCommand(mySQLSTR, connection))
                {
                    returnTable.Load(command.ExecuteReader());
                }
            }
            catch (Exception ex)
            {
                returnTable = null;
            }
            finally
            {
                connection.Dispose();
            }
            return returnTable;
        }

       //Run a SQL select with parameters
       public static DataTable Customers(string mySQLSTR, string myACCTID, string myCUSTID)
       {
           string connectionString = ConnectionString();
           OracleConnection connection = new OracleConnection(connectionString);
           OracleDataReader readerResults = null;
           DataTable returnTable = new DataTable();
           try
           {
               connection.Open();
               using (OracleCommand command1 = new OracleCommand(mySQLSTR, connection))
               {
                   command1.BindByName = true;
                   command1.Parameters.Add(new OracleParameter("ACCTID", myACCTID));
                   command1.Parameters.Add(new OracleParameter("CUSTID", myCUSTID));
                   readerResults = command1.ExecuteReader();
                   command1.Parameters.Clear();
                   returnTable.Load(readerResults);
               }
           }
           catch (Exception ex)
           {
               returnTable = null;
           }
           finally
           {
               if (readerResults != null) { readerResults.Dispose(); }
               connection.Dispose();
           }
           return returnTable;
       }

       //Call a stored procedure that returns a recordset
       public static DataTable CustomerDates(string mySQLSTR, string myACCTID, string myTARGETDATE, string myCUSTID)
       {
           string connectionString = ConnectionString();
           OracleConnection connection = new OracleConnection(connectionString);
           OracleParameter paramOutput = null;
           DataTable returnTable = new DataTable();
           try
           {
               connection.Open();
               using (OracleCommand command1 = new OracleCommand(mySQLSTR, connection))
               {
                   command1.BindByName = true;
                   command1.CommandType = CommandType.StoredProcedure;
                   command1.Parameters.Add(new OracleParameter("VARACCTID", myACCTID));
                   command1.Parameters.Add(new OracleParameter("VARSTARTDATE", myTARGETDATE));
                   command1.Parameters.Add(new OracleParameter("VARCUSTID", myCUSTID));
                   paramOutput = command1.Parameters.Add(new OracleParameter("IO_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output));
                   command1.ExecuteNonQuery();
                   returnTable.Load(((OracleRefCursor)paramOutput.Value).GetDataReader());
               }
           }
           catch (Exception ex)
           {
               returnTable = null;
           }
           finally
           {
               if (paramOutput != null) { paramOutput.Dispose(); }
               connection.Dispose();
           }
           return returnTable;
       }

    }
}

Finally, I added a small function to retrieve info on all the parameters for a given Oracle stored procedure – which really comes in handy when you don’t have access to the Oracle database.

        public static string StoredProcInfo(string SQLSTR)
        {
            //// This is a little utility that gets info about the parameters of an Oracle stored proc
            StringBuilder retString = new StringBuilder();
            OracleConnection connection = new OracleConnection(ConnectionString());
            try
            {
                connection.Open();
                using (OracleCommand spcommand = new OracleCommand(SQLSTR, connection))
                {
                    spcommand.CommandType = CommandType.StoredProcedure;
                    OracleCommandBuilder.DeriveParameters(spcommand);
                    retString.Append("Number of Parameter: " + spcommand.Parameters.Count.ToString() + "<br />");
                    for (int i = 0; i < spcommand.Parameters.Count; i++)
                    {
                        retString.Append(spcommand.Parameters[i].ParameterName.ToString());
                        retString.Append(" ");
                        retString.Append(spcommand.Parameters[i].Direction.ToString());
                        retString.Append(" ");
                        retString.Append(spcommand.Parameters[i].OracleDbType.ToString());
                        retString.Append("<br />");
                    }
                    spcommand.Parameters.Clear();
                }
            }
            catch (Exception ex) { retString.Append(ex.Message + "<br><br>" + ex.StackTrace); }
            finally { connection.Dispose(); }
            return retString.ToString();
        }

RESTful Web Services for Resource-Oriented Architectures (ROA) – Part 4 – Statelessness

The second constraint Dr. Fielding places on RESTful architectures is that of Statelessness. In a RESTful web service, “communication must be stateless in nature … such that each request from client to server must contain all of the information necessary to understand the request, and cannot take advantage of any stored context on the server. Session state is therefore kept entirely on the client.” 1

In a Resource Oriented Architecture, Richardson and Ruby define two types of state: application (or session) state which is the responsibility of the client and resource state which is held on the server.2 To illustrate the difference in the two types of state, consider the banking service found in our previous post RESTful Web Services for Resource-Oriented Architectures (ROA) – Part 2 – HATEOAS. Pending transactions are not kept in a session controlled by the server. The server does not wait for and is not expecting the client to proceed through the completion of the posting process in any particular order or timeline. Instead, pending transaction are resources stored on the server. It is up to the client to retrieve the transactions and then decide whether to post, edit or delete them. The client determines the next step he wants to take and when he will take it. The server responds with an appropriate answer – in the form of another resource – to the client’s requests. If the client tries to POST, PUT or DELETE an invalid resource, the server simply returns another resource indicating failure and containing links that can lead the client to a remedy.

Suppose our banking service returns the following resource when a client makes a GET request to “/bank/transferforms/dlwelch”. The response contains a list of all pending and posted transfer transactions for account dlwelch.

200 OK
Allow: GET, POST
Content-Type: application/xml; charset=utf-8
Location: "/bank/transferforms/dlwelch"

  <transferforms>
    <pendingforms>
      <form> 
        <id>1235</id>
        <link rel="self"  type="application/xml"
              uri="/bank/transferforms/dlwelch/pending/1235" />
      </form>
      <link rel="self"  type="application/xml"
            uri="/bank/transferforms/dlwelch/pending" />
    </pendingforms>
    <postedforms>
      <form> 
        <id>0001</id>
        <link rel="self"  type="application/xml"
              uri="/bank/transferforms/dlwelch/posted/0001" />
      </form>
      ...
      <form> 
        <id>1234</id>
        <link rel="self"  type="application/xml"
              uri="/bank/transferforms/dlwelch/posted/1234" />
      </form>
      <link rel="self"  type="application/xml"
            uri="/bank/transferforms/dlwelch/posted" />
    </postedforms>
    <link rel="self" type="application/xml" 
          uri="/bank/transferforms/dlwelch" />
    <link rel="up" type="application/xml" 
          uri="/bank/accounts/dlwelch" />
    <link rel="/bank/rel/home" type="application/xml" 
          uri="/bank" />
    <link rel="/bank/rel/create" type="application/xml" 
          uri="/bank/transferforms/dlwelch" />
  </transferforms>

As described in our previous article, the client can finish a pending transaction by submitting a POST request to the pending transaction’s URL. But if we tried to POST to “/bank/transferforms/dlwelch/pending/1234”, our banking service should return an error response, for example:

410 GONE
Content-Type: application/xml; charset=utf-8
Location: "/bank/transferforms/dlwelch/pending/1234"

<error>
  <link rel="/bank/rel/transfers" type="application/xml" 
       uri="/bank/transferforms/dlwelch" />
  <link rel="/bank/rel/accounts" type="application/xml" 
       uri="/bank/accounts/dlwelch" />
  <link rel="/bank/rel/customers/dlwelch/" type="application/xml" 
       uri="/bank/customers/dlwelch/" />
  <link rel="/bank/rel/home" type="application/xml" 
       uri="/bank" />
</error>

This response tells us the resource located at “/bank/transferforms/dlwelch/pending/1234” existed at one time but has been permanently moved or deleted.

All responses from a RESTful service – including error messages – are returned to the client as useful resource representations that guide the client to an acceptable state. A RESTful service, due to its stateless nature, never requires the client to be in a particular state in order to provide an appropriate, valid response to the client’s request. The service will not become confused or inoperable because the client does not follow a pre-defined process flow. And it will not re-post our transfer or re-submit an online payment – even when the user hits the back button.

“HTTP is an intrinsically stateless protocol, so when you write a web service you get statelessness by default. You have to do something to break it. The most common way to break statelessness is to use your framework’s version of HTTP sessions.” 2 Take, for example, ASP.NET session state. “ASP.NET session state enables you to store and retrieve values for a user as the user navigates ASP.NET pages in a Web application. HTTP is a stateless protocol. This means that a Web server treats each HTTP request for a page as an independent request. The server retains no knowledge of variable values that were used during previous requests. ASP.NET session state identifies requests from the same browser during a limited time window as a session, and provides a way to persist variable values for the duration of that session. By default, ASP.NET session state is enabled for all ASP.NET applications.” 5 ASP.NET purposely breaks the natural statelessness of HTTP. By retaining session identifiers and variables on the server, an ASP.NET web application can dictate the application state to the client. For an ASP.NET developer, the first thing to do when moving to a RESTful ROA is to abandon any ideas about sessions.

Instead of sessions and methods, start thinking about resources and representations. “A (RESTful) web service only needs to care about your application state when you’re actually making a request. The rest of the time it doesn’t even know you exist. This means that whenever a client makes a request, it must include all the application states the server will need to process the request.”2 If you get stuck trying to remove application state from the server, ask yourself how you would accomplish the same task using hard-copy documents. Our banking service works the same way as when we present a paper check at the bank teller window. We need to show a drivers license (authentication), a valid check made out to the bearer (authorization) and a deposit slip for our account (URI request). In return we will receive a deposit receipt or cash (resource representation) depending on the resource request we submitted.

The Jazz Artist web service discussed in previous posts is a stateless service. The client can make a request to any URI, in any order, without the server having knowledge of previous requests. The server considers each client request in isolation and then returns a RESTful response – even if the returned representation is an error report.

400 Bad Request
Date: Fri, 01 Nov 2013 19:27:21 GMT
Server: Microsoft-IIS/7.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Content-Type: application/xml; charset=utf-8
Location: http://dlwelchservices.com/jazz/badrequest?aspxerrorpath=/jazz/artists/125*
Cache-Control: no-cache
Content-Length: 503
<?xml version="1.0" encoding="utf-8"?>
<JazzArtists xmlns="http://schemas.dlwelch.com/JazzService" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Artists/>
<Links>
<link rel="/jazz/rel/home" href="http://dlwelchservices.com/jazz/artists" type="application/xml"/>
<link rel="/jazz/rel/search" href="http://dlwelchservices.com/jazz/artists/?firstname={firstname}&lastname={lastname}&grammys={grammys}" type="application/xml"/>
</Links>
</JazzArtists>

It is up to the client application to manage state and works it’s way through our service in a meaningful progression using the information provided by the response.


1. Fielding, Roy Thomas. Architectural Styles and the Design of Network-based Software Architectures. Doctoral dissertation, University of California, Irvine, 2000.

2. Richardson, Leonard and Ruby, Sam. RESTful Web Services. Sebastopol: O’Reilly Media, Inc., 2008. Ebook

3. Fielding, Roy Thomas. REST APIs must be hypertext-driven. Untangled – Musings of Roy T. Fielding, October 20, 2008

4. Fielding, Roy Thomas, et. al. RFC 2616 – Hypertext Transfer Protocol – HTTP/1.1. The Internet Society, June 1999

5. MSDN Library – ASP.NET Session State Overview – .Net 4

Vance & Hines Big Shots Long Baffle Replacement

Now that I have a sidecar on my big V-Twin, I have to do most maintenance and repairs myself. The latest job was replacing the standard Vance & Hines Big Shots Long baffles with V&H Quiet Baffles© P/N 21869.

Standard Quiet

First thing to do is remove the end caps by removing the hex head screws located on the interior side of the heat shields. Once the screw is remove, the end cap simply pulls out. Mine were a little difficult to get out but with the help of a flat, rubber jar opener I was able to pull them off without damage.


Next, I removed the heat shields by first removing the three hose clamps that hold the shields to the pipes. I wound up bending the clamps to the point that I had to buy some new ones. Fortunately they are just regular hose clamps found at any auto parts store. (The clamps I purchased were a little longer than the ones I replaced – which actually made the re-install quicker.)

The end of the heat shields surround the pipe so you have to twist the shield away from the engine and then slide it back approximately one foot before removing it. I found it easier to do once I removed the rider and passenger floorboards. Without a sidecar, you may not need to do so.


Next, I removed the baffles by first removing the retaining screw on the inside of the pipes.

I then used a pair of long, needle nose pliers to grab a fin on the baffle and pulled it out of the pipe. Mine were stuck so I used a rubber mallet to whack the pipe a few times and with a little twist, the baffle slipped right out.


Before installing the new baffles, I wound some thin wire around the end of fiberglass cloth to prevent it from bunching. Then I installed the baffle making sure to line up the holes in the baffle and pipe and replaced the retaining screws.


I re-installed the heat shields and end caps…and that was it! When I started it, I felt a little more back pressure, so I took it by the shop the next morning. My bike didn’t need a re-tune, but be sure to have that checked out when you change your baffles.

The Quiet Baffles deepened the tone of my exhaust and reduced the volume at idle. But they did not significantly reduce the volume once I rolled on the throttle.

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.