Joseph Michael Pesch
VP Programming

SQL Server Analysis Services (SSAS) Issue Processing Database on Server

by 31. August 2011 16:28

I encountered the below error when attempting to process the SSAS database.  In my case all I needed to do was switch the data source connection string provider from "Native OLE DB\SQL Server Native Client 10.0" (SQLNCLI10.1) to ".Net Providers\SqlClient Data Provider" (System.Data.SqlClient).  For some reason the OLE DB provider worked fine on my local development PC; however, it did not work on my test server and required the native .Net SQL client to process.

ERROR MESSAGE:

OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Tags:

SQL Server Analysis Services (SSAS) Issue Processing Database on Server

by 30. August 2011 14:36

I encountered the below error when attempting to process the SSAS database.  In my case all I needed to do was switch the data source connection string provider from "Native OLE DB\SQL Server Native Client 10.0" (SQLNCLI10.1) to ".Net Providers\SqlClient Data Provider" (System.Data.SqlClient).  For some reason the OLE DB provider worked fine on my local development PC; however, it did not work on my test server and required the native .Net SQL client to process.

ERROR MESSAGE:

OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Tags:

SQL Server Analysis Services (SSAS) Deployment from Dev to Test/Prod

by 30. August 2011 10:59

 

I encountered the below error when attempting to process the SSAS database.  In my case I went into the Services control panel and found the login account that the SQL Server Analysis Services was using and granted that user db_datareader permission on my source data warehouse database.

ERROR MESSAGE:

OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Tags:

C# Utility to Read Exif Image Data

by 28. August 2011 17:05

The below code will represents a utility class that makes it easier to get Exif data from images.

using System;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Text;
using System.Linq;

namespace ImageProperties
{
  class ExifUtil
  {

    // Sample usage:
    // ExifUtil.EXIFReader exif = new ExifUtil.EXIFReader(@"c:\pathtoimage.jpg");
    // Console.WriteLine(exif[ExifUtil.PropertyTagId.EquipMake].ToString());
    // Console.WriteLine(exif[ExifUtil.PropertyTagId.EquipModel].ToString());

    public enum PropertyTagId
    {
      GpsVer = 0x0000,
      GpsLatitudeRef = 0x0001,
      GpsLatitude = 0x0002,
      GpsLongitudeRef = 0x0003,
      GpsLongitude = 0x0004,
      GpsAltitudeRef = 0x0005,
      GpsAltitude = 0x0006,
      GpsGpsTime = 0x0007,
      GpsGpsSatellites = 0x0008,
      GpsGpsStatus = 0x0009,
      GpsGpsMeasureMode = 0x000A,
      GpsGpsDop = 0x000B,
      GpsSpeedRef = 0x000C,
      GpsSpeed = 0x000D,
      GpsTrackRef = 0x000E,
      GpsTrack = 0x000F,
      GpsImgDirRef = 0x0010,
      GpsImgDir = 0x0011,
      GpsMapDatum = 0x0012,
      GpsDestLatRef = 0x0013,
      GpsDestLat = 0x0014,
      GpsDestLongRef = 0x0015,
      GpsDestLong = 0x0016,
      GpsDestBearRef = 0x0017,
      GpsDestBear = 0x0018,
      GpsDestDistRef = 0x0019,
      GpsDestDist = 0x001A,
      NewSubfileType = 0x00FE,
      SubfileType = 0x00FF,
      ImageWidth = 0x0100,
      ImageHeight = 0x0101,
      BitsPerSample = 0x0102,
      Compression = 0x0103,
      PhotometricInterp = 0x0106,
      ThreshHolding = 0x0107,
      CellWidth = 0x0108,
      CellHeight = 0x0109,
      FillOrder = 0x010A,
      DocumentName = 0x010D,
      ImageDescription = 0x010E,
      EquipMake = 0x010F,
      EquipModel = 0x0110,
      StripOffsets = 0x0111,
      Orientation = 0x0112,
      SamplesPerPixel = 0x0115,
      RowsPerStrip = 0x0116,
      StripBytesCount = 0x0117,
      MinSampleValue = 0x0118,
      MaxSampleValue = 0x0119,
      XResolution = 0x011A,
      YResolution = 0x011B,
      PlanarConfig = 0x011C,
      PageName = 0x011D,
      XPosition = 0x011E,
      YPosition = 0x011F,
      FreeOffset = 0x0120,
      FreeByteCounts = 0x0121,
      GrayResponseUnit = 0x0122,
      GrayResponseCurve = 0x0123,
      T4Option = 0x0124,
      T6Option = 0x0125,
      ResolutionUnit = 0x0128,
      PageNumber = 0x0129,
      TransferFunction = 0x012D,
      SoftwareUsed = 0x0131,
      DateTime = 0x0132,
      Artist = 0x013B,
      HostComputer = 0x013C,
      Predictor = 0x013D,
      WhitePoint = 0x013E,
      PrimaryChromaticities = 0x013F,
      ColorMap = 0x0140,
      HalftoneHints = 0x0141,
      TileWidth = 0x0142,
      TileLength = 0x0143,
      TileOffset = 0x0144,
      TileByteCounts = 0x0145,
      InkSet = 0x014C,
      InkNames = 0x014D,
      NumberOfInks = 0x014E,
      DotRange = 0x0150,
      TargetPrinter = 0x0151,
      ExtraSamples = 0x0152,
      SampleFormat = 0x0153,
      SMinSampleValue = 0x0154,
      SMaxSampleValue = 0x0155,
      TransferRange = 0x0156,
      JPEGProc = 0x0200,
      JPEGInterFormat = 0x0201,
      JPEGInterLength = 0x0202,
      JPEGRestartInterval = 0x0203,
      JPEGLosslessPredictors = 0x0205,
      JPEGPointTransforms = 0x0206,
      JPEGQTables = 0x0207,
      JPEGDCTables = 0x0208,
      JPEGACTables = 0x0209,
      YCbCrCoefficients = 0x0211,
      YCbCrSubsampling = 0x0212,
      YCbCrPositioning = 0x0213,
      REFBlackWhite = 0x0214,
      Gamma = 0x0301,
      ICCProfileDescriptor = 0x0302,
      SRGBRenderingIntent = 0x0303,
      ImageTitle = 0x0320,
      ResolutionXUnit = 0x5001,
      ResolutionYUnit = 0x5002,
      ResolutionXLengthUnit = 0x5003,
      ResolutionYLengthUnit = 0x5004,
      PrintFlags = 0x5005,
      PrintFlagsVersion = 0x5006,
      PrintFlagsCrop = 0x5007,
      PrintFlagsBleedWidth = 0x5008,
      PrintFlagsBleedWidthScale = 0x5009,
      HalftoneLPI = 0x500A,
      HalftoneLPIUnit = 0x500B,
      HalftoneDegree = 0x500C,
      HalftoneShape = 0x500D,
      HalftoneMisc = 0x500E,
      HalftoneScreen = 0x500F,
      JPEGQuality = 0x5010,
      GridSize = 0x5011,
      ThumbnailFormat = 0x5012,
      ThumbnailWidth = 0x5013,
      ThumbnailHeight = 0x5014,
      ThumbnailColorDepth = 0x5015,
      ThumbnailPlanes = 0x5016,
      ThumbnailRawBytes = 0x5017,
      ThumbnailSize = 0x5018,
      ThumbnailCompressedSize = 0x5019,
      ColorTransferFunction = 0x501A,
      ThumbnailData = 0x501B,
      ThumbnailImageWidth = 0x5020,
      ThumbnailImageHeight = 0x5021,
      ThumbnailBitsPerSample = 0x5022,
      ThumbnailCompression = 0x5023,
      ThumbnailPhotometricInterp = 0x5024,
      ThumbnailImageDescription = 0x5025,
      ThumbnailEquipMake = 0x5026,
      ThumbnailEquipModel = 0x5027,
      ThumbnailStripOffsets = 0x5028,
      ThumbnailOrientation = 0x5029,
      ThumbnailSamplesPerPixel = 0x502A,
      ThumbnailRowsPerStrip = 0x502B,
      ThumbnailStripBytesCount = 0x502C,
      ThumbnailResolutionX = 0x502D,
      ThumbnailResolutionY = 0x502E,
      ThumbnailPlanarConfig = 0x502F,
      ThumbnailResolutionUnit = 0x5030,
      ThumbnailTransferFunction = 0x5031,
      ThumbnailSoftwareUsed = 0x5032,
      ThumbnailDateTime = 0x5033,
      ThumbnailArtist = 0x5034,
      ThumbnailWhitePoint = 0x5035,
      ThumbnailPrimaryChromaticities = 0x5036,
      ThumbnailYCbCrCoefficients = 0x5037,
      ThumbnailYCbCrSubsampling = 0x5038,
      ThumbnailYCbCrPositioning = 0x5039,
      ThumbnailRefBlackWhite = 0x503A,
      ThumbnailCopyRight = 0x503B,
      LuminanceTable = 0x5090,
      ChrominanceTable = 0x5091,
      FrameDelay = 0x5100,
      LoopCount = 0x5101,
      GlobalPalette = 0x5102,
      IndexBackground = 0x5103,
      IndexTransparent = 0x5104,
      PixelUnit = 0x5110,
      PixelPerUnitX = 0x5111,
      PixelPerUnitY = 0x5112,
      PaletteHistogram = 0x5113,
      Copyright = 0x8298,
      ExifExposureTime = 0x829A,
      ExifFNumber = 0x829D,
      ExifIFD = 0x8769,
      ICCProfile = 0x8773,
      ExifExposureProg = 0x8822,
      ExifSpectralSense = 0x8824,
      GpsIFD = 0x8825,
      ExifISOSpeed = 0x8827,
      ExifOECF = 0x8828,
      ExifVer = 0x9000,
      ExifDTOrig = 0x9003,
      ExifDTDigitized = 0x9004,
      ExifCompConfig = 0x9101,
      ExifCompBPP = 0x9102,
      ExifShutterSpeed = 0x9201,
      ExifAperture = 0x9202,
      ExifBrightness = 0x9203,
      ExifExposureBias = 0x9204,
      ExifMaxAperture = 0x9205,
      ExifSubjectDist = 0x9206,
      ExifMeteringMode = 0x9207,
      ExifLightSource = 0x9208,
      ExifFlash = 0x9209,
      ExifFocalLength = 0x920A,
      ExifMakerNote = 0x927C,
      ExifUserComment = 0x9286,
      ExifDTSubsec = 0x9290,
      ExifDTOrigSS = 0x9291,
      ExifDTDigSS = 0x9292,
      ExifFPXVer = 0xA000,
      ExifColorSpace = 0xA001,
      ExifPixXDim = 0xA002,
      ExifPixYDim = 0xA003,
      ExifRelatedWav = 0xA004,
      ExifInterop = 0xA005,
      ExifFlashEnergy = 0xA20B,
      ExifSpatialFR = 0xA20C,
      ExifFocalXRes = 0xA20E,
      ExifFocalYRes = 0xA20F,
      ExifFocalResUnit = 0xA210,
      ExifSubjectLoc = 0xA214,
      ExifExposureIndex = 0xA215,
      ExifSensingMethod = 0xA217,
      ExifFileSource = 0xA300,
      ExifSceneType = 0xA301,
      ExifCfaPattern = 0xA302
    }

    public enum PropertyTagType
    {
      PixelFormat4bppIndexed = 0,
      Byte = 1,
      ASCII = 2,
      Short = 3,
      Long = 4,
      Rational = 5,
      Undefined = 7,
      SLONG = 9,
      SRational = 10
    }

    public class Fraction
    {
      #region Class constructor

      public Fraction(int numerator, int denumerator)
      {
        Numerator = numerator;
        Denumerator = denumerator;
      }

      public Fraction(uint numerator, uint denumerator)
      {
        Numerator = Convert.ToInt32(numerator);
        Denumerator = Convert.ToInt32(denumerator);
      }

      public Fraction(int numerator)
      {
        Numerator = numerator;
        Denumerator = 1;
      }

      #endregion

      #region Numerator

      private int numerator;
      public int Numerator
      {
        get
        {
          return numerator;
        }
        set
        {
          numerator = value;
        }
      }

      #endregion

      #region Denumerator

      private int denumerator;
      public int Denumerator
      {
        get
        {
          return denumerator;
        }
        set
        {
          denumerator = value;
        }
      }

      #endregion

      #region ToString override

      public override string ToString()
      {
        if (Denumerator == 1)
          return String.Format("{0}", Numerator);
        else
          return String.Format("{0}/{1}", Numerator, Denumerator);
      }


      #endregion
    }

    public class PropertyTagValue
    {
      public static object GetValueObject(PropertyItem property)
      {
        if (property == null)
          return null;
        switch ((PropertyTagType)property.Type)
        {
          //ASCII
          case PropertyTagType.ASCII:
            ASCIIEncoding encoding = new ASCIIEncoding();
            return encoding.GetString(property.Value, 0, property.Len - 1);
          //BYTE
          case PropertyTagType.Byte:
            if (property.Len == 1)
              return property.Value[0];
            else
              return property.Value;
          //LONG
          case PropertyTagType.Long:
            uint[] resultLong = new uint[property.Len / 4];
            for (int i = 0; i < resultLong.Length; i++)
              resultLong[i] = BitConverter.ToUInt32(property.Value, i * 4);
            if (resultLong.Length == 1)
              return resultLong[0];
            else
              return resultLong;
          //SHORT
          case PropertyTagType.Short:
            ushort[] resultShort = new ushort[property.Len / 2];
            for (int i = 0; i < resultShort.Length; i++)
              resultShort[i] = BitConverter.ToUInt16(property.Value, i * 2);
            if (resultShort.Length == 1)
              return resultShort[0];
            else
              return resultShort;
          //SLONG
          case PropertyTagType.SLONG:
            int[] resultSLong = new int[property.Len / 4];
            for (int i = 0; i < resultSLong.Length; i++)
              resultSLong[i] = BitConverter.ToInt32(property.Value, i * 4);
            if (resultSLong.Length == 1)
              return resultSLong[0];
            else
              return resultSLong;
          //RATIONAL
          case PropertyTagType.Rational:
            Fraction[] resultRational = new Fraction[property.Len / 8];
            uint uNumerator;
            uint uDenumerator;
            for (int i = 0; i < resultRational.Length; i++)
            {
              uNumerator = BitConverter.ToUInt32(property.Value, i * 8);
              uDenumerator = BitConverter.ToUInt32(property.Value, i * 8 + 4);
              resultRational[i] = new Fraction(uNumerator, uDenumerator);
            }
            if (resultRational.Length == 1)
              return resultRational[0];
            else
              return resultRational;
          //SRATIONAL
          case PropertyTagType.SRational:
            Fraction[] resultSRational = new Fraction[property.Len / 8];
            int sNumerator;
            int sDenumerator;
            for (int i = 0; i < resultSRational.Length; i++)
            {
              sNumerator = BitConverter.ToInt32(property.Value, i * 8);
              sDenumerator = BitConverter.ToInt32(property.Value, i * 8 + 4);
              resultSRational[i] = new Fraction(sNumerator, sDenumerator);
            }
            if (resultSRational.Length == 1)
              return resultSRational[0];
            else
              return resultSRational;
          //UNDEFINE
          default:
            if (property.Len == 1)
              return property.Value[0];
            else
              return property.Value;
        }
      }
    }

    public class EXIFReader
    {
      #region EXIFReader constructors

      public EXIFReader(Image image)
      {
        this.image = image;
        props = image.PropertyItems;
      }

      public EXIFReader(string path)
      {
        using (FileStream fs = new FileStream(path, FileMode.Open))
        {
          this.image = Image.FromStream(fs);
          props = image.PropertyItems;
        }
      }

      public EXIFReader(Stream stream)
      {
        this.image = Image.FromStream(stream);
        props = image.PropertyItems;
      }

      #endregion

      #region Image

      public Image image;
      private PropertyItem[] props;

      #endregion

      #region EXIF property indexers

      public object this[int Id]
      {
        get
        {
          try
          {
            PropertyItem property = props.Where(x => x.Id.ToString().Equals(Id.ToString())).FirstOrDefault();
            return PropertyTagValue.GetValueObject(property);
          }
          catch
          {
            return null;
          }
        }
      }

      public object this[PropertyTagId TagId]
      {
        get
        {
          try
          {
            PropertyItem property = props.Where(x => x.Id.ToString().Equals(((int)TagId).ToString())).FirstOrDefault();
            return PropertyTagValue.GetValueObject(property);
          }
          catch
          {
            return null;
          }
        }
      }

      #endregion
    }

  }
}

Tags:

C#

iTunes iPhone Backup Location - Set Alternate Folder Path

by 27. August 2011 13:21

When using iTunes to backup your iPhone it will typically store all the data (pictures, videos, etc.) in the following location: 

Old Style:  C:\Documents and Settings\USERNAME\Application Data\Apple Computer\MobileSync\Backup

New Style: C:\Users\jmpadmin\AppData\Roaming\Apple Computer\MobileSync\Backup

Apparently, there is no way to change this path through the iTunes configuration options.  When using a Windows based PC you can however use the Microsoft SysInternals "Junction.exe" program to create a pointer from one file path to another.  The command line example below would point to another drive.  In that example any files written to the Apple Backup folder on the C: drive will actually be stored in the folder on the E: drive.  This can be useful when (as in my case) the C: drive is nearly full and you want to store all that backup data on an external hard drive (in this example the E: drive represents an external hard drive).

1) Delete the "Backup" folder from inside the "MobileSync" folder (make sure to make a copy of whatever is inside the folder if you have already used iTunes on the computer).

2) Create a new "Backup" folder in your desired target location (copy your original files back into this new location).

3) Run the following command from a command prompt (note, you must the the Junction.exe executable located in the folder your command line is running from):

junction.exe  "C:\Users\USERNAME\AppData\Roaming\Apple Computer\MobileSync\Backup" D:\NEWFOLDERLOCATION

NOTE: You can also use MKLink command line that works similarly to Junction

Tags:

Convert C# DataTable to Delimited File

by 22. August 2011 16:11

 

public static DataTable GetDataTableFromCsv(string FilePath)
{
  return GetDataTableFromDelim(FilePath, ",");
}
public static DataTable GetDataTableFromTab(string FilePath)
{
  return GetDataTableFromDelim(FilePath, @"\t");
}
public static DataTable GetDataTableFromDelim(string FilePath, string Delim)
{
  DataTable tbl = new DataTable();
  string CsvData = string.Empty;
  CsvData = File.ReadAllText(FilePath);
  bool firstRow = true;
  foreach (string row in CsvData.Split("\n".ToCharArray()))
  {
    DataRow dr = tbl.NewRow();
    System.Text.RegularExpressions.RegexOptions options = (
        System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace
      | System.Text.RegularExpressions.RegexOptions.Multiline
      | System.Text.RegularExpressions.RegexOptions.IgnoreCase);
    Regex reg = new Regex(Delim 
      + @"(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))", options);
    var csvArray = reg.Split(row.Replace("\n", "").Replace("\r", ""));
    for (int i = 0; i < csvArray.Length; i++)
    {
      csvArray[i] = csvArray[i].Replace("\"\"", "");
      if (firstRow)
        tbl.Columns.Add(new DataColumn() { ColumnName = csvArray[i] });
      else
        dr[i] = csvArray[i];
      Console.WriteLine(csvArray[i]);
    }
    if (!firstRow && !dr[0].ToString().Length.Equals(0)) tbl.Rows.Add(dr);
    firstRow = false;
  }
  return tbl;
}

private static string DelimTextFromTable(DataTable tbl, string delim)
{
  var sb = new StringBuilder();
  sb.AppendLine(string.Join(delim,
    tbl.Columns.Cast<DataColumn>().Select(arg => arg.ColumnName)));
  foreach (DataRow dataRow in tbl.Rows)
    sb.AppendLine(string.Join(delim,
      dataRow.ItemArray.Select(arg => arg.ToString())));
  return sb.ToString();
}

Tags:

C#

Create a Simple Windows Service to Send Daily Email with Dynamically Created .CSV Attachment

by 17. August 2011 10:28

The sample code below shows how to setup a simple Windows service that will send a daily email with a dynamically generated .CSV file (built by querying a SQL database).  After you compile the program you can using these commands to setup and delete the service (to update the service you will delete and re-install):

  • sc create MyServiceName binpath= "C:\PathToExecutable\Executable.exe"
  • sc delete MyServiceName
using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Net.Mail;
using System.ServiceProcess;
using System.Text;

namespace CrmRatesheetDistributionListEmailer
{
  public partial class Emailer : ServiceBase
  {
    public Emailer()
    {
      InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
      BackgroundWorker wkr = new BackgroundWorker();
      wkr.DoWork += DailyEmailManager;
      wkr.RunWorkerAsync();
    }

    private void DailyEmailManager(object sender, DoWorkEventArgs e)
    {
      DateTime lastRun = DateTime.MinValue;
      do
      {
        if (lastRun.DayOfYear.Equals(DateTime.Today.DayOfYear))
        {
          System.Threading.Thread.Sleep(60 * 1000);
          continue;
        }
        lastRun = DateTime.Today;
        SmtpClient mail = new SmtpClient("mailrelay.mydomain.com");
        MailMessage msg = new MailMessage("fromme@mydomain.com", "toyou@yourdomain.com", "Sample Daily Email", "This is a sample windows service that will send a daily email.");
        msg.CC.Add(new MailAddress("someoneelse@yourdomain.com"));
        msg.Attachments.Add(new Attachment(GetAttachment()));
        mail.Send(msg);
      } while (true);
    }

    private string GetAttachment()
    {
      SqlConnection cn = new SqlConnection("Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=true;");
      SqlCommand cmd = new SqlCommand("select * from SomeTable where SomeCritera = true", cn);
      DataTable tbl = new DataTable();
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      da.Fill(tbl);
      string fileName = "c:\\temp\\SomeFileName.csv";
      System.IO.StreamWriter file = new System.IO.StreamWriter(fileName);
      file.Write(TableToCSV(tbl));
      file.Close();
      return fileName;
    }

    private static string TableToCSV(DataTable table)
    {
      var result = new StringBuilder();
      for (int i = 0; i < table.Columns.Count; i++)
      {
        result.Append(table.Columns[i].ColumnName);
        result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
      }
      foreach (DataRow row in table.Rows)
      {
        for (int i = 0; i < table.Columns.Count; i++)
        {
          result.Append(row[i].ToString());
          result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
        }
      } return result.ToString();
    }

    protected override void OnStop()
    {
    }
  }
}

Tags:

C#

Customize MS CRM Marketing List Members Default View

by 7. July 2011 15:25

I wanted to customize the Marketing List Members default view, specifically just to add the EMail address column.  The default view was originally three columns: Full Name, Business Phone and Address 1: City.  I assumed this would be accomplished by modifying the Contact entity; specifically the "Contact Associated View".  When I opened that view it did in fact seem to match the Marketing List Members default view; however, after modifying and publishing the Contact Assoiated View, the Marketing List Members view was unaffected.  Upon further inspection I noticed the icon of the Marketing List Members was different than that of the Contacts which led me to believe that I was working with the correct entity.  However, I could not find any entity in the Customize Entities screen that had th same unique icon as the Marketing List Members.  As it turns out although you cannot modify the Marketing List Members default view through the Customize Entities screen, there is a workaround that will allow you to customize it. 

Follow the steps below:

  1. From the main CRM screen perform an "Advanced Find".
  2. In the "Look for:" dropdown select "View".
  3. In the details section dropdown select "View" from the "Fields" section of the dropdown values.
  4. Set the operator to "Equals" (which should be the default).
  5. In the "Enter Value" field type "All Members" and/or select from the magnifier button pop-up screen.  NOTE: there may be more than one "All Members" (in my case there were three), so make sure to select all of them.
  6. Click the "Find" button.
  7. Double-click the result rows (one at a time) and modify the view when it opens.
  8. Click "Save and Close", repeat as necessary for each of the views.
  9. IMPORTANT: The changes you made will not be visible until you go back to the Customize Entities screen and select "More Actions" > "Publish All Customizations" from the menu at the top of the entity list.

Tags:

MS CRM

Customize MS CRM Marketing List Members Default View

by 7. July 2011 15:05

I wanted to customize the Marketing List Members default view, specifically just to add the EMail address column (the default view was originally just Full Name, Business Phone and Address 1: City).  I origially assumed that this would b accomplished by modifying the Contact entities "Contact Associated View".  When I opened that view it did in fact seem to match the Marketing List Members default view; however, after modifying and publishing the Contact Assoiated View, the Marketing List Members view was unaffected.  Upon further inspection I noticed the icon of the Marketing List Members was different than that of the Contacts and in fact I could not find any entity in the Customize Entities screen that had that same unique icon.  As it turns out you cannot modify the Marketing List Members default view through the Customize Entities screen.  There is however a workaround that allow you to customize it.  Follow the steps below:

  1. From the main CRM screen perform an "Advanced Find".
  2. In the "Look for:" dropdown select "View".
  3. In the details section dropdown select "View" from the "Fields" section of the dropdown values.
  4. Set the operator to "Equals" (which should be the default).
  5. In the "Enter Value" field type "All Members" and/or select from the magnifier button pop-up screen.  NOTE: there may be more than one "All Members" (in my case there were three), so make sure to select all of them.
  6. Click the "Find" button.
  7. Double-click the result rows (one at a time) and modify the view when it opens.
  8. Click "Save and Close", repeat as necessary for each of the views.
  9. IMPORTANT: The changes you made will not be visible until you go back to the Customize Entities screen and select "More Actions" > "Publish All Customizations" from the menu at the top of the entity list.

Tags:

MS CRM

Forgot SQL Server Login

by 27. June 2011 08:52
On my current computer I had installed SQL Server 2008 locally some time back and forgot the "sa" password I had assigned at the time of installation and could not find it documented anywhere.  Also, it seems I did not add my windows account as an administrator as I could not login with integrated security even using the windows account that has local administrator access on the machine.  To solve this problem, I stopped the SQL service, added the "single user mode" parameter to the startup parameters, restarted the service, and changed the "sa" password using a SQL query window (SQL command shown below).
To add the "single user mode" startup parameter, open the "Sql Server Configuration Manager", right click on the "SQL Server (INSTANCENAMEHERE)" service and select "Properties", then click on the "Advanced" tab, scroll down to the "Startup Parameters" and add "-m;" (without the quotes) to the front of the existing parameters.
Restart the service, then through Sql Management Studio, open a new query window and change the connection string to your Sql Instance and run the following commands.  Note: You can put whatever new password you want in the @new parameter.  Once that is finished, go back and remove the "-m;" from the startup parameters, restart the service and login with your "sa" account and the new password you assigned.
exec sp_password @new='NewPasswordHere', @loginame='sa' 
go 
alter login sa 
enable 
go
Alternatively, if you cannot even login to the server, perform the following:
  1. Start the instance of SQL Server in single-user mode by using either the -m; or -f; options.
  2. Start command prompt as local administrator
  3. Connect to the server: sqlcmd -E -S SERVER_NAME_HERE
  4. Add yourself (computername\username) to the sysadmin SQL group with the following commands:
    • create login [computername\username] from windows;
    •  exec sp_addsrvrolemember 'computername\username', 'sysadmin';
  5. Restart the server in normal mode

Tags:

SQL Server

Get User that Initiated Task Change Event on a Workflow Task in Sharepoint

by 17. June 2011 11:13

This topic covers Sharepoint workflow tasks.  Specifically, the how to get the user account that triggered the workflow event.  By using the typical CurrentUser method on the Web object you will typically get the SHAREPOINT\system account rather than the actual account of the user that initiated the event.  Instead you can use the OriginatorUser method of the Workflowproperties as shown below.

// In both cases below you will typically get "SHAREPOINT\system"
Workflowproperties.web.CurrentUser;
// or
SPContext.Current.Web.CurrentUser;

// To get the acutal user that initiated task change event use this:
Workflowproperties.OriginatorUser

 

Tags:

C# | Sharepoint

Update Sharepoint Task Item inside Workflow Causes Item to be Indefinitely Locked by Workflow

by 17. June 2011 10:31

This topic covers updating a Sharepoint workflow task item, inside of a workflow event.  Sometimes performing an update to the item inside the workflow event will cause the item to be seen as indefinitely locked by the workflow in which case future attempts to update the same item (either inside a workflow event or by a user attempting to edi the item directly through the Sharepoint front end) will result in this error: "This task is currently locked by a running workflow and cannot be edited.". 

Typically, this will happen if you use the basic Update method of the item object (as shown in sample code below).  However, you should instead use the SystemUpdate method along with the explicit setting of the WorkflowVersion field value to 1 (as shown in sample code below).

/* You have to include the following two lines before the call to item.SystemUpdate()   
* to avoid this error:    
* "This task is currently locked by a running workflow and cannot be edited."   
* For details see:    
* http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splistitem.update%28v=office.12%29.aspx   
*/
web.AllowUnsafeUpdates = true; 
// You have to include this line  
item[SPBuiltInFieldId.WorkflowVersion] = 1;  
item.SystemUpdate(false);  // Don't use: item.Update(); 
//

Tags:

C# | Sharepoint

Sharepoint Hidden Site User List Collection

by 14. June 2011 10:48

Logged in as administrator, navigate to: _catalogs/users/simple.aspx

see: http://www.zimmergren.net/archive/2008/06/25/sharepoints-hidden-user-list-user-information-list.aspx

Tags:

Sharepoint

Sharepoint 2010 Get SPListItem with C# LINQ Syntax

by 14. June 2011 10:31

using (SPWeb web = new SPSite(workflowProperties.Web.Url).OpenWeb())

{

  SPListItem itm = (from x in web.Lists["List Name"].Items.OfType<SPListItem>()

                    where x["Field Name"] != null

                       && x["Field Name"].Equals("Value to search for")

                    select x).ToList<SPListItem>().FirstOrDefault();

}

Tags:

Deploy Visual Studio 2010 Sharepoint 2010 Workflow via .WSP File and STSADM

by 9. June 2011 10:04

NOTE:
Make sure the "Sharepoint 2010 Administration" service is NOT running.
Using execadmsvcjobs to execute in lieu of Timer service running them.
This ensures that the prior steps are complete before running the next.

Generate .WSP file by using the "Package" command from Visual Studio.

Copy .WSP file to somewhere on the Sharepoint server
for this example: "c:\MySpDeployment\MyWorkflow.wsp"
Run command window as Administrator and execute the commands below.

NOTE:
If you are re-installing the workflow you can first fully remove it
by using the Uninstall steps shown below the Install steps.

INSTALL STEPS

1) Navigate to the BIN where the STSADM.EXE exists
   (so we don't need the full path on all commands)

cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

2) Run all the STSADM commands...
stsadm -o addsolution -filename "MySpDeployment\MyWorkflow.wsp"
stsadm -o deploysolution -name "MyWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies
stsadm -o execadmsvcjobs
stsadm -o activatefeature -name "MyWorkflow" -url http://yoursharepointsite

NOTE: 
Last step to activate feature requires exact folder name of feature.
Verify by navigating to the features folder to confirm the folder name.
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES

UNINSTALL STEPS

1) Navigate to the BIN where the STSADM.EXE exists
   (so we don't need the full path on all commands)

cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

NOTE: 
First step to deactivate feature requires exact folder name of feature.
Verify by navigating to the features folder to confirm the folder name.
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES

2) Run all the STSADM commands...
stsadm -o deactivatefeature -name "MyWorkflow" -url http://devuam
stsadm -o retractsolution -name "MyWorkflow.wsp" -immediate
stsadm -o execadmsvcjobs
stsadm -o deletesolution -name "MyWorkflow.wsp"

Tags:

Sharepoint

Deploy Visual Studio 2010 Sharepoint 2010 Workflow via .WSP File and STSADM

by 9. June 2011 09:37

NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.
      We are using the execadmsvcjobs to execute rather than letting th Timer service run them.

To install workflow from .WSP file ("Package" command from Visual Studio 2010 generates this file)
Copy the .WSP file to somewhere on the Sharepoint server (c:\MySpDeployment\MyWorkflow.wsp for this example)
Running command window as Administrator execute the commands below.
NOTE: If you are re-installing the workflow you can first fully remove it with the Uninstall steps also shown below.

INSTALL STEPS

1) Navigate to the BIN where the STSADM.EXE exists (so we don't need the full path on all commands)..
cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

2) Run all the STSADM commands...
stsadm -o addsolution -filename "MySpDeployment\MyWorkflow.wsp"
stsadm -o deploysolution -name "MyWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies
stsadm -o execadmsvcjobs
stsadm -o activatefeature -name "MyWorkflow" -url http://devuam

NOTE: The last step to activate the feature requires the exact folder name where the feature was installed.
      You can verify by navigating to the features folder (listed below) and confirming the folder name.
      (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES

UNINSTALL STEPS

1) Navigate to the BIN where the STSADM.EXE exists (so we don't need the full path on all commands)..
cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

2) Run all the STSADM commands... (see NOTE from last step of install that also applies to first step of removal)
stsadm -o deactivatefeature -name "MyWorkflow" -url http://devuam
stsadm -o retractsolution -name "MyWorkflow.wsp" -immediate
stsadm -o execadmsvcjobs
stsadm -o deletesolution -name "MyWorkflow.wsp"

Tags:

Sharepoint

C# ArrayList to Delimited String

by 31. May 2011 09:18
// Convert ArrayList to comma delimited string...
string myListString = string.Join(","
        , ((myArrayList.ToArray(typeof(string)) as string[]);

Tags:

C#

LDAP Query to Find a Users Manager Using C#

by 28. May 2011 11:46

Method 1:

DirectoryEntry deUserRootEntry = new DirectoryEntry("LDAP://impaccompanies.com");
DirectorySearcher dsUserSearcher = new DirectorySearcher();
dsUserSearcher.SearchRoot = deUserRootEntry;
//dsUserSearcher.Filter = "name=User Name";
dsUserSearcher.Filter = "userprincipalname=userid@domain.com";
if (dsUserSearcher.FindOne().GetDirectoryEntry() != null)
{
  DirectoryEntry deUser = dsUserSearcher.FindOne().GetDirectoryEntry();

  if (dsUserSearcher.FindOne().GetDirectoryEntry().Properties["manager"].Value != null)
  {
    ///you get manager here...
  }
}

Method 2:

using System.DirectoryServices;
using System.DirectoryServices.AccountManagement;

// Get specific user in domain...
PrincipalContext ctx = new PrincipalContext(ContextType.Domain, "domain.com");
UserPrincipal usp = UserPrincipal.FindByIdentity(ctx, "userid@domain.com");

// Call the GetManager method and get the managers user id...
UserPrincipal mgp = GetManager(ctx, usp);
System.Diagnostics.Debug.WriteLine(mgp.UserPrincipalName);

// Method to find the manager for a specific user...
public UserPrincipal GetManager(PrincipalContext ctx, UserPrincipal user)
{
  if (user != null)
  {
    // Get the DirectoryEntry object of the UserPrincipal object
    var dirEntryForUser = user.GetUnderlyingObject() as DirectoryEntry;

    if (dirEntryForUser != null)
    {
      // See if the user has a manager assigned and if so return it...
      if (dirEntryForUser.Properties["manager"] != null 
          && dirEntryForUser.Properties["manager"].Count > 0)
      {
        string mgrDN = dirEntryForUser.Properties["manager"][0].ToString();
        // Get the manager UserPrincipal via the DN 
        return UserPrincipal.FindByIdentity(ctx, mgrDN);
      }
    }
  }
  return null;
}

Tags:

C#

LDAP Query to Find a Users Manager Using C#

by 28. May 2011 11:28

using System.DirectoryServices;
using System.DirectoryServices.AccountManagement;

// Get specific user in domain...
PrincipalContext ctx = new PrincipalContext(ContextType.Domain, "domain.com");
UserPrincipal usp = UserPrincipal.FindByIdentity(ctx, "userid@domain.com");

// Call the GetManager method and get the managers user id...
UserPrincipal mgp = GetManager(ctx, usp);
System.Diagnostics.Debug.WriteLine(mgp.UserPrincipalName);

// Method to find the manager for a specific user...
public UserPrincipal GetManager(PrincipalContext ctx, UserPrincipal user)
{
  if (user != null)
  {
    // Get the DirectoryEntry object of the UserPrincipal object
    var dirEntryForUser = user.GetUnderlyingObject() as DirectoryEntry;

    if (dirEntryForUser != null)
    {
      // See if the user has a manager assigned and if so return it...
      if (dirEntryForUser.Properties["manager"] != null 
          && dirEntryForUser.Properties["manager"].Count > 0)
      {
        string mgrDN = dirEntryForUser.Properties["manager"][0].ToString();
        // Get the manager UserPrincipal via the DN 
        return UserPrincipal.FindByIdentity(ctx, mgrDN);
      }
    }
  }
  return null;
}

Tags:

C# | LDAP

LDAP Queries

by 28. May 2011 09:03
DirectoryEntry deUser = new DirectoryEntry("LDAP://cn=John Doe,cn=Users,dc=YourCorp,dc=com");

if(deUser != null)
{
   
// check if the manager property is set - it could be NULL (no manager defined)
   
if(deUser.Properties["manager"] != null)
   
{
     
string managerDN = deUser.Properties["manager"][0].ToString();
   
}
}

Properties:
[0]: "homemdb"

    [1]: "countrycode"

    [2]: "cn"

    [3]: "msexchuseraccountcontrol"

    [4]: "mailnickname"

    [5]: "msexchhomeservername"

    [6]: "msexchhidefromaddresslists"

    [7]: "msexchalobjectversion"

    [8]: "usncreated"

    [9]: "objectguid"

    [10]: "msexchrequireauthtosendto"

    [11]: "whenchanged"

    [12]: "memberof"

    [13]: "accountexpires"

    [14]: "displayname"

    [15]: "primarygroupid"

    [16]: "badpwdcount"

    [17]: "objectclass"

    [18]: "instancetype"

    [19]: "msmqdigests"

    [20]: "objectcategory"

    [21]: "samaccounttype"

    [22]: "whencreated"

    [23]: "lastlogon"

    [24]: "useraccountcontrol"

    [25]: "msmqsigncertificates"

    [26]: "samaccountname"

    [27]: "userparameters"

    [28]: "mail"

    [29]: "msexchmailboxsecuritydescriptor"

    [30]: "adspath"

    [31]: "lockouttime"

    [32]: "homemta"

    [33]: "description"

    [34]: "msexchmailboxguid"

    [35]: "pwdlastset"

    [36]: "logoncount"

    [37]: "codepage"

    [38]: "name"

    [39]: "usnchanged"

    [40]: "legacyexchangedn"

    [41]: "proxyaddresses"

    [42]: "userprincipalname"

    [43]: "admincount"

    [44]: "badpasswordtime"

    [45]: "objectsid"

    [46]: "msexchpoliciesincluded"

    [47]: "mdbusedefaults"

    [48]: "distinguishedname"

    [49]: "showinaddressbook"

    [50]: "givenname"

    [51]: "textencodedoraddress"

    [52]: "lastlogontimestamp"

Tags: