SharePoint Saturday Phoenix 2013

If you attended my session on Connecting a SharePoint list to a SQL table for CRUD ops, thank you for coming! If you missed it, or if you wanted to review it again, my girlfriend was kind enough to record it for everyone! The projector can be difficult to see due to room lighting, but you can always follow along with the slide deck. Both are included in this post:

Slide deck

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Windows 8.1 RTM on Surface Pro

Today I installed Windows 8.1 RTM on my Surface Pro, and so far I’m pretty happy. I decided to list what I believe to be some pros and cons to consider when updating your Windows 8 system:

Pros:

  • Mail app has improved significantly. Ability to easily select more than one message by either long-pressing or right-clicking, or by checking the checkboxes next to each item. Also appearance is closer to Outlook.com’s look and feel.
    Windows Mail
  • Setup prompted me to either keep my files only, or nothing (Sounds like an OS upgrade to me). I chose to keep my files, and it stored them in the usual Windows.old folder. However, all of my configurations and customizations remained! Awesome!
  • RT applications all installed with one click!
  • My WiFi connections and credentials were all remembered, I didn’t have to re-enter any passwords or search for SSIDs. Another huge plus IMHO.
  • IE bookmarks, auto-fill, remembered passwords, favorites, etc. were all remembered and flowed right through once I signed on with my Microsoft account.
  • Total time from begin to end to completely update the Surface Pro was approximately 15 minutes, including reboots

Cons:

  • Had to reinstall all of my x64 applications. Unfortunately for me, since the Surface Pro is my dev machine that’s quite a few applications. To me this is more of an OS upgrade than a Service Pack style update.
  • Still having to right-click and choose “Run As Administrator” every time I launch certain applications. Would be nice to have an “Always Run As Administrator” option
    UPDATE: This isn’t a limitation in Windows 8, this was my own lack of knowledge. To set an application (in this case Visual Studio 2013) to always run as administrator do the following (click the images to enlarge):

    1. Right-click on the Start screen tile and choose Open File Location
      Screenshot (3)
    2. Right-click on the program you want to alter and choose Properties
      Screenshot (4)
    3. Click the Advanced button at the bottom
      Screenshot (5)
    4. Make sure the Run As Administrator box is checked and click OK
      Screenshot (6)

    The application will now run as administrator whenever clicked from the Start screen!

So far more pros than cons, I’m pretty satisfied. I’ll post more here as I use the Surface more. Feel free to comment and add your pros/cons.

Update (09/11/2013 06:25 MT): It appears that if you’re running 8.1 Preview on the machine, it treats it like an OS upgrade, but if you’re running Windows 8 it’s treated like a SP update. Mark Brown noted that his Surface Pro running Windows 8 kept all of his x64 applications and just applied 8.1. Nice! Thanks for the feedback!

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

SSRS 2008 functionality in IE9, IE10

Seems lately browsers update faster than the web solutions, or at least faster than we can update the web solutions to keep up with the browser changes. Maybe it’s just me…

Regardless, my company very heavily relies upon custom reporting. Since there were numerous changes in the way IE9 and IE10 render JavaScript DOM objects and other elements, things like SSRS and SharePoint start “breaking”. I recently handled an update enterprise-wide to IE9 by having to modify SharePoint 2010’s compatibility with IE9. Now I had to do it for SSRS, because things like the context menu drop downs on the report items were either invisible or unresponsive when clicked. unresponsive
Thankfully, Chris Snowden posted this response on StackOverflow.

Basically, you’ll need to replace your ReportingService.js file with this one. Click that link, save the file, and follow these steps:

  1. Open Windows Explorer and navigate to your server’s C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportManagerjs directory
  2. Make a backup copy of your existing ReportingServices.js file
  3. Copy this one into the same location. Make sure it’s named “ReportingServices.js”. Do not overwrite your old file just in case.
  4. Restart the SQL Server Reporting Services service on the server
  5. Clear your IE browser cache

That’s it! You should now be able to view the report item’s context drop down menu when clicked. Enjoy!
working

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Update SQL table with SharePoint Excel file data automatically

This is probably an extremely rare circumstance, so I’m writing it down so I don’t forget how I did this.

I recently built a simple solution for my company called “Work & Win”. Basically it’s a slot machine game that allows one random employee per day to win a variety of pre-determined prizes. Initially, I set up the prizes table in the database so it could be managed through a web UI, which means the application reads from the database to determine which prize to show. However the business said they wanted it to instead be kept in an Excel file stored in SharePoint, and not in the database. Ugh.

So basically I needed to do several things:
1. Read the file in SharePoint
2. Copy the data from the file into the database
3. Do this on a regular basis in case of changes

I tried several options, including opening the file programmatically in Excel, copying the file to the local machine using WebDAV and xcopy, mapping a drive to the SharePoint library using WebDAV, having a scheduled task move the file around, use SSIS to try to consume the file, nothing worked. I finally created a two-step solution outside of SharePoint that did the trick:

First, I created a Windows Service to handle the movement of the file from SharePoint to the local server. Like all things SharePoint, the service needs to be created on and run inside of a SharePoint server. Attempting to reference the SharePoint libraries in a project outside of a SharePoint environment may appear to work, and it will compile, but when it runs you will get all sorts of bizarre errors with no guidance or help.

The service code looks like this:

using System;
using System.Diagnostics;
using System.ServiceProcess;
using Microsoft.SharePoint;
using System.IO;
using System.Timers;

namespace MyService
{
    public partial class Service1 : ServiceBase
    {
        public Timer timer = new Timer(3600000);
        public Service1()
        {
            InitializeComponent();
            
        }

        void timer_Tick(object sender, ElapsedEventArgs e)
        {
            GetWorksheet();
        }

        private void GetWorksheet()
        {
            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite site = new SPSite("http://<server>/<site>"))
                    {
                        site.AllowUnsafeUpdates = true;
                        using (SPWeb thisweb = site.OpenWeb())
                        {
                            SPList docs = thisweb.Lists["Shared Documents"];
                            SPQuery query = new SPQuery();
                            query.ViewFields = "<FieldRef Name='FileLeafRef' />";
                            query.Query = "<Where><Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>filename</Value></Eq></Where>";
                            SPListItemCollection collection = docs.GetItems(query);
                            SPFile file = collection[0].File;
                            byte[] byteArray = file.OpenBinary();
                            using (MemoryStream memStr = new MemoryStream())
                            {
                                memStr.Write(byteArray, 0, byteArray.Length);
                            }
                            File.WriteAllBytes(@"C:userspublicdocumentsfilename.xlsx", byteArray);
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry("The service has encountered an error: " + ex.Message.ToString());
            }
        }

        protected override void OnStart(string[] args)
        {
            EventLog.WriteEntry("Service started successfully");
            timer.Enabled = true;
            timer.Interval = 3600000;
            timer.Elapsed += new ElapsedEventHandler(timer_Tick);
            GetWorksheet();
        }

        protected override void OnStop()
        {
            EventLog.WriteEntry("Service stopped successfully");
            timer.Enabled = false;
        }
    }
}

Basically, it connects to the SharePoint site, loads the desired library where the file is stored, performs a query to get the file, then copies it to a MemoryStream. The MemoryStream sends it to a byte array, so a File object can save the file locally to the server. The timer is so it gets a fresh copy every hour in case of changes. When setting this up, make sure the service account that will be running this not only has access to both the SharePoint site but also the path where you’re dropping the file, and set this account in the “ProjectInstaller” code behind file like so:

this.serviceProcessInstaller1.Account = System.ServiceProcess.ServiceAccount.User;
this.serviceProcessInstaller1.Password = "<password>";
this.serviceProcessInstaller1.Username = "domain\username";

Step one done.

The next step is to get the data into the database. I used an SSIS package to read the file from the server as an Excel connection & map the necessary fields to the database table. Nice & simple. Just make sure the account that will be running the SSIS package on the server has access to the file on the SharePoint server.

Step two done.

The result is the ability for the application to read a current list of prizes from an Excel spreadsheet maintained in SharePoint, using only a Windows service and an SSIS package.

Sure there are many ways to accomplish this, including programmatically downloading it from SharePoint, using a SharePoint Timer job, Workflow, etc., but this worked for me.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

SharePoint 2010 compatibility in IE9

SharePoint 2010 is not fully compatible with IE9. To the best of my knowledge this is due to SharePoint’s heavy dependency upon DOM expandos and other features which were available through IE8, but are all removed from IE9. Controls like the Rich TextBox and People Picker tend to render improperly, throw errors on postback, etc. Fortunately, M_Olson posted a JavaScript mod that worked perfectly for me! It looks like this:

function ConvertEntityToSpan(ctx, entity)
{ULSGjk:;    
if(matches[ctx]==null)
	matches[ctx]=new Array();    
var key=entity.getAttribute("Key");    
var displayText=entity.getAttribute("DisplayText");    
var isResolved=entity.getAttribute("IsResolved");    
var description=entity.getAttribute("Description");    
var style='ms-entity-unresolved';    
if(isResolved=='True')
        style='ms-entity-resolved';    
var spandata="";
spandata+="";    
if(PreferContentEditableDiv(ctx))    
{
    if(browseris.safari)        
	{
        spandata+="";        
	}        
	else        
	{
        spandata+="";        
	}    
}    
else    
{
    spandata+="";    
}    
if (browseris.ie8standard)
    spandata+="r";    
if(displayText !='')
    spandata+=STSHtmlEncode(displayText);    
else
    spandata+=STSHtmlEncode(key);    
if (browseris.ie8standard)
    spandata+="rr";    
else    
    spandata+="";    
return spandata;
}
// **** CUSTOM FUNCTION ****
function fixDataInIE9(data)
{    
	if(data.indexOf('') >= 0)    
	{    
		data = data.replace('', '');    
	}    
	return data;
}

Save that as a .js file in your /TEMPLATE/LAYOUTS/1033 path, then reference it at the very bottom of your Master Page, immediately before the two input elements like so:

<script type="text/javascript" src="/_layouts/1033/filename.js"></script>

<input type="text" name="__spText1" title="text" style="display:none;" />
<input type="text" name="__spText2" title="text" style="display:none;" />

Worked like a charm! There are other ways to do this, such as call it from a ScriptLink control, which also works. The key is to load the JavaScript file absolutely last, after all other content has loaded. At least this will give my company the chance to keep using SharePoint 2010 until we can migrate everything to SharePoint 2013.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube