Technical: Microsoft – SharePoint – v2100 – List – Management – Deletion

Technical: Microsoft – SharePoint – v2010 – List – Management – Deletion


I have a little assignment; and the assignment is to copy a SharePoint List from an old server to a new one.

Knowing me,  I will likely make a lot of false starts and so let me sure I will be able to remove half-way created lists.


Navigate to the Site Home

Let us navigate to our Site’s home.

Thank goodness that SharePoint has Navigational panel for sojourning back to the Site’s home page.

Here is our top panel.


At the top of the page we have the little folder Navigation icon folderNavigation

We will use that icon to take us to the Site’s Home page.

Access List Management

Once on the Site’s home page, we will access the List’s management tools.

Pasted below is our Site’s home page.


The List’s page is accessible by moving our glance to the left sub-panel and clicking on the Lists hyperlink.

Select List

Here is the list of our currently established lists:

List - StandardView - Lists (include friends)

Because of the Site Template we chose when creating our “Site Collection”, we have a few standard lists:

  • Announcements
  • Calendar
  • Links
  • Tasks

The one list that we created is the Friends List.

And, so that is the one we will offer up.

Navigation Tools

More on Navigational Tools.  I think it is important to know where one is in SharePoint. For the sake of consistency and uniformity most of the screens have a familiar and standard look.

So to know where you really are, I will suggest two ploys:

  • Keeping an eye on your URL box
  • Making note of the middle panel

When on the List View panel, our URL reads http://<sharepointserver>:13201/_layouts/viewlsts.aspx.  The important notifier is the document tag that reads viewlsts.aspx.

And, the middle panel reads:


Which to me says that the screen is displaying all sub-sites, lists, and libraries.

List – All Items

Once we chose to go with the Friend’s List, here is where we ended up.

List - Friends - AllItems

Our guide is:

In case we have contents for our lists, they will be noted and listed on our right panel.

List – All Items – Management

To be able to manage the settings on our list, we have to click on the “List Tools/List” icon.

Once we do so, we will see a screen that resembles the screen shot below:

List - Friends - AllItems - Management

List Settings

To manage our List Settings, we will aptly click on the “List Settings” icon.

Upon clicking on the List Settings icon, we are taken to the List Settings page.

List - Friends - ListSettings


And, I think this is where we want to be:

We see familiar options, such as:

  • Title, description, and navigation
  • Versioning Settings
  • Advanced Settings
  • Validation Settings
  • Delete this list
  • Save list as template
  • Permissions for the list
  • Workflow Settings
  • RSS Settings

Obviously, our choice it to go with the “Delete this list” choice.

Delete List

Once the “Delete this List” choice is made, the confirmation box is offered:

Are you sure you want to send this list to the site Recyle Bin?


And, we affirmative say OK.

All Site Content

Once the List is removed, we are returned to the “All Site Content” page.

List - Friends - AllSiteContent


Yes, should have read the manual.  But, unlike my last few posted and best left private posts, did not pay bitterly for not reading the manual.

Now on to reviewing the steps for exporting and importing SharePoint Lists.


Technical: Microsoft – SharePoint 2010 Administration Toolkit – SharePoint Diagnostic Studio – Error – Microsoft SharePoint is not supported with version 4.0.30319.18051 of the Microsoft .Net Runtime

Technical: Microsoft – SharePoint 2010 Administration Toolkit – SharePoint Diagnostic Studio – Error – Microsoft SharePoint is not supported with version 4.0.30319.18051 of the Microsoft .Net Runtime


A week ago or so, I downloaded and installed a fresh copy of Microsoft SharePoint 2010 Foundation Services.

Upon installation everything worked well. But, it appears that as I played around with stsadm, I possibly messed up my install.


As I tried to recover from the problem stated above, one of the tools that I discovered on the Net is Microsoft SharePoint 2010 Administration Toolkit; and the main component from that kit is SharePoint Diagnostic Studio.

In this post, I will list a specific problem we ran into upon trying to use that tool on an MS Windows 2012 box.

Error Message

Upon targeting the SharePoint Diagnostic Studio against my local MS Windows 2012 box, I am getting the message stated below:

Microsoft SharePoint is not supported with version 4.0.30319.18051 of the Microsoft .Net Runtime.

So it seems the that I have to get the Application to target a lower level version of Microsoft .Net:

Possible Fixes

Possible Fixes – Application Configuration File

To do so, I tried creating configuration files:

The configuration files will be placed in our target folder for “SharePoint 2010 Administration ToolKit”.  In our case, the target folder is “E:\Program Files\Microsoft\SharePoint 2010 Administration Toolkit”.

So we create a file that looks like the one below.  Our targeted file is named SPDIAG.EXE, and we will be adding .CONFIG to it and so we end up with SPDIAG.EXE.CONFIG.

The contents of our App Config file is shown below:


The key elements are that we un-checked v4.0, and list v2.0x as the lone supported and required runtime version.

But, still getting the same error. And, so I am getting a bit un-convinced that I am on the right path.  And, I really in need of a miracle here.

SysInternals – Process Explorer

Let us try out SysInternal’s Process Explorer and see what it says about our Application’s running environment.

Please start Process Explorer as a Administrator

Once SysInternal’s Process Explorer is up, look for the SPDIAG.EXE process.

Once you focus in on SPDIAG.EXE, you can transverse each tab and get specific sets of data:

SPDIAG.EXE – Properties – Image


SPDIAG.EXE – Properties – .Net Assemblies



  • On the left tab, the Structure column shows that we are using CLR v2.0.50727
  • And, the right tab, specifically the Path column shows that the files are being sourced from “C:\Windows\assembly\GAC_64\mscorlib\2.000_ ” and “C:\Windows\assembly\NativeImages_v2.0.50727_64”


So though SPDIAG is target CLR v2.x, we are still failing….

SysInternals – Process Explorer

Again running as an Administrator, launch Process Explorer and filter our tangential processes.

Here are pertinent events:

  • Operation:- RegOpenKey\
    Path :- HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\powershell.exe
  • Operation :- ProcessCreate
    Path :- C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
    Command line: powershell  -command “& {& ‘C:\Users\<computer-name>\Documents\SharePoint Diagnostic Studio\Server Extensions\RemoteInstall-ExtendedDiagnosticProviders.ps1’ <computer-name>}”
  • Operation :- ProcessStart
    Path :-
    Command line:
    powershell  -command “& {& ‘C:\Users\<username>\Documents\SharePoint Diagnostic Studio\Server Extensions\RemoteInstall-ExtendedDiagnosticProviders.ps1’ COMFORT}”
    Environment :

    	=E:=E:\Program Files\Microsoft\SharePoint 2010 Administration Toolkit
    	CommonProgramFiles=C:\Program Files\Common Files
    	CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
    	CommonProgramW6432=C:\Program Files\Common Files
    	LOGONSERVER=\\<AD Server>

Here are the relevant screen-shots:


Looking to determine whether an ImageFileExecutionOptions are defined in the registry for PowerShell.exe


Process Create:

Reviewing what command line options are passed to Powershell.exe when the process is being created:

Here they are:

  •  -command “& {& ‘C:\Users\<username>\Documents\SharePoint Diagnostic Studio\Server Extensions\RemoteInstall-ExtendedDiagnosticProviders.ps1’ <computer-name>}”
  • So we have the name of the PowerShell script to invoke — RemoteInstall-ExtendedDiagnosticProviders.ps1

Process Create – Tab – Event


Process Create – Tab – Process


Process Start:

  • The environment options details all the environment options that are either created a-new or passed along from the invoking-parent’s process



From the screen shot above, we can tell that that the lone argument that is passed to PowerShell is the -command argument.

Our required/supported CLR version 2 is not being passed along.


Overall Conclusion

As  SharePoint Diagnostic Studio predates MS Windows 2012 it rightfully does not consider the new PowerShell v3.0’s graceful ability to expose PowerShell v2.0 via command line arguments.

In later posts will discuss other failed attempts!

PowerShell / Microsoft .Net CLR Version


A week or so ago, I tried running Microsoft SharePoint 2010 Administration Toolkit on an MS Windows box.

I ran into problems due to the SharePoint diagnostic tool not being compatible with the default PowerShell/.Net Framework combo on the Windows 2012 box.

Here is the first in a series of post on the school yard beating I took.


In this post, I will limit our discussion to ensuring that our pre-requisites are installed and functioning.   Those pre-requisites are Microsoft PowerShell and Microsoft .Net v4. and v3.5.

Confirm installation of Microsoft .Net 3.5 & PowerShell 2.0 Engine

Courtesy of (, here are the install\confirmation steps:

Add Microsoft .Net 3.5

  • In Server Manager, from the manage menu, select the Add Roles and Features
  • Select the Server and click on the “Add Roles and Features” option
  • On the Installation Type page, select “Role-based or feature-based” installation
  • On the features page, expand the .Net 3.5 Framework features and select .Net Framework 3.5 (includes .Net 2.0 and 3.0)


To add the Windows PowerShell 2.0 Engine feature

  • In Server Manager, from the manage menu, select the Add Roles and Features
  • Select the Server and click on the “Add Roles and Features” option
  • On the Installation Type page, select “Role-based or feature-based” installation
  • On the features page, expand the “PowerShell (installed)” option and select the “Windows PowerShell 2.0 Engine” option


Get OS/CLR Version

To get OS/CLR Version within PowerShell, we can try the following commands:

  • [System.Environment]::OSVersion.Version
  • (Get-WmiObject -class Win32_OperatingSystem).Caption
  • $PSVersionTable

The most important for our current discussion is the $PSVersionTable command:





On MS Windows 2008/R2 box, you will see:


On MS Windows 2012, you will see:


On MS Windows 2012, if your application demands and requires Version 2, then based on the example documented below, add “-version 2” as a command line argument when starting PowerShell

Syntax Screen:


So we issue :


powershell -version 2

And, replay the Version and CLR Info we previously mentioned:




Here is a quick rundown of OS, PowerShell Version support along with Microsoft.Net CLR support and thereof backward compatibility.

OS PSVersion CLRVersion CLR Version Backward
Win2008-R2  2.0 2.0.50727.572 {1.0,2.0}
Win2012  3.0 4.0.30319.18051 {1.0,2.0,3.0}
Win2012 (-v2)  2.0 2.0.50727.6407 {1.0, 2.0}

System Info Tools

As said, PowerShell loads Microsoft.Net dll depending on the OS Version and/or command line parameters.

One popular tool for reviewing system information and activities are of-course SysInternals set of tools.

Windows 2012

In the screen-shot above, we initiated PowerShell on an MS Windows 2012, and did not pass in any command-line parameter:


Here are the Microsoft .Net Dlls loaded:


From the screen-shot above, we can see the following:

  • on the left panel, we can see that we are on CLR v4.0
  • On the right panel, we have the listing on .Net 4.0 native assemblies loaded

Windows 2012 (passed in -version 2.0)

The screen below indicates what things look like when we pass in the “-version 2.0” option; please pay attention to the “command line” section.


And, here is CLR Version & DLL Listings


So we are able to confirm that when we pass in the “-version 2.0” option, we get PowerShell to go back in time.


In follow-up posts, I will discuss the actual problems I ran into and the series of fruitless steps I tried to correct it.


References – Determine Version#

References – PowerShell 2.0 Engine

Microsoft – SharePoint (v2010) – SharePoint Foundation – Get List of Products Installed along with Version#

Microsoft – SharePoint (v2010) – SharePoint Foundation – Get List of Products Installed along with Version#

Which Products are installed?

Using GUI

  • Launch SharePoint 2010 Central Administration
  • Access the default screen
  • Under “upgrade and migration”, select “Check product and patch installation status”
  • The “Manage Patch Status” screen is displayed
  • On that screen you should be able to view products are installed and their corresponding version#

Using PowerShell

PowerShell Query:

get-SPFarm | select Servers, Name, DisplayName, Status, BuildVersion

PowerShell Output:



Or do one better and try out someone else’s code:

  • I like Matthias_Einig (RENCORE AB) code and it is available @
  • Please download and extract the compressed file
  • Launch Terminal Mode (Command Line Shell – cmd.exe); preferable as an Administrator
  • Using cd <extracted-foldername>, Change directory to the folder where you placed your files
  • Launch Powershell (powershell.exe)
  • Unfortunately, Matthias’ code is not digitally signed and you will have to tell your system it is OK to run it
  • In Powershell, you indicate so by issuing “Set-ExecutionPolicy unrestricted”
  • Run Matthias’s script (note that you have to enter the file’s full name or if you in that folder, place “.\” prior to your file name

In our case, we got



Yes, it is much easier to use the GUI.  But, you might want to get comfortable with the SharePoint Command-Line Tool (stsadm.exe) and the PowerShell Snapin (Microsoft.SharePoint.PowerShell Snap-In).

And, yes GUI’s quickly and readily convey information in easily accessible forms, but the backplane is where all the instrumentation and hard-lifting occurs.


Microsoft – SQL Server Provisioning for MS SharePoint


Goggled for things to do and not to do when setting up SharePoint Databases.  And, found a good list here.

I was spirited to script out SQL Scripts against some of the items listed.

Items Identified

Here is a quick list of items identified:

  • Set “max degree of parallelism” to 1
  • Set database files to auto-grow by actual values, rather than by percentile values
  • Make sure that you ‘re running Database Consistency against all databases
  • Ensure that ‘Auto Create Statistics’ is turned off at the Database Level
  • Make sure that you are successfully defragmenting indexes; especially dbo.AllDocs

Items Discussion

For SharePoint SQL Instance, Max degree of parallelism should be set at 1

  • Essentially, no parallelism
  • The reasons are probably due to the fact that Microsoft SharePoint is an OLTP Application and that the queries are likely well known and predictable enough in terms of Resource Requirement; and that comparative to less understood systems they are less likely to benefit from being broken into parallel tracks

Script to verify “Max degree of Parallelism”:

set nocount on

declare @runValueShowAdvancedOptions     int

declare @runValueMaxDegreeofParallelism  int
declare @runValueDatabaseBackupCompressionDefault  int
declare @runValueIndexFillFactor  int

declare @message   sysname

if OBJECT_ID('tempdb..#configure') is not null

	print 'Dropped remnant table (#configure)'
	drop table #configure 


create table #configure 
	  [serverName]  sysname null default
                  cast(serverproperty('servername') as sysname)
	, [name]        sysname
	, [minimum]     int
	, [maximum]     int	
	, [configValue] int
	, [runValue]    int

insert into #configure 
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
exec sp_configure 'show advanced options'

select top 1 
	  @runValueShowAdvancedOptions = [runValue]
from   #configure 
where  [name] = 'show advanced options'

if (
	   (@runValueShowAdvancedOptions is null) 
	or (@runValueShowAdvancedOptions = 0)

	print 'set show advanced options ... '

	exec sp_configure 'show advanced options',1

	reconfigure with override


insert into #configure 
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
exec sp_configure 'max degree of parallelism'

insert into #configure 
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
exec sp_configure 'fill factor (%)'

insert into #configure 
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
exec sp_configure 'backup compression default'

select *
from   #configure 

select top 1 
	@runValueMaxDegreeofParallelism = [runValue]
from   #configure 
where  [name] = 'max degree of parallelism'

select top 1 
	@runValueDatabaseBackupCompressionDefault = [runValue]
from   #configure 
where  [name] = 'backup compression default'

select top 1 
	@runValueIndexFillFactor = [runValue]
from   #configure 
where  [name] = 'fill factor (%)'

drop table #configure 

if (@runValueMaxDegreeofParallelism = 1) 
       set @message = 'max degree of parallelism is good @ 1'
     set @message = 'max degree of parallelism is no good @ ' 
	AST(@runValueMaxDegreeofParallelism as sysname)

print @message

if (@runValueDatabaseBackupCompressionDefault = 1)
	set @message = 'backup compression default @ 1'
	set @message = 'backup compression default is no good @ ' 
			+ CAST(@runValueDatabaseBackupCompressionDefault as sysname)

print @message

if (@runValueIndexFillFactor = 80)
	set @message = 'Index Fill Factor % is good @ 80'
	set @message = 'Index Fill Factor % is no good @ ' 
			  + CAST(@runValueIndexFillFactor as sysname)
			  + case
				when @runValueIndexFillFactor = 0 then '/100'
				else ''

print @message


For SharePoint Databases, database files should be set to auto-growth by actual values and not percentile


   , case
	when  (db_name(tblMasterFile.database_id) 
                not  in ('master', 'tempdb', 'model', 'msdb') ) 
	      then 'Yes'
	else  'No'
     end as 'IsSharePointDB'	

   , tblMasterFile.growth  

  , case
	when  (tblMasterFile.is_percent_growth =1) then 'Yes'
	else  'No'
    end as 'IsPercentGrowth'

from   sys.master_files tblMasterFile

where  db_name(tblMasterFile.database_id) 
         not in ('master', 'tempdb', 'model', 'msdb')

order by database_id

For Sharepoint Databases, database should be configured as:

  • Auto-Create Statistics should be off
  • Database Collation Name should be set to Latin1_General_CI_AS_KS_WS

     , case
	  when  ( not in ('master', 'tempdb', 'model', 'msdb') )
                   then 'Yes'
	  else  'No'
       end as 'IsSharePointDB'

     , tblDatabase.is_auto_create_stats_on

     , case
	  when  (tblDatabase.is_auto_create_stats_on =1) then 'Yes'
		else  'No'
      end as 'IsCreateAutoStatisticsOn'		

    , tblDatabase.collation_name

   , case
 	when  (tblDatabase.collation_name ='Latin1_General_CI_AS_KS_WS') then 'Yes'
	else  'No'
    end as 'IsDBCollationSetProperly'		

from   sys.databases tblDatabase

where  ( not in ('master', 'tempdb', 'model', 'msdb') )

and  (

	   (tblDatabase.is_auto_create_stats_on = 1)

	or (tblDatabase.collation_name != 'Latin1_General_CI_AS_KS_WS')


order by 

	  tblDatabase.is_auto_create_stats_on desc
	, tblDatabase.database_id

SQL Maintenance

Follow Best Practice by scheduling, running, and reviewing Database Consistency Checks

I really like Sankar Reddy’s comment on Jonathan Kehayias blog post:

When did DBCC CHECKDB last run on my databases?


And, I am publicly stealing it here:

      Id INT IDENTITY(1,1),
      ParentObject VARCHAR(255),
      [Object] VARCHAR(255),
      Field VARCHAR(255),
      [Value] VARCHAR(255)



   FROM #temp 
   WHERE Field IN ('dbi_dbname')

       SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 
       FROM #temp 
       WHERE Field IN ('dbi_dbccLastKnownGood')


          CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS LastRanDBCCCHECKDB
           ON rn1 =rn2


Check for Index Fragmentation, especially against the dbo.AllDocs table

set nocount on;

DECLARE @dbName sysname
DECLARE @objectName sysname
DECLARE @objectNameFull sysname
DECLARE @object_id INT;

DECLARE @id int;
DECLARE @iNumberofObjects INT;

if object_id('##tbData') is not null
	drop table ##tbData 

create table ##tbData 
	id int not null identity(1,1),
	DatabaseName sysname,
	objectName sysname,
	ObjectKind sysname

EXEC sp_Msforeachdb "use [?];
       insert ##tbData select db_name(),,so.xtype from sysobjects so where = 'AllDocs' "

select * from ##tbdata

set @iNumberofObjects = (select COUNT(*) from ##tbdata)
set @id = 1

while (@id &lt;= @iNumberofObjects)

	          @dbName = tblData.DatabaseName
		, @objectName = tblData.objectName
		, @objectNameFull = tblData.DatabaseName
                                      + '.dbo.' + tblData.objectName
	from   ##tbdata tblData
	where = @id

	SET @db_id = DB_ID(@dbName);	
	SET @object_id = OBJECT_ID(@objectNameFull);

	IF @db_id IS NULL
		PRINT N'Invalid database' + isNull(@dbName, '');
	ELSE IF @object_id IS NULL
		PRINT N'Invalid object ' + isNull(@objectNameFull, '');
		    DB_NAME(database_id) as databaseName
		  , object_name(tblStats.object_id, database_id) as objectName
		  , tblStats.index_id				
		  , tblStats.index_type_desc				
		  , tblStats.partition_number
		  , alloc_unit_type_desc
		  , avg_fragmentation_in_percent
		  , ghost_record_count
		 , forwarded_record_count
	   FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL 
                                     , 'DETAILED') tblStats

           --only look @ Clustered and Non Clustered Indexes
           --omit heaps
	   WHERE tblStats.index_type_desc IN 

           -- Index Leaf Levels
           AND   tblStats.index_level = 0

	set @id = @id + 1


drop table ##tbData


For code above, crediting:



  • Review your output and watch for Index Fragmentation levels over 30% or so

My Own Thoughts

Review percentile of Single Use Queries and determine whether “Optimize for Ad-hocs should be engaged”

From Kimberly Tripp’s public library, let us use:

Plan Cache and optimizing for ad-hoc workloads

          objtype AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

        , avg(usecounts) AS [Avg Use Count]

        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) 
               as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) 
               AS [Total Plans - USE Count 1]

        , (sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) * 100) 
			/  sum(1) 
				as [% SingleUse Entries]

		, str(sum(cast( (CASE WHEN usecounts = 1 THEN size_in_bytes 
                         ELSE 0 END) as decimal(18,2))) * 100
			/ sum(cast( size_in_bytes as decimal(18,2) )), 12, 2)
				as [% SingleUse MemoryUse]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC


Tabulated Output:



  • Reviewing Cache Type = “Prepared”, we can see that single use counts make up 53% of the Prepared plans in the Cache and that they are taken up about 80% of the Memory
  • Will advise that SQL Server Instance configuration “Optimize for ad-hocs workloads” be engaged

Determine whether forcing “Database Parameterization” will be sufficient

“Optimize for Ad-Hocs” impacts the entire SQL Instance, you might opt for forcing Database Parameterization on specific databases instead.

A few months ago I stole this piece of code that just lets use identify single used SQL.

The SQL is courtesy of MSFT’s Bart Duncan:

    query_hash, query_plan_hash,
    , total_elapsed_time_ms,
    total_logical_reads, total_logical_writes, total_physical_reads,
    sample_database_name, sample_object_name,
        query_hash, query_plan_hash,
        COUNT (*) AS cached_plan_object_count,
        MAX (plan_handle) AS sample_plan_handle,
        SUM (execution_count) AS execution_count,
        SUM (total_worker_time)/1000 AS total_cpu_time_ms,
        SUM (total_elapsed_time)/1000 AS total_elapsed_time_ms,
        SUM (total_logical_reads) AS total_logical_reads,
        SUM (total_logical_writes) AS total_logical_writes,
        SUM (total_physical_reads) AS total_physical_reads
    FROM sys.dm_exec_query_stats
    GROUP BY query_hash, query_plan_hash
) AS plan_hash_stats
        qs.sql_handle AS sample_sql_handle,
        qs.statement_start_offset AS sample_statement_start_offset,
        qs.statement_end_offset AS sample_statement_end_offset,
            WHEN [database_id].value = 32768 THEN 'ResourceDb'
            ELSE DB_NAME (CONVERT (int, [database_id].value))
        END AS sample_database_name,
        OBJECT_NAME (CONVERT (int, [object_id].value), CONVERT (int, [database_id].value)) AS sample_object_name,
        SUBSTRING (
            (qs.statement_start_offset/2) + 1,
                    CASE qs.statement_end_offset
                        WHEN -1 THEN DATALENGTH(sql.[text])
                        WHEN 0 THEN DATALENGTH(sql.[text])
                        ELSE qs.statement_end_offset
                    - qs.statement_start_offset
            ) + 1
        ) AS sample_statement_text
    FROM sys.dm_exec_sql_text(plan_hash_stats.sample_plan_handle) AS sql 
    INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = plan_hash_stats.sample_plan_handle
    INNER JOIN sys.dm_exec_cached_plans as cachedPlans 
			ON qs.plan_handle = cachedPlans.plan_handle
			AND cachedPlans.usecounts = 1
    CROSS APPLY sys.dm_exec_plan_attributes (plan_hash_stats.sample_plan_handle) AS [object_id]
    CROSS APPLY sys.dm_exec_plan_attributes (plan_hash_stats.sample_plan_handle) AS [database_id]
    WHERE [object_id].attribute = 'objectid'
        AND [database_id].attribute = 'dbid'
) AS sample_query_text
ORDER BY execution_count DESC;

Output Tabulated:



Automation Opportunities

Jeremy Taylor @ discusses the opportunity for using SharePoint’s own Health Analyzer to carry out some of the items listed above.

Jeremy Taylor also cites Database maintenance for SharePoint 2010 Products (  A nice well article written by Bill Baer and Bryan Porter; and technically reviewed by Paul S. Randal (

Songs Playing

Watching YouTube last night and heard a song playing in the Background.  Tried to make out the lyrics and found out it is Peter Bradley Adams.  The particular song is Full Moon Song (

Also, I liked his “The Longer I Run” track:

A bit of a melancholic tracks, and so you might prefer his “Keep Us” song  It is here

And, later on came back to add Emily Lyrics; which is inturn here.



Sharepoint 2013

Sharepoint 2010

SQL Scripts

MS SQL SERVER – OLE DB / ACE Provider – Unable to update existing records

MS SQL SERVER – OLE DB / ACE Provider – Unable to update existing records

The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “LSERVER” could not UPDATE table “[Microsoft.ACE.OLEDB.12.0]”. OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “LSERVER” returned message “You cannot update this field because the value you’re trying to apply is not valid or would break a data integrity rule.  Please correct and try again.”.

While trying to update MS Sharepoint list data from MS SQL Server using the ACE Provider, experienced the error pasted above.

Spent so much time trying to correct:

  1. Reviewed the columns I was updating
  2. In MS Sharepoint, checked that the columns, which were choice columns, were fully defined – that is they contained the corresponding “look-up” values

Checked numerous times, my fingers started to tingle from over-typing

What it ended up being is that though the new data was good, some previous data violated the rule and were now being checked as part of ‘commiting’ this new data.

Ended up relaxing the rules for all choice columns:

Allow ‘Fill-in’ choices:

MS Sharepoint – Sharepoint List – Unable to Edit in DataSheet – The Standard View of your list is being displayed because your browser does not support running ActiveX controls

MS Sharepoint – Sharepoint List – Unable to Edit in DataSheet – The Standard View of your list is being displayed because your browser does not support running ActiveX controls

MS suggests the following culprits:

  • A datasheet component that is compatible with Windows SharePoint Services is not installed
  • A Microsoft Office 2003 Professional edition is not installed on the computer
  • The Web browser does not support Active X controls and/or support for Active X Controls is disabled

Here are some credible links:

  1. Install Microsoft Office XP or later
  2. Make sure that SharePoint support is installed as part of MS Office
  3. Register the Owssupp.dll file
  4. Running a repair of Office 2003 or Office 2007
  5. Launch Control Panel \ Internet Options, then to the Advanced tab, and clicked Reset. After IE was reset to default, it finally worked for some.
  6. Re-register owssupp.dll
  7. If you ‘re using MS Office 2007 or later, make use of Office Diagnostics
  8. Determine the Internet Explorer Zone that the web site is in — Ensure that Active X is enabled per this site.  Preferable, the web site is in the “Trusted” or “Local Intranet” zone.
  9. Ensure that you are running Professional version of Office, and not Standard Edition
  10. If you are using MS Office 2003, Install MS Office 2003 Web Components

Suggested Readings: