Sharepoint – Export List – Error – “To export a list, you must have a Microsoft SharePoint Foundation-compatible application”

Background

Trying to export a SharePoint List, but getting the prompt and error pasted below.

Prompt

Do you want to open or save owssvr.iqy from sp..

Textual

Do you want to open or save owssvr.iqy from sp..

Image

Error

Error – SharePoint Foundation-Compatible Application

Textual

To export a list, you must have a Microsoft SharePoint Foundation-compatible application.

Image

 

TroubleShooting

Microsoft SharePoint Foundation Support

Is “Microsoft SharePoint Foundation Support” installed?

Accessed “Control Panel \ “Add or Remove Programs” \ “Microsoft Office Professional Plus 2010” \ Changed and made sure that “Microsoft SharePoint Foundation Support” is checked

 

Microsoft Office

Check MS Office Version #

MS Word

Using MS Word Help/About, check Version#

Image

Version Info:

Product :- Microsoft Office Professional Plus 2010
Version # :- 14.0.7184.5000 ( 32-bit)

Version Matrix

Image

Tabulate
Product Version Version# – Low Version# – High
Office 2010 – RTM 14.0.4763.1000 14.0.6029.1000
Office 2010 – SP1 14.0.6029.1000 14.0.7015.1000
Office 2010 – SP2 14.0.7015.1000

 

Explanation

Our current version # is 14.0.7184.5000.

And, SP2 is at minimum 14.0.7015.1000.

 

Microsoft Office – Repair

Image

Control Panel \ All Control Panel Items \ Programs and Features – Uninstall or Change

Microsoft Office Professional Plus 2010 – Change your Installation of Microsoft Office Professional Plus 2010

Microsoft Office Professional Plus 2010 – Configuration Progress

Repairing Microsoft Office Professional Plus 2010 ….

Microsoft Office Professional Plus 2010 – Configuration Complete

The configuration for Microsoft Office Professional Plus 2010 is complete.
To make your changes take effect, exit and restart and open Office Programs.

Microsoft Office Professional Plus 2010 – Configuration Complete

In order to complete setup, a system reboot is necessary.
Would you like to reboot now?

Validation

Post system reboot, launch Internet Explorer and revisited SharePoint List

Images

Microsoft Excel Security Notice

Microsoft Office Has identified a potential security concern

Textual

Microsoft Office Has identified a potential security concern.

Image

Excel

Exported List

Summary

To address the error “To export a list, you must have a Microsoft SharePoint Foundation-compatible application“, we were getting we simply repaired our microsoft Office 2010 installation.

In some cases one needs to apply the latest Service Pack, but we verified that we are already on the latest SP.

 

References

  1. Microsoft
    • Technet
      • Kim P – MSFT
        • To export a list, you must have a Microsoft SharePoint Foundation-compatible application
          Link
    • Support
      • Description of Office 2010 Service Pack 2
        Link

SQL Server – Index Rebuild – Blocked / Blocking

Background

Got a call about a hung database job.

 

Check Current Sessions

sp_whoIsActive

Code


exec sp_whoIsActive

Output

Explanation

  1. Session ID :- 161
    • Alter Index Session
    • Being blocked by Session ID :148
  2. Session ID :- 148
    • Select Statement

 

TroubleShooting

Why Is Select Blocking?

sp_block

Let us issue sp_block against the blocker and blocked sessions.

Code


-- exec sp_help sp_lock
declare @spidIndexOptimize int
declare @spidBlocker int

set @spidIndexOptimize = 161
set @spidBlocker = 148

exec sp_lock
		  @spid1 = @spidIndexOptimize 
		
exec sp_lock
		  @spid1 = @spidBlocker

 

Output

 

Explanation

  • Session ID :- 161
    • Schema Stability
    • Schema Modification
  • Session ID :- 148
    • Schema Stability

 

Why Is Index Reorg / Rebuilt?

sysindexes

Let us see how many records we have and how many records have changed.

Code


select 
		  [table] = 
					  object_schema_name(tblSI.id)
					+ '.'
					+ object_name(tblSI.id)
		, tblSI.indid
		, tblSI.[name]
		, tblSI.[rowcnt]
		, tblSI.[rowmodctr]
from   sysindexes tblSI
where  tblSI.indid In ( 0, 1)
and    tblSI.id = object_id('dbo.event_log_bkp')

;

 

sys.dm_db_index_physical_stats

Fragmentation %

Code


use [rbpivr1]
go

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'rbpivr1');  
SET @object_id = OBJECT_ID('dbo.event_log_bkp');  

IF @db_id IS NULL  
BEGIN;  
    PRINT N'Invalid database';  
END;  
ELSE IF @object_id IS NULL  
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN

	SELECT
			  tblSI.[name]
			--, tblSI.type_desc
			, tblSI.[index_id]
			, tblDIPS.index_type_desc
			, tblDIPS.page_count
			--, tblDIPS.record_count
			--, tblDIPS.*
			, tblDIPS.avg_fragmentation_in_percent 

	FROM   sys.dm_db_index_physical_stats
			(
				  @db_id
				, @object_id
				, NULL
				, NULL 
				, 'LIMITED'
			) tblDIPS

	inner join sys.indexes tblSI
		on   tblDIPS.[object_id] = tblSI.[object_id]
		and  tblDIPS.[index_id] = tblSI.[index_id]
			;  
END;

Output

Explanation

It appears that a couple of indexes are eligible for re-org and others for a rebuild.

Remediation

Handle Blocking

Documentation

Found out that Olla Hallengren’s code has a built-in mechanism for handling blocking.

The documentation is here.

Image

Explanation

  1. WaitAtLowPriorityMaxDuration
    • How long to wait in minutes
  2. WaitAtLowPriorityAbortAfterWait
    • Options
      • NONE
        • Continue waiting for Locks
        • Default Option
      • SELF
        • Abort the online index rebuild operation.
        • Terminate the Index Rebuild Option
      • BLOCKERS
        • Kill user transactions that block the online index rebuild operation.
        • Terminate the Blocker

 

Choice

Our choice, when blocked, will be to wait 10 minutes and terminate the Index Rebuild Option

Code


EXECUTE [AdminDB].dbo.IndexOptimize
	  @Databases = 'USER_DATABASES'
	, @FragmentationLow = NULL
	, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationLevel1 = 5
	, @FragmentationLevel2 = 30
	, @UpdateStatistics = 'ALL'
	, @OnlyModifiedStatistics = 'Y'
	, @LogToTable = 'Y'
	--20170813 9:17 PM dadenji
	, @WaitAtLowPriorityMaxDuration=10
	, @WaitAtLowPriorityAbortAfterWait='SELF'

Generated Code

Here is the code that is generated when we make the change outlined above:

 

References

  1. Michael J Swart ( Database Whisper )
    • The Sch-M Lock is Evil
      Link

SSRS – Validating Smart Host – Using Mozilla Thunderbird

Background

A few posts ago, we provisioned a Smart Host.

Let us quickly test it out.

 

Lineage

Here are other posts in this multi-series:

  1. SSRS – Setting up Smart Host
    Link

Scenario

To make sure that we understand the specificity of Microsoft’s Office 365 SMTP service, we will install a free, durable email client; specifically Mozilla Thunderbird.

 

Wireshark

Filter

  1. tcp.port == 25 || tcp.port == 465 || tcp.port == 587

Mozilla Thunderbird

Download

Download Mozilla Thunderbird from here.

Installation

Installation is straightforward.

Configuration

Images

Session – 1

Configuration
SMTP Server Settings

 

Output
Netstat

WireShark

 

Session – 2

Configuration
SMTP Server Settings

Output
Netstat

WireShark

WireShark – SMTP ( Destination Port 587 ) – 01
WireShark – Explanation
  1. Connection to SMTP Host
WireShark – SMTP ( Destination Port 587 ) – 02

WireShark – Explanation
  1. Reply from SMTP Host

 

Session – 3

Configuration
SMTP Server Settings

WireShark

WireShark – Explanation
  1. We see a fuller conversation

 

Item Port :- 465 /
Connection :- SSL/TLS
Port :- 567 /
Connection Start :- SSL/TLS
Port :- 567 /
Connection Start :- STARTSSL
Server Name smtp.office365.com smtp.office365.com smtp.office365.com
Port 465 587 587
Connection Security SSL/TLS SSL/TLS STARTSSL
Authentication Password Normal Password Normal Password Normal Password
Results SYN/SENT  Truncated Conversation  Full Conversation

 

Network Ports

  1. Port :- 25
    • SMTP
      • Server to Server email
        • Message relay port
      • (-)
        • Not Authenticated
        • Blocked by a lot of firewall
  2. Port :- 465
    • SMTP
      • (-)
        • Not widely adopted
  3. Port 587
    • SMTP
      • Mail Submission Port
      •  (+)
        • Authenticated emails

 

References

  1. Microsoft
    • Office
      • Support
        • POP and IMAP settings for Outlook Office 365 for business
          Link
        • How to set up a multifunction device or application to send email using Office 365
          Link
    • technet
      • blogs.technet.com
        • Andrew Stobart – Useful Wireshark Filters for Mail Flow Troubleshooting
          Link
  2. Mozilla.Org
    • Mozilla Support
      • cannot send mail. Connected to smtp.office365.com but times out.
        Link
  3. Stanford University
    • Stanford | University IT
      • How to Configure Thunderbird for Office 365 Using IMAP
        Link
  4. StackOverflow
    • Network Ports
      • What is the difference between ports 465 and 587?
        Link
  5. JSCAPE
    • Managed File Transfer and Network Solutions
      • John Carl Villanueva
        • Still Confused With SMTP Ports? Read This
          Link
  6. FastMail
    • SSL vs TLS vs STARTTLS
      Link

 

Timothy Keller – 2017/Summer

Teachings

  1. The One God uses
    • YouTube
      Published On :- 2017-August-10th
      Link
  2. TIM KELLER 2017 – An Immigrant’s Courage (POWERFUL SERMON 2017)
    • YouTube
      Published On :- 2017-July-4th
      Link
  3. A World of Idols | Timothy Keller
    Sermon :- Acts 17:16-34

    • YouTube
      Published On :- 2017-August-7th
      Link

 

Indepth

The One God Uses

  1. The calling of God
    • Divine Call
      • Being
      • Doing
  2. Apprentice of 18 years
  3. Prophetic of 30 years

 

An Immigrant’s Courage

  1. Deuteronomy 23
    • “No Ammonite or Moabite may enter the assembly of the Lord. Even to the tenth generation, none of them may enter the assembly of the Lord forever,
      because they did not meet you with bread and with water on the way, when you came out of Egypt, and because they hired against you Balaam the son of Beor from Pethor of Mesopotamia, to curse you.
      But the Lord your God would not listen to Balaam; instead the Lord your God turned the curse into a blessing for you, because the Lord your God loved you.
      You shall not seek their peace or their prosperity all your days forever. ( Deuteronomy 23:3-6 )
    • “You shall not abhor an Edomite, for he is your brother. You shall not abhor an Egyptian, because you were a sojourner in his land.
      Children born to them in the third generation may enter the assembly of the Lord. ( Deuteronomy 23:7)
  2. Barren Woman
    • “Shout for joy, O barren one, you who have borne no child; Break forth into joyful shouting and cry aloud, you who have not travailed; For the sons of the desolate one will be more numerous Than the sons of the married woman,” says the LORD. ( Isaiah 54:1 )
    • …But the Jerusalem above is free, and she is our mother.
      For it is written: “Rejoice, O barren woman, who bears no children; break forth and cry aloud, you who have never travailed; because more are the children of the desolate woman, than of her who has a husband.”
      Now you, brothers, like Isaac, are children of the promise.… ( Galatians 4:26-28 )

A World of Idols

  1. Gehazi’s Greed and Leprosy
    …Naaman said, “If not, please let your servant at least be given two mules’ load of earth; for your servant will no longer offer burnt offering nor will he sacrifice to other gods, but to the LORD.
    “In this matter may the LORD pardon your servant: when my master goes into the house of Rimmon to worship there, and he leans on my hand and I bow myself in the house of Rimmon, when I bow myself in the house of Rimmon, the LORD pardon your servant in this matter.”
    He said to him, “Go in peace.” So he departed from him some distance.… ( 2 Kings 5:17-19 )

IIS Logs / Log Parser Studio – Aggregated Hits per Server

Background

Our monitoring team has developed and rolled out scripts for monitoring our web farm.

And, we are getting alerts through email.

Quite a lot of emails are coming across and wanted to see if they are coming from same host or a combination of hosts.

 

Emails

Looked at the emails and they happen to be coming from same host.

And, so will have to engage our Network team and see how the Load Balancer is configured.

Is there a prospect that more traffic is being directed at the failing node?

Network Load Balancer

As we prepared to go to the Network Load Balancer team took the opportunity to take gather and query the IIS Logs, as well.

 

TroubleShooting

Log Parser Studio

Query


SELECT 
            To_String(date, 'yyyy-MM-dd') as dated

          , sc-status as status

          , sum (
                    case s-ip
                        when '10.0.4.25' then 1
                        else 0
                   end
               ) as S1

          , sum (
                    case s-ip
                        when '10.0.4.26' then 1
                        else 0
                   end
               ) as S2


          , sum (
                    case s-ip
                        when '10.0.4.27' then 1
                        else 0
                   end
               ) as S3

         , sum (
                    case s-ip
                        when '10.0.4.28' then 1
                        else 0
                   end
               ) as S4

          , min(TO_TIMESTAMP(date, time)) as tsRecordedMin


          , max(TO_TIMESTAMP(date, time)) as tsRecordedMax


FROM '[LOGFILEPATH]' 


where   (


           (

             TO_TIMESTAMP(date, time) 
                     between timestamp('2017/08/02 10:30:00', 'yyyy/MM/dd hh:mm:ss')  
                          and timestamp('2017/08/02 17:20:00', 'yyyy/MM/dd hh:mm:ss')
           )

       )

/*

	and  c-ip not in ('10.0.4.141')
	
*/

group by
         date
       , sc-status


order by
           dated 
         , status



Output

Time Range – 1 ( August 2nd 10:30 AM – 5:20 PM )

Results

Explanation
  1. It is difficult to make case that traffic is exhaustively being waded into a specific host

Time Range – 2 ( August 8th 5:13 PM – 8:40 PM )

Results

Explanation
  1. In our second time slot, 4700 records bearing HTTP 200 is right around average

Summary

At this time it is likely that the sufferance we are seeing with this specific host is not due to outside pressure, but internal to the host itself.

 

SQL Server – Unable to shrink Transaction Log file

Background

Received an alert stating that we have gotten below our threshold of 10% on one of DB Servers.

Thanks goodness no late night phone calls.

Only saw it coming to work this morning.

The good and bad about smartphone.

TroubleShooting

Windows Desktop

Folder

Image

Explanation

  1. ppsivr_8.ldf is 28 GB

SQL Metadata

Outline

Here are dynamic management views ( DMVs) that we will access…

  1. dbcc sqlperf(logspace)
  2. sys.database_files
  3. sys.databases
  4. sys.dm_exec_requests & sysprocesses

 

Review Transaction Log File Sizes

SQL


dbcc sqlperf(logspace)

Output

 

Explanation

  1. ppsivr
    • Log Size MB
      • 27,699 MB
      • 27 GB

Review Database File Sizes

SQL


-- replace with targeted database
use [database]

SELECT 
		  [name]

		, [type]
			= tblSDF.[type_desc]

		, [filename]
			= tblSDF.[physical_name]

		, [allocatedMB]
			= tblSDF.size / 128

		, [usedMB]
			= CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)
				/ 128

		, [availableSpaceMB]
				 = ( size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) )
					/ 128

FROM sys.database_files tblSDF

order by
		tblSDF.size desc
;

Output

Review Database Transaction Log Re-usability State

SQL


declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		    tblSD.[database_id]
		  , tblSD.[name]
		  , tblSD.[log_reuse_wait_desc]

from   sys.databases tblSD

where  (
			   (@dbid is null)

			or ( tblSD.[database_id] = @dbid)
		
	   )

Output

Explanation

  1. log_reuse_wait_desc
    • The database is not re-cycling its transaction log due to an ‘Active Transaction’

Review Current DB Requests

Code



declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		  tblSDER.[session_id]

		, [database] 
			= db_name(tblSDER.[database_id])

		, [openTransaction]
			= tblSP.[open_tran]

		, tblSDER.[status]

		, tblSDER.[command]

		, tblSDER.[percent_complete]

		, tblSDER.wait_type

		, tblSDER.last_wait_type

		, tblSDER.[reads]

		, tblSDER.[writes]

from   sys.dm_exec_requests tblSDER

inner join master..sysprocesses tblSP

	on tblSDER.[session_id] = tblSP.[spid]

where  (

	         ( tblSDER.[open_tran] != 0 )

	      or ( tblSDER.[database_id] = @dbid)

      )



Output

Explanation

  1. User Transactions
    • It does not appear that we have user transactions that are current using our targeted database
  2. Background
    • WAIT_XTP_OFFLINE_CKPT_NEW_LOG
      • It does does not apply that WAIT_XTP_OFFLINE_CKPT_NEW_LOG updates the percent_complete column as so it is a bit difficult to track its current progress

 

Remediation

Here are the things we tried:

  1. dbcc shrinkfile
  2. Take database offline and bring back online

Attempt to Shrink Transaction Log Files

SQL


use [ppsivr]
go

dbcc shrinkfile('ppsivr_log')
go

Output

Explanation

  1. We want to take a good look at Current Size and Used Pages
    • In our case they are same
    • What is in our Transaction Log File?

 

Take Database Offline and bring it back online

Force termination of ongoing sessions

SQL


use master
go

ALTER DATABASE [ppsivr]
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	;

alter database [ppsivr] set offline;

alter database [ppsivr] set online;

ALTER DATABASE [ppsivr]
	SET MULTI_USER WITH ROLLBACK IMMEDIATE
	;

 

Summary

But, nothing worked!

We will come back and talk about what worked.

References

  1. Microsoft
    • Support.Microsoft.com
      • FIX: Offline checkpoint thread shuts down without providing detailed exception information in SQL Server 2014
        Link