Joseph Michael Pesch
VP Programming

WCF Service X.509 Certificate CryptographicException: Keyset does not exist or Access is denied

by 3. August 2012 15:19

When I tried running my WCF service is received a System.Security.Cryptography.CryptographicException: Keyset does not exist or Access is denied.  In my case the issue was that the user my IIS application pool was running under did not have access to the private key.  There are a couple ways to grant the user access and make the X.509 certificate accessible to WCF.

Option 1: Grant read access to the file that contains the private key to the account that WCF is running under, using a tool such as cacls.exe.  See this link for full topic: http://msdn.microsoft.com/en-us/library/aa702621.aspx
The following code example edits (/E) the access control list (ACL) for the specified file to grant (/G) the NETWORK SERVICE account read (:R) access to the file.
cacls.exe "C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys\8aeda5eb81555f14f8f9960745b5a40d_38f7de48-5ee9-452d-8a5a-92789d7110b1" /E /G "NETWORK SERVICE":R

Option 2:  Use the Microsoft Management Console (MMC)
1) Start > Run > MMC
2) File > Add/Remove Snap-in > Certificates, Add, Computer Account, Local
3) Open > Certificates > Personal > Certificates
4) Right Click Certificate > All Tasks > Manage Private Keys
5) Add Network Service (or whatever login the application pool is running under) with Read access

Option 3:  Use Windows HTTP Services Certificate Configuration Tool (WinHttpCertCfg.exe) located here: http://www.microsoft.com/en-us/download/details.aspx?id=19801
Typical installation path: C:\Program Files (x86)\Windows Resource Kits\Tools\
This is another command line option and the installation folder contains an HTML instruction file on the command line parameters.

Tags:

Sharepoint Powershell Script to Remove Missing Feature References from Sharepoint Site Content Configuration

by 2. August 2012 11:55

Our Sharepoint 2010 Central Administration portal Health Analyzer shows a "Missing server side dependencies." entry.  When clicking into the details of that entry we see the error shown below:

[MissingFeature] Database [WSS_CONTENT_MySite] has reference(s) to a missing feature: Id = [00000000-0000-0000-0000-000000000000]. The feature with Id 00000000-0000-0000-0000-000000000000 is referenced in the database [WSS_CONTENT_MySite], but is not installed on the current farm

Below is code to find and or delete the reference to the missing feature.  Thanks to Phil Childs blog entry: http://get-spscripts.com/2011/06/removing-features-from-content-database.html for providing the powershell script.

Code (note remove the -ReportOnly parameter to actually delete the reference):

function Remove-SPFeatureFromContentDB($ContentDb, $FeatureId, [switch]$ReportOnly)
{
    $db = Get-SPDatabase | where { $_.Name -eq $ContentDb }
    [bool]$report = $false
    if ($ReportOnly) { $report = $true }
    
    $db.Sites | ForEach-Object {
        
        Remove-SPFeature -obj $_ -objName "site collection" -featId $FeatureId -report $report
                
        $_ | Get-SPWeb -Limit all | ForEach-Object {
            
            Remove-SPFeature -obj $_ -objName "site" -featId $FeatureId -report $report
        }
    }
}

function Remove-SPFeature($obj, $objName, $featId, [bool]$report)
{
    $feature = $obj.Features[$featId]
    
    if ($feature -ne $null) {
        if ($report) {
            write-host "Feature found in" $objName ":" $obj.Url -foregroundcolor Red
        }
        else
        {
            try {
                $obj.Features.Remove($feature.DefinitionId, $true)
                write-host "Feature successfully removed from" $objName ":" $obj.Url -foregroundcolor Red
            }
            catch {
                write-host "There has been an error trying to remove the feature:" $_
            }
        }
    }
    else {
        #write-host "Feature ID specified does not exist in" $objName ":" $obj.Url
    }
}

Remove-SPFeatureFromContentDB -ContentDB "WSS_CONTENT_MySite" -FeatureId "00000000-0000-0000-0000-000000000000" -ReportOnly

Tags:

Google Chart Image (Deprecated)

by 23. July 2012 15:14

Deprecated image based charting: https://developers.google.com/chart/image/

Here is a url based example of the deprecated Google chart image: 

https://chart.googleapis.com/chart?chf=bg,s,EFEFEF&chtt=Sample%20Chart&chco=9999CC&cht=pc&chd=t:60,30,10&chl=60|30|10&chs=300x150

For current charting methods visit the google coding playground here: https://code.google.com/apis/ajax/playground/

NOTE: The playgound didn't work on my IE8 browser, worked fine on Firefox.

Tags:

Open PDF in Sharepoint 2010

by 16. July 2012 17:02

We added an editable PDF document to our Sharepoint 2010 site; however, under the sites default settings it forced the user to save the document (i.e. it would not open the document directly from the site).  Below is a PowerShell script that will add the PDF MIME type to the list of allowable inline download types.  NOTE: Save the script with .PS1 extension to run in PowerShell.

$webapp = Get-SPWebApplication http://sharepointsitehere
If ($webapp.AllowedInlineDownloadedMimeTypes -notcontains "application/pdf")
{
  Write-Host -ForegroundColor White "Adding Pdf MIME Type..."
  $webapp.AllowedInlineDownloadedMimeTypes.Add("application/pdf")
  $webapp.Update()
  Write-Host -ForegroundColor White "Added and saved."
}
 Else
{
  Write-Host -ForegroundColor White "Pdf MIME type is already added."
}

Tags:

Sharepoint | PowerShell

HttpRuntime MaxRequestLength vs. Security RequestFiltering RequestLimits MaxAllowedContentLength

by 24. May 2012 15:11

Below is illustration of setting the maximum file upload size to 150MB in IIS7.  It requires two separate settings, one specifically for ASP.Net and the other specifically for IIS (as outlined below).

This section applies to the maximum allowed file size that ASP.Net will allow.  The value is specified in KBytes.

<system.web>
  <httpRuntime maxRequestLength="153600" executionTimeout="900" />
</system.web>

This section applies to the maximum length of content that IIS will allow.  The value is specified in Bytes.

<system.webServer>
   <security>
     <requestFiltering>
       <requestLimits maxAllowedContentLength="157286400" />
    </requestFiltering>
  </security>
</system.webServer>

Tags:

ASP.Net

Change Owner of SSRS Subscription on SQL Server 2008 R2

by 9. April 2012 10:26

Recently a DBA left the company and before his final day we disabled his login account to make sure that nothing broke (i.e. no jobs were running under his account, etc.).  The next couple days everything seemed fine.  Then after he left we actually deleted his account.  After deleting his account every SSRS report subscription that was originally created by him stopped working.  Some of them gave error messages that indicated some type of problem with the user account while others had very vague error messages.  The bottom line is that they ran fine when the AD account existed (regarless of the state of the account); but, the fail once the account is removed.  The initial fix was simply to create a new version of the subscription under a different user account; however, there were so many that this would be a very tedious and time consuming process. 

As an alternative the attached script can be used to change the subscription owner of all subscriptions belonging to a specific user. To execute the Script, please follow these steps:

1. Open Command line and Change Directory to this location “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn”
2. Execute the following command:

  • rs -i <Path to ChangeSubscriptionOwner.rss Script> -s <ReportServer URL> -e Mgmt2010 -v varOwner="<CurrentSubscriptionOwner>" -v varNewOwner="<NewSubscriptionOwner>"

Example:

  • rs -i D:\ChangeSubscriptionOwner.rss -s http://myserver/reportserver -e Mgmt2010 -v varOwner="mydomain\origuserid" -v varNewOwner="mydomain\newuserid"

This will change all the Subscriptions owned by "mydomain\originaluserid" to "mydomain\newuserid".

Note: Please Execute this script on the Reporting Services Server.

Note 2: This script and should be used only when Good ReportServer and ReportServerTempDB backups are available. As a good practice, you should also take a backup of the Reporting Services EncryptionKey.

Tags:

SQL Server | SSRS

C# LDAP Query - Get User Properties by User Name

by 5. April 2012 08:44

This code allows you to search for an Active Directory user by name and return a dynamic list of properties with optional line feed.  NOTE: There is a hardcoded userid and password that needs to be changed to a valid AD user login.

using System;
using System.DirectoryServices;
using System.Text;
using System.Text.RegularExpressions;

namespace LDAP_Query
{
  class Program
  {
    static void Main(string[] args)
    {
      Console.Write(GetLDAPUserByName("Joseph Pesch"
        , new string[] { "department", "title", "givenName", "displayName" }
        , ((char)10).ToString()));
    }

    private static string GetLDAPUserByName(string userName, string[] properties, string lineFeed)
    {
      StringBuilder sb = new StringBuilder();
      userName = Regex.Replace(userName, @"[\(\)\*\\]", (match) =>
      {
        // escape reserved chars 
        return "\\" + ((int)match.Value[0]).ToString("x");
      }, RegexOptions.Compiled);
      string query = "(&"
        + "(objectCategory=person)" + "(objectClass=user)"
        + "(|" + "(&" + "(sn={0}*)" + "(givenName={1}*)" + ")"
        + "(displayName={2})" + ")" + ")";
      query = String.Format(query, userName.Split(' ')[1]
        , userName.Split(' ')[0], userName);
      using (DirectoryEntry entry =
        new DirectoryEntry("LDAP://" + Environment.UserDomainName
        , "userid", "password", AuthenticationTypes.Secure))
      {
        using (DirectorySearcher ds =
          new DirectorySearcher(entry, query, null, SearchScope.Subtree))
        {
          SearchResultCollection res = ds.FindAll(); // all matches 
          if (res == null) return "CANNOT LOCATE USER";
          foreach (SearchResult r in res)
          {
            foreach (string prop in properties)
            {
              foreach (object property in r.Properties[prop])
              {
                sb.Append(prop).Append("=").Append(property.ToString()).Append(lineFeed);
              }
            }
          }
        }
      }
      return sb.ToString();
    }

  }

}

Tags:

C#

C# Draw Thermometer Goal

by 14. March 2012 07:59

This sample shows how to take a generic thermometer image (with transparent background and empty contents, i.e. outline only) and draw in the content using C#.  The base thermometer image is attached and full code sample below. 

Here are samples of the base image and a test output using the URL shown below:

DrawThermometer.aspx?colorString=%230A58BC&goalHeadText=Southwest Funding&goalBodyText=November Goal&goalAmt=75&actualAmt=55&amtSuffix=MM

Here is the sample code:

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Drawing;

public partial class DrawThermometer : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    // Sample call:
    // DrawThermometer.aspx?colorString=%230A58BC&goalHeadText=Southwest Funding&goalBodyText=November Goal&goalAmt=75&actualAmt=55&amtSuffix=MM
    string colorString = Request.Params["colorString"];
    string goalHeadText = Request.Params["goalHeadText"];
    string goalBodyText = Request.Params["goalBodyText"];
    decimal goalAmt = Convert.ToDecimal(Request.Params["goalAmt"]);
    string amtSuffix = Request.Params["amtSuffix"];
    decimal actualAmt = 0;
    // ActualAmt is optional input parameter, when not provided we run a SQL query.
    if (Request.Params["actualAmt"] != null)
    {
      actualAmt = Convert.ToDecimal(Request.Params["actualAmt"]);
    }
    else
    {
      string sql = "select sum(SomeField) as actualAmt from SomeTable where Something = true";
      using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCN"].ConnectionString))
      {
        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
          cn.Open();
          actualAmt = Convert.ToInt32(cmd.ExecuteScalar());
          actualAmt = Convert.ToDecimal(actualAmt * Convert.ToDecimal(.000001));
          cn.Close();
        }
      }
    }
    DrawImage(colorString, goalHeadText, goalBodyText, goalAmt, actualAmt, amtSuffix);
  }
  private void DrawImage(string colorString, string goalHeadText, string goalBodyText, decimal goalAmt, decimal actualAmt, string amtSuffix)
  {
    // Locals
    int width = 325;
    int height = 100;
    int barLeft = 83;
    int barTop = 44;
    int barWidth = 220;
    int barHeight = 29;
    int actualWidth = 0;
    int intervalWidth = 20;
    string val = string.Empty;
    ColorConverter converter = new ColorConverter();
    Color fillColor = (Color)converter.ConvertFromString(colorString);
    string imagePath = Server.MapPath("~/images/Thermometer.png");
    using (Image image = Image.FromFile(imagePath))
    {
      using (Graphics g = Graphics.FromImage(image))
      {
        Color color = fillColor;
        SolidBrush fillBrush = new SolidBrush(color);
        g.FillEllipse(fillBrush, new Rectangle(8, 23, 70, 70));
        g.FillRectangle(fillBrush, new Rectangle(barLeft - 10, barTop, 10, barHeight));
        actualWidth = Convert.ToInt32((Convert.ToDecimal(actualAmt) / Convert.ToDecimal(goalAmt)) * Convert.ToDecimal(barWidth));
        // Adjust for width of markers between bars...
        actualWidth = actualWidth - Convert.ToInt32((Convert.ToDecimal(actualAmt) / Convert.ToDecimal(goalAmt)) * 2);
        if (actualWidth > barWidth) actualWidth = barWidth;
        // TALL: Rectangle rect = new Rectangle(45, 135, 29, 145);
        // WIDE: Rectangle rect = new Rectangle(83, 44, 221, 29);
        Rectangle rect = new Rectangle(barLeft, barTop, actualWidth, barHeight);
        g.FillRectangle(fillBrush, rect);
        g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;
        g.DrawString(goalBodyText, new Font("Tahoma", 14, FontStyle.Bold), fillBrush, barLeft, barTop - 40); // use 28 when printing the goalHeadText...
        val = "$" + goalAmt.ToString() + amtSuffix;
        g.DrawString(val, new Font("Tahoma", 14, FontStyle.Bold), fillBrush, width - val.Length * 18, barTop - 40); // use 28 when printing the goalHeadText...
        // Draw the current actual number
        // TALL: g.DrawString("55.55", new Font("Tahoma", 10, FontStyle.Bold), Brushes.White, 40, 275);
        // WIDE: g.DrawString("55.55", new Font("Tahoma", 10, FontStyle.Bold), Brushes.White, 16, 39);
        val = "$" + actualAmt.ToString("#.00") + amtSuffix;
        g.DrawString(val, new Font("Tahoma", 12, FontStyle.Bold), Brushes.White
          , width - (barWidth - actualWidth) - 5 - (val.Length * 14), 47);
        // Draw the intervals
        bool target = false;
        for (int i = 1; i <= 10; i++)
        {
          Font font = new Font("Arial Narrow", 8, FontStyle.Bold);
          Brush brush = fillBrush;
          int interval = ((goalAmt > Convert.ToInt32(actualAmt)) ? Convert.ToInt32(goalAmt) : Convert.ToInt32(actualAmt)) / 10;
          int mark = i * interval;
          val = "" + mark.ToString();
          if (!target && (i.Equals(10) || ((mark >= goalAmt) || goalAmt - mark < interval / 2)))
          {
            target = true;
            font = new Font("Arial Narrow", 8, FontStyle.Bold);
            brush = Brushes.Red;
          }
          g.DrawRectangle(new Pen(Brushes.Black), barLeft - 2 + (intervalWidth * i), height - 60, 1, 8);
          g.DrawRectangle(new Pen(Brushes.Black), barLeft - 2 + (intervalWidth * i), height - 33, 1, 8);
          g.DrawString(val, font, brush, barLeft + (intervalWidth * i) - (val.Length * 4), height - 18);
        }
      }
      System.IO.MemoryStream stream = new System.IO.MemoryStream();
      image.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
      Context.Response.BinaryWrite(stream.ToArray());
    }

  }

}

Thermometer.png (5.78 kb)

Tags:

C#

Microsoft Word VBA Macro to Run Mail Merge on SQL DSN

by 8. March 2012 13:07

This is a sample macro to generate a dynamic SQL connection and SQL select statement using VBA in Microsoft Word.  The only item I was not able to resolve is getting the connection string to be DSN-less (i.e. it seems to always require and actual ODBC DSN).

Private Sub Document_Open()
  
  Dim msg As String
  msg = "Select ""Yes"" to run the standard half month interval query." & _
        vbCrLf & vbCrLf & _
        "For a custom query select ""No"" " & _
        "and go to Mailings > Edit Recipient List > Filter"
  RunQuery (MsgBox(msg, vbYesNo, "Run Standard Query") = vbYes)
  
End Sub

Private Function GetConnection() As String
  
  GetConnection = "DSN=DWSQL;" & _
       "Driver={SQL Server};" & _
       "Server=servername\instancename,portnumber;" & _
       "Database=databasename;" & _
       "Trusted_Connection=yes;" & _
       "UID=" & Environ("username") & ";" & _
       "WSID=" & Environ("computername")

End Function

Private Sub RunQuery(Standard As Boolean)
  
  Dim doc As Document
  Set doc = ActiveDocument

  Dim sql As String
  If Standard Then
    sql = "select * from TestMailView where StandardCriteria = 'Y'"
  Else
    sql = "select * from TestMailView"
  End If

  ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
  ActiveDocument.MailMerge.OpenDataSource Name:="", ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:=GetConnection() _
    , SQLStatement:=sql, _
     SQLStatement1:="", SubType:=wdMergeSubTypeOther
     
  If Standard Then
    On Error GoTo MergeError
    With ActiveDocument.MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With
    'Close the main doc since we already generated the merged doc.
    doc.Close (False)
  End If
  
MergeError:
     If Err.Number <> 0 Then MsgBox Err.Description
  
End Sub

Tags:

Word Mail Merge Error: "Record 'number' contained too few data fields"

by 7. March 2012 10:44

Error message when you use an .odc file to connect to a specific table in a mail merge data source in Microsoft Word: "Record 'number' contained too few data fields"

This appears to be an issue when you use a SQL datasource and the table/view you are selecting is not in the default schema (i.e. dbo).  The Microsoft Support Article (link below) contains information on this.  Appears there is a registry setting (shown below) that can enable a fix for this.

Excerpt from the Microsoft Support Article:

How to enable the hotfix

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

To enable this hotfix, follow these steps:

  1. Exit all Office programs.
  2. Click Start, click Run, type regedit, and then click OK.
  3. If you are using a 2007 Office suite, locate and then click the following registry subkey:
    HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common
    If you are using Office 2003, locate and then click the following registry subkey:
    HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common
  4. On the Edit menu, point to New, and then click DWORD Value.
  5. Type UseQualifiedTableNameForMailMerge, and then press ENTER.
  6. Right-click UseQualifiedTableNameForMailMerge, and then click Modify.
  7. In the Value data box, type 1, and then click OK.
  8. On the File menu, click Exit to exit Registry Editor.


Microsoft Support Article

Tags:

C# Upload Local Document to SharePoint Site

by 22. February 2012 10:17

Below is a sample windows service that will run daily.  This method is used to open a local Excel file, refresh it's data connection(s), save the updated file locally and finally, upload the local file to a SharePoint site.

Command prompts to Delete and Create the Windows Service:

sc delete ServiceNameHere
sc create ServiceNameHere binpath= "c:\pathtoexe\AutomationService.exe"

Sample App.Config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="FileDictionary" type="System.Configuration.DictionarySectionHandler" />
  </configSections>
  <FileDictionary>
    <add key="C:\Data\File1.xlsx" value="http://mysharepoint/sites/reports/File1.xlsx" />
    <add key="C:\Data\File2.xlsx" value="http://mysharepoint/sites/reports/File2.xlsx" />
  </FileDictionary>
</configuration>

Sample Code:

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Net;
using System.Runtime.InteropServices;
using System.ServiceProcess;
using Microsoft.Office.Interop.Excel;

namespace AutomationService
{
  public partial class AutomationServiceInstance : ServiceBase
  {

    string path = ConfigurationManager.AppSettings["FilePath"];
    // Go back one day so we run on start...
    DateTime lastRun = DateTime.Now.AddDays(-1);

    public AutomationServiceInstance()
    {
      InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n') 
        + "Starting service ... " + DateTime.Now.ToString());
      if(System.IO.File.Exists(path + @"\LastRun.txt"))
      {
        // If last run time file exists load the date/time from the file
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
      }
      BackgroundWorker wkr = new BackgroundWorker();
      wkr.DoWork += TimeCycle;
      wkr.RunWorkerAsync();
    }

    protected override void OnStop()
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n')
        + "Stopping service ... " + DateTime.Now.ToString());
    }


    protected void TimeCycle(object sender, DoWorkEventArgs e)
    {
      do
      {
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Checking time ... " + DateTime.Now.ToString()
          + " lastRun: " + lastRun + "; curTime: " + DateTime.Now.ToString());
        if (!lastRun.DayOfYear.Equals(DateTime.Now.DayOfYear) 
          && DateTime.Now.TimeOfDay >= new TimeSpan(8,15,0)) ProcessIt();
        System.Threading.Thread.Sleep(60 * 1000);
      } while (true);
    }

    protected void ProcessIt()
    {
      // Key is the local source file, Value is the target web URL
      Hashtable tbl = (Hashtable)ConfigurationManager.GetSection("FileDictionary");
      foreach (DictionaryEntry de in tbl)
      {
        try
        {
          Application excel = new ApplicationClass();
          excel.DisplayAlerts = false;
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Begin processing ... " + DateTime.Now.ToString());
          string srcFile = de.Key.ToString();
          string outFile = de.Value.ToString();
          // Open the file 
          // Important, do not double up on objects in COM 
          // Notice the Workbooks variable used to open the workbook 
          // DO NOT DO THIS: Workbook wkb = excel.Workbooks.Open(srcFile, false, true); 
          Workbooks wkbs = excel.Workbooks;
          Workbook wkb = wkbs.Open(srcFile, false, true);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook open: " + DateTime.Now.ToString());
          wkb.EnableConnections();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     connections enabled: " + DateTime.Now.ToString());
          wkb.RefreshAll();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook refreshed: " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Processing complete ... " + DateTime.Now.ToString());
          // Clean up to release resources 
          // IMPORTANT: This cleanup sequence was required when running 
          // this inside a service that remains open and recycles on a schedule 
          // otherwise multiple EXCEL processes would be running and resources 
          // left open causing file access problems and memory leaks. 
          GC.Collect();
          GC.WaitForPendingFinalizers();
          wkb.Close(false, Type.Missing, Type.Missing);
          Marshal.ReleaseComObject(wkb);
          Marshal.ReleaseComObject(wkbs);
          excel.Quit();
          Marshal.ReleaseComObject(excel);
          GC.Collect();
          GC.WaitForPendingFinalizers();
          if (WebClientUpload(srcFile, outFile, path))
          {
            lastRun = DateTime.Now;
            System.IO.File.WriteAllText(path + @"\LastRun.txt", lastRun.ToString());
          }
        }
        catch (Exception ex)
        {
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Exception occurred ... " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        }
      }
    }
    static bool WebClientUpload(string SourcePath, string TargetPath, string LogPath)
    {
      WebClient wc = new WebClient();
      wc.UseDefaultCredentials = true;
      try
      {
        byte[] response = wc.UploadFile(TargetPath, "PUT", SourcePath);
      }
      catch (WebException ex)
      {
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Exception occurred ... " + DateTime.Now.ToString());
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        return false;
      }
      return true;
    }
  }
}

Tags:

C# | Excel | Sharepoint

Microsoft SQL Server Stop Script Execution

by 14. February 2012 14:11

I have two basic styles of scripting to stop a Microsoft SQL Server TSQL script execution through SQL Management Studio.  The first is a soft stop where I will throw an exception message for the user to read and take action of (i.e. manually cancel the script execution at that point if necessary).  It will wait with a predefined arbitrary time delay and if the user takes no action within the time limit the script will continue.  The second is a hard stop where we do not leave it up to the user.  For the hard stop the script must be run in SQLCMD mode.  The second style involves one of two possible methods.  First would be simply setting the severity level of the raiserror statment to level 20.  However, this is only allowed if the user running the script is a member of sysadmin role. So, the fallback to that (i.e. if the user is not a member of the sysadmin role) is to first check to see if the user is running the script in SQLCMD mode, and, if they are not throw an error message telling them that they must run the script in SQLCMD mode or as a user who is member of sysadmin role.  Immediately after that set a maximum waitfor delay (24 hours is the max delay).  NOTE: The fallback script will still execute if the user leaves the connection open and running for the full 24 hour delay so this is still tecnically not a hard stop in all cases.

Style 1 - Soft Stop

declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! '
  + ' Make sure you are running this script on the desired server. '
  + ' If this is not the desired server please cancel the script execution now! '
  + ' Otherwise, this script will execute in 30 seconds. '
  + ' The current server is: ' + @@servername
select @errMsg
print @errMsg
raiserror(@errMsg, 16, 1) with nowait
-- wait for the user to read the message, and terminate the script manually if need be
waitfor delay '00:00:30';

Style 2 - Hard Stop

:setvar SqlCmdMode "DbNameHere"
go
declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! This script MUST be run in SQLCMD mode!  Please cancel script now and re-run in SQLCMD mode.'
if ('$(SqlCmdMode)' = '$' + '(SqlCmdMode)')     
if is_srvrolemember('sysadmins') = 1
  raiserror(@errMsg, 20, -1) with log 
else begin
    select @errMsg
    print @errMsg
    raiserror(@errMsg, 16, 1) with nowait
    -- wait for the user to read the message, and terminate the script.
    waitfor delay '23:59:59'; 
end
go

Tags:

Microsoft SQL Server Halt Script Execution On Error

by 14. February 2012 09:41

I have two basic styles of scripting to perform a halt during a Microsoft SQL Server TSQL script execution through SQL Management Studio.  The first is a soft halt where I will throw an exception message for the user to read and take action of (i.e. manually cancel the script execution at that point if necessary).  It will wait with a predefined arbitrary time delay and if the user takes no action within the time limit the script will continue.  The second is a hard halt where we do not leave it up to the user.  For the hard halt the script must be run in SQLCMD mode.  The second style involves one of two possible methods.  First would be simply setting the severity level of the raiserror statment to level 20.  However, this is only allowed if the user running the script is a member of sysadmin role.  So, the fallback to that (i.e. if the user is not a member of the sysadmin role) is to first check to see if the user is running the script in SQLCMD mode, and, if they are not throw an error message telling them that they must run the script in SQLCMD mode or as a user who is member of sysadmin role.  Immediately after that set a maximum waitfor delay (24 hours is the max delay).  NOTE: The fallback script will still execute if the user leaves the connection open and running for the full 24 hour delay so this is still tecnically not a hard stop in all cases.

Style 1 - Soft Halt

declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! '
  + ' Make sure you are running this script on the desired server. '
  + ' If this is not the desired server please cancel the script execution now! '
  + ' Otherwise, this script will execute in 30 seconds. '
  + ' The current server is: ' + @@servername
select @errMsg
print @errMsg
raiserror(@errMsg, 16, 1) with nowait
-- wait for the user to read the message, and terminate the script manually if need be
waitfor delay '00:00:30'; 

Style 2 - Hard Halt

:setvar SqlCmdMode "DbNameHere"
go
declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! This script MUST be run in SQLCMD mode!  Please cancel script now and re-run in SQLCMD mode.'
if ('$(SqlCmdMode)' = '$' + '(SqlCmdMode)')     
if is_srvrolemember('sysadmins') = 1
  raiserror(@errMsg, 20, -1) with log 
else begin
    select @errMsg
    print @errMsg
    raiserror(@errMsg, 16, 1) with nowait
    -- wait for the user to read the message, and terminate the script.
    waitfor delay '23:59:59'; 
end
go

Tags:

SQL Server

Excel to PDF Conversion Using Office Automation

by 31. January 2012 08:17

I wrote a simple C# method to open and Excel file and save it as PDF using basic Microsoft Office automation via the Excel COM.  In my case this method was inside a Windows Service that was scheduled to refresh the external data links (to datawarehouse and SSAS cube).  The initial code was problematic in that after the initial execution the output PDF file would often be locked causing the subsequent executions to fail.  Restarting the service would release the file, so it was apparent that the method was not cleanly releasing it's resources at end of execution. After some research and testing I now have a cleanup sequence that seems to reliably releasing all the resources so the service can now run indefinitely without conflict.  Below is a subset of the C# Excel COM code that illustrates the basic steps.

As a side note, while researching this issue I found an interesting article on using OpenOffice to perform a similar operation.  In my case I'm not sure it would work (at least not without further research and extending the method), as I need to refresh external data connections (which was not a feature covered in the sample I found).  However, it did sound like an interesting option as it only requires a few DLL's and not the full application install that the Office COM automation seems to require.  I included a simple version of the OpenOffice code (along with a reference link to the original article I read) below as well.  I will try to research this option later and update my post with the findings. 

Important side note:  Running this code on a Windows 2008 Server will fail with generic file access error if you don't have the folders shown below (i.e. create empty folder to resolve the issue):

  • Windows 2008 Server x64: C:\Windows\SysWOW64\config\systemprofile\Desktop
  • Windows 2008 Server x86: C:\Windows\System32\config\systemprofile\Desktop

 

Microsoft Excel Sample Code:

// Start application
Application excel = new ApplicationClass();
// Turn off message boxes
excel.DisplayAlerts = false;

string sourceFilePath = @"C:\SourceFile.xls"; 
string targetFilePath = @"C:\TargetFile.pdf";  

// Open the file
// Important, do not double up on objects in COM
// Notice the Workbooks variable used to open the workbook
// DO NOT DO THIS: Workbook wkb = excel.Workbooks.Open(sourceFilePath, false, true);
Workbooks wkbs = excel.Workbooks;
Workbook wkb = wkbs.Open(sourceFilePath, false, true);

// Refresh data connections and do some formatting
wkb.EnableConnections();
wkb.RefreshAll();
Worksheet sht = wkb.Worksheets["Summary"] as Worksheet;
sht.get_Range("A1").EntireColumn.ColumnWidth = 21;
sht.get_Range("B1", "E1").EntireColumn.AutoFit();
sht.get_Range("A1").Select();

// Delete target file
File.Delete(targetFilePath);

// Export to PDF
sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
, targetFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
, true, false, Type.Missing, Type.Missing, false, Type.Missing);

// Clean up to release resources
// IMPORTANT: This cleanup sequence was required when running
// this inside a service that remains open and recycles on a schedule
// otherwise multiple EXCEL processes would be running and resources
// left open causing file access problems and memory leaks.
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(sht);
wkb.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(wkbs);
excel.Quit();
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();

Here is a good article on cleaning up Excel COM objects: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp.

OpenOffice Sample Code:

/*
You need the following DLL's from OpenOffice and reference them in your project:

cli_basetypes.dll 
cli_cppuhelper.dll 
cli_oootypes.dll 
cli_uno.dll 
cli_ure.dll 
cli_uretypes.dll 
*/

//...
using System;
using uno.util; 
using unoidl.com.sun.star.lang; 
using unoidl.com.sun.star.uno; 
using unoidl.com.sun.star.bridge; 
using unoidl.com.sun.star.frame; 
using unoidl.com.sun.star.text; 
using unoidl.com.sun.star.beans; 
using unoidl.com.sun.star.util;  
//...
  
string sourceFilePath = "file:///C:/SourceFile.xls"; 
string targetFilePath = "file:///C:/TargetFile.pdf";  

// Start OpenOffce or get a reference to an existing session 
XComponentContext localContext = Bootstrap.bootstrap(); 
XMultiServiceFactory multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager(); 
XComponentLoader componentLoader = (XComponentLoader)multiServiceFactory.createInstance("com.sun.star.frame.Desktop");  

// Set the file open properties
PropertyValue[] openProperties = new PropertyValue[2]; 
// Hidden...
openProperties[1] = new PropertyValue(); 
openProperties[1].Name = "Hidden"; 
openProperties[1].Value = new uno.Any(true);  
// Read only...
openProperties[0] = new PropertyValue(); 
openProperties[0].Name = "ReadOnly"; 
openProperties[0].Value = new uno.Any(true); 
// Open the file 
XComponent sourceDocument = componentLoader.loadComponentFromURL(sourceFilePath, "_blank", 0, openProperties);  

// Conversion parameters - overwrite existing file, use PDF exporter 
PropertyValue[] conversionProperties = new PropertyValue[3]; 
conversionProperties[0] = new PropertyValue(); 
conversionProperties[0].Name = "Overwrite"; 
conversionProperties[0].Value = new uno.Any(true); 
conversionProperties[1] = new PropertyValue(); 
conversionProperties[1].Name = "FilterName"; 
conversionProperties[1].Value = new uno.Any("calc_pdf_Export");  

// Set PDF export parameters 
PropertyValue[] filterData = new PropertyValue[3];  
// JPEG compression quality 70 
filterData[0] = new PropertyValue(); 
filterData[0].Name = "Quality"; 
filterData[0].Value = new uno.Any(70); 
filterData[0].State = PropertyState.DIRECT_VALUE;  
// Max image resolution 300dpi 
filterData[1] = new PropertyValue(); 
filterData[1].Name = "ReduceImageResolution"; 
filterData[1].Value = new uno.Any(true); 
filterData[1].State = PropertyState.DIRECT_VALUE; 
filterData[2] = new PropertyValue(); 
filterData[2].Name = "MaxImageResolution"; 
filterData[2].Value = new uno.Any(300); 
filterData[2].State = PropertyState.DIRECT_VALUE;  
conversionProperties[2] = new PropertyValue(); 
conversionProperties[2].Name = "FilterData"; 
conversionProperties[2].Value = new uno.Any(filterData.GetType(), filterData);  
// Export the PDF 
XStorable xstorable = (XStorable)sourceDoc; 
xstorable.storeToURL(pdfFile, conversionProperties);  

// Close document 
((XCloseable)sourceDoc).close(false); 
((XCloseable)xstorable).close(false);

OpenOffice API documentation can be found here: http://www.openoffice.org/api/.  Here is a link to the artical I used for basis of OpenOffice version of code: http://stackoverflow.com/questions/769246/xls-to-pdf-conversion-inside-net).

Tags:

C# | Excel

Excel to PDF Conversion Using Office Automation and/or Open Office

by 30. January 2012 10:23

One option to converting Excel documents to PDF is using Microsoft Office automation.  However, this requires installing the full application on the server that will be performing the automation (see this link for more details on that: ).  Another alternate solution is to switch to OpenOffice which only requires installing a few DLL's on the automation server.  Another option that I will review in the future is using the "ExcelLibrary" http://code.google.com/p/excellibrary/downloads/list

Microsoft Excel Sample Code:

// Start application
Application excel = new ApplicationClass();
// Turn off message boxes
excel.DisplayAlerts = false;

string sourceFilePath = @"C:\SourceFile.xls"; 
string targetFilePath = @"C:\TargetFile.pdf";  

// Open the file
// Important, do not double up on objects in COM
// Notice the Workbooks variable used to open the workbook
// DO NOT DO THIS: Workbook wkb = excel.Workbooks.Open(sourceFilePath, false, true);
Workbooks wkbs = excel.Workbooks;
Workbook wkb = wkbs.Open(sourceFilePath, false, true);

// Refresh data connections and do some formatting
wkb.EnableConnections();
wkb.RefreshAll();
Worksheet sht = wkb.Worksheets["Summary"] as Worksheet;
sht.get_Range("A1").EntireColumn.ColumnWidth = 21;
sht.get_Range("B1", "E1").EntireColumn.AutoFit();
sht.get_Range("A1").Select();

// Delete target file
File.Delete(targetFilePath);

// Export to PDF
sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
, targetFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
, true, false, Type.Missing, Type.Missing, false, Type.Missing);

// Clean up to release resources
// IMPORTANT: This cleanup sequence was required when running
// this inside a service that remains open and recycles on a schedule
// otherwise multiple EXCEL processes would be running and resources
// left open causing file access problems and memory leaks.
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(sht);
wkb.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(wkbs);
excel.Quit();
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();

Here is a good article on cleaning up Excel COM objects: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp.

OpenOffice Sample Code:

/*
You need the following DLL's from OpenOffice and reference them in your project:

cli_basetypes.dll 
cli_cppuhelper.dll 
cli_oootypes.dll 
cli_uno.dll 
cli_ure.dll 
cli_uretypes.dll 
*/

//...
using System;
using uno.util; 
using unoidl.com.sun.star.lang; 
using unoidl.com.sun.star.uno; 
using unoidl.com.sun.star.bridge; 
using unoidl.com.sun.star.frame; 
using unoidl.com.sun.star.text; 
using unoidl.com.sun.star.beans; 
using unoidl.com.sun.star.util;  
//...
  
string sourceFilePath = "file:///C:/SourceFile.xls"; 
string targetFilePath = "file:///C:/TargetFile.pdf";  

// Start OpenOffce or get a reference to an existing session 
XComponentContext localContext = Bootstrap.bootstrap(); 
XMultiServiceFactory multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager(); 
XComponentLoader componentLoader = (XComponentLoader)multiServiceFactory.createInstance("com.sun.star.frame.Desktop");  

// Set the file open properties
PropertyValue[] openProperties = new PropertyValue[2]; 
// Hidden...
openProperties[1] = new PropertyValue(); 
openProperties[1].Name = "Hidden"; 
openProperties[1].Value = new uno.Any(true);  
// Read only...
openProperties[0] = new PropertyValue(); 
openProperties[0].Name = "ReadOnly"; 
openProperties[0].Value = new uno.Any(true); 
// Open the file 
XComponent sourceDocument = componentLoader.loadComponentFromURL(sourceFilePath, "_blank", 0, openProperties);  

// Conversion parameters - overwrite existing file, use PDF exporter 
PropertyValue[] conversionProperties = new PropertyValue[3]; 
conversionProperties[0] = new PropertyValue(); 
conversionProperties[0].Name = "Overwrite"; 
conversionProperties[0].Value = new uno.Any(true); 
conversionProperties[1] = new PropertyValue(); 
conversionProperties[1].Name = "FilterName"; 
conversionProperties[1].Value = new uno.Any("calc_pdf_Export");  

// Set PDF export parameters 
PropertyValue[] filterData = new PropertyValue[3];  
// JPEG compression quality 70 
filterData[0] = new PropertyValue(); 
filterData[0].Name = "Quality"; 
filterData[0].Value = new uno.Any(70); 
filterData[0].State = PropertyState.DIRECT_VALUE;  
// Max image resolution 300dpi 
filterData[1] = new PropertyValue(); 
filterData[1].Name = "ReduceImageResolution"; 
filterData[1].Value = new uno.Any(true); 
filterData[1].State = PropertyState.DIRECT_VALUE; 
filterData[2] = new PropertyValue(); 
filterData[2].Name = "MaxImageResolution"; 
filterData[2].Value = new uno.Any(300); 
filterData[2].State = PropertyState.DIRECT_VALUE;  
conversionProperties[2] = new PropertyValue(); 
conversionProperties[2].Name = "FilterData"; 
conversionProperties[2].Value = new uno.Any(filterData.GetType(), filterData);  
// Export the PDF 
XStorable xstorable = (XStorable)sourceDoc; 
xstorable.storeToURL(pdfFile, conversionProperties);  

// Close document 
((XCloseable)sourceDoc).close(false); 
((XCloseable)xstorable).close(false);

OpenOffice API documentation can be found here: http://www.openoffice.org/api/.  Here is a link to the artical I used for basis of OpenOffice version of code: http://stackoverflow.com/questions/769246/xls-to-pdf-conversion-inside-net).

Tags:

C# | Excel

C# Determine if Folder is Empty

by 28. January 2012 21:25
if (!Directory.EnumerateFileSystemEntries(FolderPath).Any())
{ 
  System.Diagnostics.Debug.WriteLine("Folder is empty");
}

Tags:

C#

SQL Server Management Studio "Invalid Object Name ..." when Object Does Exist

by 25. January 2012 10:06

I was struggling to understand why the SSMS intellisense was highlighting certain objects in my query window and indicating that they were not valid; when in fact, they did exist.  I tried closing and re-opening the studio, refreshing the local cache (Edit -> IntelliSense -> Refresh Local Cache (CRTL+Shift+R)).  All to no avail.  I finally realized the issue was due to the fact that the top of my SQL script had statements to drop and recreate some of the objects dynamically (e.g. if exists(...) drop XXX go select * into XXX from YYY).  It turns out that even though the objects actively existed in the database the fact that the script had a conditional drop statement of the objects, the IntelliSense seemed to override reality with fiction (i.e. thinking they are or will be dropped, perhaps?).  Anyway, if I simply commented out the drop statements all the errors went away.  Strange...

Tags:

SQL Server

SharePoint 2010 Default Log File Location

by 24. January 2012 07:29

Since I always seem to forget the path to the SharePoint 2010 default log folder... Here it is: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

Tags:

Sharepoint

Transform URL List into HTML Unordered List

by 23. January 2012 16:16
This method will take a flat list of fully qualified URL's and transform them into nested bullets (i.e. HTML unordered lists).  It is a simple method to convert the flat list of URL's into a psuedo tree view.  Here is the C# code to transform the list.
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Xml;
using System.Xml.Xsl;

namespace UrlListToSiteMap
{
  public class Program
  {
    public static void Main(string[] args)
    {
      /*
         This method accepts a text file of fully qualified URLs 
         and will convert into HTML unordered list. 
      */
      string path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
      List urlRecords = File.ReadAllLines(path + @"\UrlList.txt").ToList();
      urlRecords.Sort();
      XmlDocument doc = new XmlDocument();
      doc.AppendChild(doc.CreateNode(XmlNodeType.Element, "SiteMap", ""));
      XmlNode rootNod = doc.ChildNodes[0];
      foreach (string url in urlRecords)
      {
        XmlNode pathNod = rootNod;
        int iNod = 0;
        int iNods = url.ToLower()
          .Replace(@"http://", "").Replace("'", "").Split('/').Count();
        foreach (string nod in url.ToLower()
          .Replace(@"http://", "").Replace("'", "").Split('/'))
        {
          iNod++;
          System.Diagnostics.Debug.WriteLine(nod);
          XmlNode subNod = pathNod.SelectSingleNode("Node[@URL='" + nod + "']");
          if (subNod == null)
          {
            System.Diagnostics.Debug.WriteLine(pathNod.Name);
            string nodType = iNod.Equals(iNods) ? "Leaf" : "Node";
            subNod = doc.CreateNode(XmlNodeType.Element, nodType, "");
            XmlAttribute atr = doc.CreateAttribute("URL");
            atr.Value = nod;
            subNod.Attributes.Append(atr);
            pathNod.AppendChild(subNod);
          }
          pathNod = subNod;
        }
      }
      doc.Save(path + @"\SiteMap.xml");
      XslTransform xslt;
      xslt = new XslTransform();
      xslt.Load(path + "SiteMap.xslt");
      xslt.Transform(path + "SiteMapNew.xml", path + "SiteMap.html");
    }
  }
}

Attached is the XSLT file for the transformation. NOTE: If you only want the folders and not the actual documents, remove the "Leaf" section from the XSLT.

SiteMap.xslt (583.00 bytes)

Tags:

C# | HTML

SharePoint Deploy Workflow Feature to Server Farm

by 11. January 2012 13:24

This post covers steps used to deploy the workflow feature to a SharePoint site (or sub-site as shown in this example URL: http://ServerNameHere/sites/SubSiteNameHere).  Attached is a sample SharePoint workflow feature that can configured to perform a daily refresh of an Excel file from a SharePoint site (i.e. refreshing the data connections to update data from external sources).

To setup the Visual Studio Solution, you will need to change the URL located in the three areas shown below:

  1. Project -> Site URL -> http://ServerNameHere/sites/SubSiteNameHere
  2. Web References -> ExcelWebService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/excelservice.asmx
  3. Web References -> ListService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/lists.asmx

When deploying the workflow feature on the server (either through Visual Studio right-click and “Deploy”; or, via Power Shell script shown below) I received the following error message:

Error occurred in deployment step 'Activate Features': Feature with Id '{GUID}' is not installed in this farm, and cannot be added to this scope.

Power Shell Deployment Script:

rem NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.
rem We are using the execadmsvcjobs to execute rather than letting th Timer service run them.
rem ADDING...
cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"
stsadm -o addsolution -filename "C:\VSS\ExcelRefreshWorkflow\bin\Debug\ExcelRefreshWorkflow.wsp"
stsadm -o deploysolution -name "ExcelRefreshWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies
stsadm -o execadmsvcjobs
stsadm -o activatefeature -name "ExcelRefreshWorkflow_Feature1" -url http://ServerNameHere/sites/SubSiteNameHere

NOTE: This feature is being deployed to “Scope = Site”, not Farm, Web or Web Application scope.

image

If you are running a single server instance of Sharepoint this should not happen (i.e. your deployment should be completed successfully). In the case of a Sharepoint server farm you will need to perform the following steps manually to complete the installation.

  1. From the “SharePoint 2010 Central Administration” site:
    • Navigate to: “System Settings” -> “Manage farm solutions”
    • Click on “excelrefreshworkflow.wsp”
    • Click on “Deploy Solution” and complete the deployment screen (schedule or run immediately).
    • Refresh the farm solutions screen until the solution management screen until the status column shows “Deployed” (note, the “Deployed To” column should show “Globally deployed.”).
    • NOTE: Make sure the “SharePoint 2010 Timer” service is running; otherwise, this step will not complete.
  2. Open the normal SharePoint site and from the top level site:
    • Navigate to: “Site Actions” -> “Site Settings” -> “Site Collection Administration” -> “Site collection features”
    • Click the “Activate” button associated with the “ExcelRefreshWorkflow” item in the list.
    • NOTE: Repeat this step on every sub-site that you want to use the workflow in.
  3. At this point the installation is complete. You can now navigate to the sub-site and configure the workflow on the appropriate Library and activate it on the appropriate documents in the library.
    • From the sub-site home page, navigate to: “Library Tools” -> “Library” -> “Library Settings” -> “Workflow Settings”.
    • From this screen you should see the “ExcelRefreshWorkflow” item in the “Select a workflow template:” list. Select that item, complete the remaining configuration settings and click “OK”.
    • For each document desired, click on the document dropdown and select “Workflows”.
    • Click on the “ExcelRefreshWorkflow”
    • Setup the appropriate configuration settings and click the “Start Workflow” button.
    • NOTE: All activity related to the workflow (including the execution history) will be stored in the associated “Workflow History”.

Attached is a sample workflow that will open an Excel file in document library, refresh the data connection and save the file as a new version in the document library on a scheduled interval.

ExcelRefreshWorkflow.zip (422.11 kb)

Tags:

Sharepoint | Workflow Foundation