Archive for September, 2012

Today we got an escalation where on one of the servers ‘Agent XPs’ was in a disabled state. And when looked at SQL Server Agent node, it is displayed in Red which basically indicates SQL Agent Service is not running, however when checked the Agent Services are actually running, which is misleading!


So here is some basic information I gathered about Agent XPs option – only found in SQL Server 2005 and above versions

Summing up from MSDN BOL:

  • When Agent XPs is not enabled (which makes Agent extended stored procedures are not enabled), SSMS does not display the contents of the SQL Server Agent node regardless of the SQL Server Agent service state.
  • Hence, in order to enable the SQL Server Agent extended stored procedures on the server, Agent XPs should be enabled
  • When SQL Server Management Studio tool is used to start the SQL Server Agent service, these extended stored procedures are enabled automatically, which should enable Agent XPs server configuration option.

It is not very clear from BOL what makes Agent XPs setting enable/disable, so I dug more into this and here is some brief notes:

  • I Installed a local instance (SQL 2012) on my machine and did a few test scenarios which confirms that Agent XPs option will be enabled automatically when SQL Agent Services are started, either with Manual mode or Automatic mode.
  • And whenever the SQL Agent service is started or stopped regardless of the SQL Agent start mode (Manual/Automatic), it will enable or disable the Agent XPs respectively.
  • Hence, if SQL Agent is stopped and don’t restart – it will disable the Agent XPs option(even though we enabled the Agent XPs prior to stopping SQL Agent)
  • When the Agent XPs option is disabled, we cannot access any SQL Agent nodes or even its properties from SSMS.
  • Vice-versa, when the Agent XPs option is enabled, we can access SQL Agent nodes and properties from SSMS whether or not SQL Agent services are running or stopped
  • And, from the above observations, it is clear says that Agent XPs option disabled doesn’t mean that SQL Agent services are stopped

Finally from my understanding most of the time Agent XPs option shouldn’t be an issue since whenever the SQL Agent services are started the Agent XPs option will be automatically enabled.

If this option is disabled, either the SQL Agent services never started or Agent XPs option might have disabled due to some reasons.. such as security patching on the servers (which can be enabled anytime using a simple script which doesn’t need Agent/Server restart).

--To check if Agent XPs option is enabled or not 
--(show advanced options must be enabled for this)
--To enable show advanced options
EXEC SP_CONFIGURE 'show advanced options', 1
--To see advanced options
EXEC SP_CONFIGURE 'show advanced options'
--To enable Agent XPs option

Read Full Post »

Recently I heard about this PowerPivot and found it’s a great feature added to Excel for interactive reporting. We can download this add-in for free from the link www.PowerPivot.com if you already have 2010 Excel installed on your machine.

So, I started to browse around to check if I can find some quality training on PowerPivot and here is what I found as of 9/20/12.

Startup videos from Microsoft’s youtube channel. However, the videos are not in a sequence, and I have to watch randomly all the videos to figure out the order and have to watch them again for better understanding. Hence I recommend to watch the videos in the below order:

  1. How to Install PowerPivot Add-In for Excel
  2. How to Import Data With PowerPivot for Excel
  3. How to Import Data From A SQL Server Database
  4. How to Create a Pivot Table from PowerPivot Data
  5. How to Create a Pivot Chart from PowerPivot Data
  6. How to Use a PowerPivot Slicer with a PivotTable
  7. How to Use a PowerPivot Slicer with a PivotChart
  8. PowerPivot Relationships Overview

Click Here to watch the videos from Microsoft’s BI YouTube channel

Click Here for overview of PowerPivot features

Click Here for PowerPivot component Architecture

Click Here for PowerPivot hands-on tutorial

Read Full Post »