Tuesday, 16 December 2014

Use InfoPath 2010 to Query an Oracle Database via a Custom Web Service in SharePoint 2010

Wow that's a long heading...  Let me explain how this is done, and why this path was chosen out of all the options.


Still to come...


There's quite a few large steps to get this done, so I'll split it into the following sections, ensuring we test along the way to make sure we haven't missed a step:

  • Install & Configure Oracle Client on Web Server
  • Create custom Web Service in Visual Studio
  • Deploy Web Service to IIS
  • Build InfoPath Form
  • Configure InfoPath Services
  • Deploy InfoPath Form to SharePoint Environment

Install & Configure Oracle Client on Web Server

  • Ask your Oracle DBA what version of Oracle the database is running on.
  • Navigate here and download the corresponding Client (for this excercise get the 32-bit version if its an option): http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
  • Copy to the IIS Server (Web Front End)
  • Extract and install, here's the settings I used for the install
    • Install DB Software Only
    • Single Instance
    • Personal Edition
    • C:\Oracle
  • Once installed, you'll need to update (or if they don't exist, create) files in the following location: C:\Oracle\product\xx.x.x\[DBname]\NETWORK\ADMIN\
    • First create a SQLNET.ORA file (in notepad) and input the following two lines:

    • Then create a TNSNAMES.ORA file.  In this file you create the connection string that the Web Service uses to connect to the Database.  Here's an example of a Connection string, however, your Oracle DBA should be able to help with this too:                
          DBNAME =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521)))

Create custom Web Service in Visual Studio

Now there is a way to create a Web Service that can be deployed to SharePoint, but as soon as you add the Oracle complexity it requires many more steps.  So in this case, I've built a Web Service that can be deployed to a separate IIS Site, and consumed from inside sharepoint.

You 'should' build the Web Service in WCF (because ASP.NET is a dying breed), but I also did not go down that path because I was having troubles with it.

  • Open Visual Studio (I'm using 2010):  New > Project > Visual C# > Web > ASP.NET Empty Web Application
  • right-click the Web Application inside Solution Explorer and select:  Add > New Item > Web Service.
  • Right click the Web Applicaiton again in Solution Explorer and:  Add Reference... > Oracle.DataAccess
  • Right click Oracle.DataAccess in Solution Explorer (Under References Folder) and click properties.  Change the 'Copy Local' property to TRUE
  • inside your Web Service Code, input the following

        [WebMethod(Description="Used to take 1 value and query an Oracle Database for results, then output to CSV")]        public string QueryHDS(String queryvalue)        {            try            {            //Set DB Connection to TNSNames.ORA DataSource            string oradb = "Data Source=D[BName in TNS File];User Id=[username];Password=[password];"; 
            OracleDataReader dr;
                using (OracleConnection conn = new OracleConnection(oradb))                {                    conn.Open();                    using (OracleCommand cmd = new OracleCommand())                    {                        //Grab Values                        cmd.Connection = conn;                        cmd.CommandText = "SELECT * FROM TABLE SOMEWHERE WHERE COLUMN = "+ queryvalue
                        cmd.CommandType = CommandType.Text; 

                        //RUN QUERY!                        dr = cmd.ExecuteReader();
                    //Write data to File                    using (System.IO.StreamWriter file = new System.IO.StreamWriter(fileLocation))                    {                        while (dr.Read())                        {                            file.Write(dr[0].ToString() + ", " + dr[1].ToString() + ", " + dr[2].ToString();                            file.Write(file.NewLine);                                                    }
                        //Close reader when finished.                        dr.Close();                    }                    conn.Close();                }                                return fileLocation;            }            //If anything fails, catch and return error            catch(Exception e)            {                return e.ToString();            }        }    }

  • Build Solution
  • Run Solution and test.  All should be well.  customise as need be.

Deploy Web Service to IIS

  • Go to IIS and Create new Web blank Web Site
  • in IIS Create new Application Pool
  • Right-click Application Pool > Advanced Settings:  set Enable 32-bit applications to TRUE.  make sure it's running in .NET Framework v4.0.  Also set the identity to a real user account, not just 'ApplicationPoolIdentity'

  • When you are happy with your Web Service, it's time to deploy it for realz to IIS.
  • Right-click Solution and Publish > To File System > Desktop.
  • Copy the Folder to the root folder of the blank web site you just made.
  • Go back to IIS, hit refresh on the blank web site and right click the folder you just copied in IIS > Convert to Application
  • Select the App Pool you just created, and Connect as a specific user with enough access.
  • You should now be able to browse to the location of the .asmx file.  
  • TEST

Build InfoPath Form

  • Create a new InfoPath Form with two fields, one to query the DB Table, and one to recieve the result (success/failure).  Also create a button to initiate the Data Connection
  • Create Data Connection to Receive from a SOAP Web Service.  Don't auto retrieve values
  • On the form's submit button:  Using Rules, update all of the Data Connections Query value.  Then Query the Web Service, then Return the value to the second field you created
  • Save Data Connection as Data Connection File (UDCX) on the SharePoint Site that the form is being published to.

Configure InfoPath Services

  • Navigate to Central Admin > Infopath Services
  • Allow Cross Domain
  • Allow Auth to Data Sources
  • Set Data Connection Timeouts to 300000 and 300000 respectively.  This allows InfoPath to wait for those long queries to run and return (if you have a slow DB like me!)

Deploy InfoPath Form to SharePoint Environment

  • Publish InfoPath to sharepoint.  
  • ensure it opens up as a browser enabled form
  • test

Monday, 10 November 2014

Create Appointment in Nintex Workflow - Finding the right certificate

Error: "The root of the certificate chain is not a trusted root authority"

this blog has a very good explanation of why you are receiving the issue: http://blogs.perficient.com/microsoft/2011/03/how-to-create-an-outlook-appointment-using-nintex-workflow-2010/

However, the solution for adding the correct Root Certificate was a bit sloppy in my opinion.  As the script below adds ALL the certificates (which in my case was about 100 of them, very messy!):
foreach ($cert in (Get-ChildItem cert:LocalMachineRoot)) { if (!$cert.HasPrivateKey) {New-SPTrustedRootAuthority -Name $cert.Thumbprint -Certificate $cert } }
When you only need to add one.  The one it's referencing.  It should be referencing your Outlook Web Access URL (Webmail).  You know, the URL you input in the Appointment Configuration Settings.

If that's the case, here's how to download the right certificate and keep a nice clean Certificate List in SharePoint:

  • navigate to your Webmail address
  • click the Lock icon next to your HTTPS url
  • View the Certificate (different ways in different browsers)
  • Click the Details Tab
  • Click Copy To File
  • Select DER encoded Binary X.509
  • Choose a filename and save.
  • Now you've got the right certificate, upload it to Central Admin via Security > Manage Trust

Re-run your workflow and revel in the glory of success.

Monday, 8 September 2014

Mini-ITX Build

I was going to build this bad boy, but I decided to buy a road bicycle instead :S

You can't have everything!  But I thought I'd throw this list up as a reference of price changes and if anyone else is looking for a build that I've double (triple) checked will definitely fit in the box provided.

This kit will fit 1 x 3.5" HDD and 1 x 2.5" HDD very comfortably.  You can fit 2 of each, the problem being that you have to start suffocating the box from air-intake, increasing heat considerably from all reports.

$49 - Cooler Master Elite 110
$171 - EVGA 02G-P4-3753-KR G-SYNC Support GeForce GTX 750 Ti Superclocked 2GB 128-Bit GDDR5 PCI Express 3.0 Video Card
$129 - Corsair RM 450
$104 - 8G Kit (4Gx2) DDR3 G.skill 2133
$164 - Gigabyte Z97N-WiFi
$100-$130 - CPU Cooling
$267 -  i5-4690K

TOTAL: $984-$1014

Prices based off msy.com.au and cpl.com.au on 15/08/2014

Friday, 5 September 2014

How Do I Create a New User in a SQL Azure Database?

Had to look at a few different KB articles for this one so thought I'd throw all the steps together in one quick blog.

First you need to create a login, to do this, connect to the master database and run the following query:

  • CREATE LOGIN login1 WITH password='SuperSecretPassword';

Then connect to the Database you wish to give access to, you need to create the user, then supply the access.  Run the following scripts:

  • CREATE USER login1User FROM LOGIN login1;
  • EXEC sp_addrolemember 'db_owner', 'login1User';

Push & Pull data to SQL via SharePoint Online and BCS

Ever wondered what sort of SQL data connections are possible in SharePoint Online?
Ever wondered of the limitations when trying this type of setup?

If so, read on...  This tutorial will go over all the steps to setup everything from scratch.  Every step is important, you miss one, it won't work.

  • SharePoint Online Plan 2 (you cannot achieve this with the basic version of SharePoint online as you require access to the Admin panel to setup Secure Store access permissions and BCS permissions.
  • A windows azure account (which you can get a free 30 day trial as long as you're willing to provide your credit card.
  • SharePoint Designer 2013 installed locally (required to create the final connection to the azure DB.

Please note, you'll make this a hell of a lot easier if all these accounts are setup using the same office logon.  Otherwise you'll be logging in and out like a madman.


Step 1 - Create SQL Database: 
Log into Azures Management portal (https://manage.windowsazure.com/)

From here, Quick Create an SQL Database.  Azure will ask if you want to create a new SQL server, say yes.  In a minute or two your server and DB should be created.  Note down the server name for use later (The Fully Qualified Domain Name:  servername.database.windows.net) as well as the DB name.

Once the Database has been created click 'Design your SQL Database', log in, and create a table and all the columns you want

Now your DB and table have been created, it's time to connect them up to SharePoint.

Step 2 - Add BCS Permissions:
log into your SharePoint Online admin panel (https://[companyname]-admin.sharepoint.com/).  Click on the BCS Tab.

Click 'Manage BDC Models and External Content Types'

In here, 'click Set Metadata Store Permissions', add the office365 account you want to have access to manage the data connection to SQL.  I just used the account I was logged in as, and ticked all the boxes for access.

Step 3 - Create Secure Store:  
While in the SharePoint Admin Portal, click on the 'Secure Store' tab.  Here's where we create the access token to connect to the database.  Click 'New'. 

Add a unique name for the Target Application ID, this can be anything, but note it down as it will be required later.  Click OK.  You have just created the secure store token, but you still need to add the username and password to it.

Check the box and click Set Permissions.  In these text boxes, enter the username and password you created earlier to connect to the SQL Database.  Click Save.

Step 4 - Create External Content Type: 
Ppen up SharePoint Designer and connect to your SP Online site (https://companyid.sharepoint.com).  Don't forget the S in https..

Click 'External Content Types'.  Click 'New External Content Type' in the ribbon.

Click connect to external data sources.  Choose SQL server.  Type in the server name and database you recorded in step 1.  Choose the last radio button and enter your secure store application id from earlier.  and then enter your SP Online username and password.  You should now see the table and columns from the database.  Select the table and click Save.

Step 5 - Create External List: 
Open up your SharePoint Online site and click settings > add an app > external list.  Choose the external content type you just created.

Step 6 - Done!: 
You should now be able to add and remove items in the external list, which should automatically push those changes to your SQL Database.  Cool as ice right!!??

  • http://office.microsoft.com/en-au/office365-sharepoint-online-enterprise-help/make-an-external-list-from-a-sql-azure-table-with-business-connectivity-services-and-secure-store-HA102933008.aspx#_Toc350352764 
  • http://michaelwasham.com/2013/03/16/office-365-access-denied-by-business-data-connectivity-connecting-to-sql-server-azure/ 

Friday, 15 August 2014

SharePoint Online - List View Threshold Workaround

Ever had this issue on SharePoint Online?
"This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator." 
"To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold."
Well I have, and it sucks.

My particular issue was that someone had uploaded 120,000 files to a document library, which they then wanted to add metadata to, however, being a bulk upload, they weren't adding it as they were going, so in turn, any sorting/filtering of such a large bank of files let to the famous error above.

Of course you can't bypass the LVT (list view threshold), but as we all (should!) know, is that once you have added metadata, you can do things to avoid it.

My solution to this issue is not quick, but it worked.  I created a view showing only the File Name and the ID column & Sorted by ID (you are allowed to do this).  I then Exported the data and added to the spreadsheet containing all the metadata that needed to be added.

In sorting by ID, we were able to add the metadata 5000 rows at a time.  after which, we could Index the columns properly and create views that filter based on those indexed columns.

I'll admit, Each LVT issue is different, and not all are solvable.  However, if you've got an issue let me know and I'll see if I can help!

Wednesday, 30 July 2014

The Perfect Responsive FREE eCommerce Solution - Revisited

This is a follow up to my previous post on the perfect responsive ecommerce solution, which you can find here: http://thebaretta.blogspot.com.au/2014/06/the-perfect-responsive-free-ecommerce.html

You can add the Code below to fix up any slight issues that I've found in the Responsive Solution from the previous post.

Fixes include:

  • Shopping Cart goes off the screen on Mobile
  • Add padding around Website Logo
  • Products span full width of site instead of being limited by the standard code.



mark { background-color: transparent; font-weight: bold;}

/* START Make the cart table responsive */
/* http://css-tricks.com/responsive-data-tables/ */

@media only screen and (max-width: 480px) {

/* Force table to not be like tables anymore */
.woocommerce-page table.shop_table,
.woocommerce-page table.shop_table thead,
.woocommerce-page table.shop_table tbody,
.woocommerce-page table.shop_table th,
.woocommerce-page table.shop_table td,
.woocommerce-page table.shop_table tr {
display: block;

/* Hide table headers (but not display: none;, for accessibility) */
.woocommerce-page table.shop_table thead tr {
position: absolute;
top: -9999px;
left: -9999px;

.woocommerce-page table.shop_table tr {
/*border: 1px solid #d2d3d3; */

.woocommerce-page table.shop_table td {
/* Behave  like a "row" */
border: 1px solid #d2d3d3;
position: relative;
padding-left: 30% !important;

.woocommerce-page table.shop_table {
border: none;

.woocommerce-page table.shop_table td.product-spacer {
border-color: #FFF;
height: 10px;

.woocommerce-page table.shop_table td:before {
/* Now like a table header */
position: absolute;
/* Top/left values mimic padding */
top: 6px;
left: 6px;
width: 45%;
padding-right: 10px;
white-space: nowrap;

Label the data
.woocommerce-page table.shop_table td.product-remove:before {
content: "DELETE";

.woocommerce-page table.shop_table td.product-thumbnail:before {
content: "IMAGE";

.woocommerce-page table.shop_table td.product-name:before {
content: "PRODUCT";

.woocommerce-page table.shop_table td.product-price:before {
content: "PRICE";

.woocommerce-page table.shop_table td.product-quantity:before {
content: "QUANTITY";

.woocommerce-page table.shop_table td.product-subtotal:before {
content: "SUBTOTAL";

.woocommerce-page table.shop_table td.product-total:before {
content: "TOTAL";

.woocommerce .quantity,
.woocommerce #content .quantity,
.woocommerce-page .quantity,
.woocommerce-page #content .quantity {
margin: 0;

.woocommerce-page table.cart td.actions,
.woocommerce-page #content table.cart td.actions {
text-align: left;
padding-left: 6px !important;

.woocommerce-page table.cart td.actions .button.alt,
.woocommerce-page #content table.cart td.actions .button.alt {
float: left;
margin-top: 10px;

.woocommerce-page table.cart td.actions div,
.woocommerce-page #content table.cart td.actions div,
.woocommerce-page table.cart td.actions input,
.woocommerce-page #content table.cart td.actions input {
margin-bottom: 10px;

.woocommerce-page .cart-collaterals .cart_totals {
float: left;
width: 100%;
text-align: left;

.woocommerce-page .cart-collaterals .cart_totals th,
.woocommerce-page .cart-collaterals .cart_totals td {
border:0 !important;

.woocommerce-page .cart-collaterals .cart_totals table tr.cart-subtotal td,
.woocommerce-page .cart-collaterals .cart_totals table tr.shipping td,
.woocommerce-page .cart-collaterals .cart_totals table tr.total td {
padding-left: 6px !important;

.woocommerce-page table.shop_table tr.cart-subtotal td,
.woocommerce-page table.shop_table tr.shipping td,
.woocommerce-page table.shop_table tr.total td,
.woocommerce-page table.shop_table.order_details tfoot th,
.woocommerce-page table.shop_table.order_details tfoot td {
padding-left: 6px !important;
border:0 !important;

.woocommerce-page table.shop_table tbody {
padding-top: 10px;

.woocommerce .col2-set .col-1,
.woocommerce-page .col2-set .col-1,
.woocommerce .col2-set .col-2,
.woocommerce-page .col2-set .col-2,
.woocommerce form .form-row-first,
.woocommerce form .form-row-last,
.woocommerce-page form .form-row-first,
.woocommerce-page form .form-row-last {
float: none;
width: 100%;

.woocommerce .order_details ul,
.woocommerce-page .order_details ul,
.woocommerce .order_details,
.woocommerce-page .order_details {

.woocommerce .order_details li,
.woocommerce-page .order_details li {
clear: left;
margin-bottom: 10px;
/* END Make the cart table responsive */

@media all {

.hgroup-wrap {
padding-top: 5px;
padding-bottom: 5px;

#site-logo {
margin-top: 5px;
#content-woocommerce {


Wednesday, 18 June 2014

The Perfect Responsive FREE eCommerce Solution

So you're building a website for someone, and you've bought the domain name and found a nice reliable company to host it.  Nice work.  Now what?

There is a plethora of options out there, so to save you a little bit of time, here's the solution I went with that is pretty ace.

Firstly, there were a few prerequisites that I needed to tick off.  The site had to:
  • Be Responsive (look sexy on all devices)
  • Be Free (client was a low budget business and it wasn't worth paying for templates, etc)
  • Have a shopping cart system
  • Sit on a CMS (Content Management System - so the client could update content themselves)
  • Be easy to navigate and manage

Here's the setup I decided upon which works a treat:
CMS: WordPress
Shopping Cart Plugin: WooCommerce
Shopping Cart Add-in: Weight-Based Shipping (For basing shipping cost on weight of bundled items)
WooCommerce Theme: Travelify

Installing each of these is an absolute breeze, and within 10 minutes the site was ready to have content and products put in.

Lastly I'd just like to thank the developers of all the products above for their hard work, saving me (and hopefully you!) a lot of time and pain!

Edit: Since this post, I have noticed a few issues with the solution which I have solved in the following post: http://thebaretta.blogspot.com.au/2014/07/the-perfect-responsive-free-ecommerce.html 

Monday, 28 April 2014

Freenas - Setting up the Transmission Plugin on Freenas with Guest Permissions

Follow the guide here by Supa, there's not much to it: http://forums.freenas.org/index.php?threads/newbie-guide-to-freenas-plex-couchpotato-sickbeard-and-transmission.19043/

For the linux noobs like myself, these are the two steps you need to complete in order to set up Transmission so that any files it downloads are read/writeable by all users.

  • Go to your jails tab again and enter into the Transmission Command-Line Shell:
  • Type: edit /usr/pbi/transmission-amd64/etc/transmission/home/settings.json and just change the field unmask to "unmask = 0". Once finished, hit esc, leave, and save the changes.
If your settings.json file is not located there you will need to go back to Freenas and click on Plugins > Transmission on the sidebar and review the location of configuration directory.  Then you can input that into the Command Line.

On newer versions of Transmission the setting Unmask has been renamed to Umask.

InfoPath XMLWriter - Adding Rows in a Repeating Table That Contain a Boolean Field

Came across an issue today with an InfoPath form I am working on.

In the form there is a Repeating Table that contains within it both String and Boolean Fields.

In order to make the form prettier we have created custom buttons to add a row to the Repeating Table.  These button use an XMLWriter to add a new row.

For any new fields (String or Boolean) the writer does the following to create a blank Field:

writer.WriteElementString("fieldname", myNamespace, "");

The problem I had was that if you created Boolean fields like the line above, and then tried to Submit the form to SharePoint, you receive a validation error "Some rules were not applied.  InfoPath cannot submit the form"

Turns out the Boolean Fields were failing to validate with the empty string value.  

This was solved by putting in a value that isn't used in the form for the Boolean Field:

writer.WriteElementString("fieldname", myNamespace, "0");

Wednesday, 23 April 2014

SharePoint 2010 - Export to Spreadsheet Action Missing on Survey Overview Page

Most people can't find the 'Export to Spreadsheet' option from the Action drop-down menu due to not being on the Overview.aspx page.

But if you've modified the Overview page via SharePoint Designer, then you've probably lost the Overview menu completely

The solution is to create a New List View via SharePoint Designer and set it as Default (crucial!).


If you don't set the new view as the default page, then the Export option will still be missing.

Wednesday, 12 March 2014

Photoshop - How To Make Lovely Rounded Corners

  • Select a square using Marquee tool
  • Select > Modify > Smooth
  • Choose radius on the corners
I've always been frustrated with the damn Feather option (right click your selected square > Feather) not making a hard corner.

Thank you - http://www.mediacollege.com/adobe/photoshop/shapes/rounded-corners.html 

Wednesday, 22 January 2014

SharePoint - Field type [FieldName] is not installed properly. Go to the list settings page to delete this field.

Sorry, something went wrong
Field type [FieldName]  is not installed properly. Go to the list settings page to delete this field.  

This is caused when a FieldType has been created usually via a custom solution (WSP), and has been used somewhere, then uninstalled while still being used somewhere on the Site Collection.  This error message is similar on all versions of SharePoint 2007/2010/2013.

Usually you will have one or more Site Columns that are using the FieldType, and possibly one or more Content Types that are using that Site Column, and then most likely one or more Lists/Librarires using the Site Columns or Site Content Types.

If you want to remove the Field Type Completely, steps to resolve are as follows:

  • Try to delete the Site Column or Content Type from every list it is being used on.  If you can't delete the Content Type from the list, you will have to delete the list.
  • Delete the Content Types using the Site Column (Site Actions > Site Settings > Site Content Types)
  • Delete the Site Column (Site Actions > Site Settings > Site Columns)
I was able to get up to the last point but still couldn't delete the Site Column as it was causing an error just trying to navigate to it.  In that case, the following Powershell script can be ran to remove the Site column.

$site = Get-SPWeb http://rootweb
$column = $site.Fields["Field Type Title"]

Powershell grabbed and modified from here, Thanks! http://www.sharepointfix.com/2011/04/powershell-script-to-delete-site.html 

SharePoint 2010 - Access List / Library Data Across Web Applications

We can do this using jQuery.js and SPServices.js
Only issue is that you will most likely be forced to receive a notification every time the page loads:

"this page is accessing information that is not under its control"

I found that although it's possible the solution was not ideal, but have not yet found an alternative.  However I thought I would provide it in case anyone else has a need for it.

The code below will retrieve the list item information from the Page library at http://intranet.  CAML query retrieves the most recent 4 articles from the Page library and the last line appends the HTML statement to the page.

The HTML statement that is created grabs the value of the FileRef Field and places it inside a div.


$(document).ready(function() {
    operation: "GetListItems",
    async: false,
    webURL: "http://intranet",
    listName: "Pages",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /><FieldRef Name='ArticleStartDate' /><FieldRef Name='PublishingRollupImage' /><FieldRef Name='FileRef' /></ViewFields>",
    CAMLQuery: "<Query><OrderBy><FieldRef Name='ArticleStartDate' Ascending='FALSE' /></OrderBy></Query>",
    CAMLRowLimit: 4,
    completefunc: function (xData, Status) {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() {
        var liHtml = "<div id='div>" + $(this).attr("ows_FileRef") + "</div>";

Friday, 17 January 2014

SharePoint 2013 - Cannot open excel or word documents

Have you recently upgraded to SharePoint 2013 but can't seem to open excel or word docs inside a Document Library?

Are you getting a URL that starts something like this?

  • ms-word:nft|u| 
  • ms-excel:ofv|u|

Chances are it's because you have both Office 2010 installed on your computer as well as either the full Office 2013 suite or any single Office 2013 product (Here's looking at you SharePoint Designer 2013!!!).

The reason for this is related to the new Microsoft SharePoint Foundation Support feature that gets updated when you install an Office 2013 product, in turn updating a few pointers to look at the Office15 Hive folder.

Others have solved this issue before me, but to save you having to read lots of comments, the solution is to install Office 2010 SP2.

Here's some links to a few blogs that helped me realise the cause and find the solution.