SQL Server: How to get instance level configuration details

By peter.stilgoe

A useful script to return you instance level SQL configuration settings:

FROM    sys.configurations
ORDER BY name ;

More information about the script and other useful scripts How to document & configure sql server instance settings


categoriaSQL Server, SQL Server 2008 commentoComments Off dataFebruary 13th, 2012
Leggi tutto

How to create an Excel Workbook from an SQL database using Open XML SDK 2.0

By peter.stilgoe

Below is the C# code to create a new Excel workbook based on a template file & populated by an SQL database.

You need to add references to:


using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ElitetoExcel
    class Program
        static string[] headerColumns =
            new string[] { "A", "B", "C", "D", "E"};

        static void Main(string[] args)
            //Make a copy of the template file
            File.Copy(@"c:\templatedoc.xlsx", @"c:\newdoc.xlsx", true);

            //Open up the copied workbook
            using (SpreadsheetDocument myWorkbook =
                SpreadsheetDocument.Open(@"c:\newdoc.xlsx", true))
                //Access the main workbook part which contains all the references
                WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                //Grab the first worksheet
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

                //Sheet data will contain all the data
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //Connect to Database LINQ
                DataClasses1DataContext = new
                int index = 2;

                //select * from SPOpenMatters table
                var MatterQuery = from t in DataClasses1DataContext.SPOpenMatters
                                     select t;

                //For each record in database add row in spreadsheet

                Console.WriteLine("Creating Excel report...........");

                foreach (var item in MatterQuery)
                    string MatterNum = item.matterno;
                    string Description = item.description;
                    string OpenedDate = item.openeddate.ToString();
                    string MatterStatus = item.matterstatus;
                    string BillingTKPR = item.billingTKPR;

                    //Add a new row
                    Row contentRow =
                        CreateContentRow(index, MatterNum, Description, OpenedDate, MatterStatus, BillingTKPR);

                    //Append new row to sheet data

        private static Row CreateContentRow(int index, string MatterNum, string Description, string OpenedDate, string MatterStatus, string BillingTKPR)
            //Create new row
            Row r = new Row();
            r.RowIndex = (UInt32)index;

            //First cell is a text cell, so create it and append it
            Cell firstCell =
                CreateTextCell(headerColumns[0], MatterNum, index);

            //create cells that contain data
            for (int i = 1; i < headerColumns.Length; i++)
                Cell c = new Cell();
                c.CellReference = headerColumns[i] + index;
                CellValue v = new CellValue();
                if (i == 1)

                    v.Text = Description.ToString();

                if (i == 2)
                    v.Text = Convert.ToDateTime(OpenedDate).ToString("D");

                if (i == 3)
                    v.Text = MatterStatus.ToString();

                if (i == 4)
                    v.Text = BillingTKPR.ToString();


            return r;

        private static Cell CreateTextCell(string header,
            string MatterNum, int index)
            //Create a new inline string cell
            Cell c = new Cell();
            c.DataType = CellValues.InlineString;
            c.CellReference = header + index;

            //Add text to text cell
            InlineString inlineString = new InlineString();
            Text t = new Text();
            t.Text = MatterNum;
            return c;


Leggi tutto

Sharepoint 2010: Database doesnt exist but sill appears in Central Admin –> Manage Databases Upgrade Status

By peter.stilgoe

If you have deleted your database in SQL but they sill appear in Central Admin –> Manage Databases Upgrade Status, you can use the following Powershell script to remove them:

$db = Get-SPDatabase | where{$_.Name -eq "YourDatabaseName"}



Leggi tutto

Populate a Sharepoint list from an SQL Database

By peter.stilgoe

If you are wanting to populate a Sharepoint list from an SQL database or populate a SQL database from a Sharepoint list you can do this failry easily using SSIS & the SharePoint List Source and Destination Adapter project on Codeplex.

This sample illustrates the creation of a a source and destination adapter in C#, using the public APIs for Microsoft SQL Server Integration Services. The component connects to SharePoint through a proxy dll, which wraps the complexities of working with the SharePoint Lists Webservice.

The Specific Features of the included SSIS Components are:

SharePoint Source Component
- Auto-detects all of the public fields from a SharePoint List or view of the list.
- Supports removal of fields from the output flow, which optimizes the query against SharePoint for increased performance.
- Supports restricting SharePoint data by a custom CAML query, which can be provided through a variable expression for fine tuned filtering over large lists.
- Provides a tuning feature for the # of rows to return at a time. Often with SharePoint, large lists need to be brought over in smaller batches than wide (many field) lists, or else the Webservice will die. The batchsize lets you specify a solid size that works for your list.

SharePoint Destination Component
- Auto-detects all of the editable fields from a SharePoint List
- Only fields bound to columns from the input flow will be ‘updated’, for performance.
- Supports deleting rows by ID
Included Bonus – SharePoint Utility

To simplify the adapter code and keep the SharePoint routines generic, a proxy VB.Net component is included which access the SharePoint Webservice API. It includes a very flexible api for getting SharePoint List information, which is used by the Components above. In addition, it can publish and remove files from a document library.

Component Features
Unique features that may inspire you to create great custom SSIS components:
• Extensive Validation: Validation for this component actively goes against the SharePoint Site to verify the properties are valid.
• Values from Expressions: This component supports external variables, which can be expressions, and can be attached to the source component to customize the query. Similar to the CommandText for the other Sql Components
• Linq: The Component has been written using Linq with .net 3.5 and shows how elements such as the metadata and columns can be combined to create a readable usage in a Linq format.
• Custom properties: The component keeps its configuration in custom properties on itself, inputs, and input columns.
• SharePointUtility Component: Simplifies access to the SharePoint Lists / Views WS using Linq friendly objects

Sharepoint List Source & Destination project on Codeplex


categoriaSharepoint Lists, SQL Server, SQL Server 2008, SQL SSIS commentoComments Off dataJanuary 31st, 2011
Leggi tutto

SQL 2008 Server Error HRESULT E_FAIL has been returned from a call to a COM component when browsing the cube

By peter.stilgoe

You get the following error when trying to browse the cube from SQL Managment Studio or from Visual Studio 2008:

Error HRESULT E_FAIL has been returned from a call to a COM component

The error is a bug in SQL 2008 server, it occurs when your server language is different to the language set on the cube ie.

Current Windows Language: English (United States)

From cube browser (VS) > Click “Cube” from menu bar > open “Language” then choose the exact same language (English (United States) in my case) as windows.


categoriaSQL Server 2008, SSAS commento6 Comments dataDecember 2nd, 2010
Leggi tutto

Support for running Microsoft Dynamics CRM 4.0 together with Microsoft SQL Server 2008

By peter.stilgoe

Support for running Microsoft Dynamics CRM 4.0 together with Microsoft SQL Server 2008



categoriaMicrosoft Dynamics CRM, SQL Server 2008 commentoComments Off dataNovember 28th, 2010
Leggi tutto

Platform Error: System.Exception: Action Microsoft.Crm.Setup.Server.RSConfigAction failed

By peter.stilgoe

When installing MS Dynamics CRM 4 on a SQL 2008 server you may get the following error:

“The specified path is not a metabase path.” Platform Error: System.Exception: Action Microsoft.Crm.Setup.Server.RSConfigAction failed.

This issue occurs when the path of Reporting Services does not exist on the server.

To work around this issue, create a Web site that runs on the same port and the same URL that the Reporting Services server is using. In SQL Server 2008 Reporting Services, IIS is not used for accessing reports. However, if Microsoft Dynamics CRM 4.0 is installed on the same server, Microsoft Dynamics CRM 4.0 must find a Web site that has the same URL to bypass the error. To verify the URL of Reporting Services, follow these steps:

Click Start –> click All Programs –> click Microsoft SQL Server 2008 –> click Configuration Tools –> and then click Reporting Services Configuration Manager.

Click connect to connect to your report server instance.

Click the Web service URL, and see what the report server Web service URL is. For example, the URL may be http://SQLServername:8181/ReportServer.

In Windows Server 2008, follow these steps:

Click Start, click Run, and then type inetmgr.

Expand the server name, right-click Sites, and then click Add Website.

Enter a name for the site, and then select a physical path.

Enter the port number of the report server Web service. For example, enter 8181.

Click OK.

In Windows Server 2003, follow these steps:
Click Start, click Run, and then type inetmgr.

Expand the server name, right-click Web Sites, click New, and then click Web Site.

Enter a description for the site, and then click Next.

Enter the port number of the report server Web service. For example, enter 8181, and then click Next.

Enter a path for the Web site, and then click Next.

On the Permissions page, click Next.

Click Finish.


Leggi tutto

K2 Blackpoint now supports Windows 2008 server & SQL Server 2008

By peter.stilgoe

Now K2 blackpoint is supported on Windows Server 2008 and on SQL Server 2008! The KB470 update just released on the K2 customer portal provides this support. KB000470 updates all K2 blackpoint 0902 version 4.9040.1.0 installations with latest product hotfixes. The installer will update K2 blackpoint 0902 32-bit and 64-bit installations.


categoriaK2 Blackpoint commentoComments Off dataSeptember 14th, 2009
Leggi tutto