If you’ve ever processed a cube then browsed it and there’s no data in there one of the common resolutions is to check that the word ‘CALCULATE’ exists in the Calculations tab within the Cube Designer in BIDS as shown below:
If it doesn’t exist then add and re-process your cube.
You may receive this error message “XML for Analysis parser: The DbpropMsmdSubqueries property was not recognized” when connecting from Excel 2010 to SSAS 2005
In most cases the machine will have had Excel 2007 previously installed and Excel 2010 has been installed side-by-side with 2007 or has been upgraded to Excel 2010
Install the Analysis Services OLE DB Provider for Microsoft® SQL Server® 2008 R2 available in the Microsoft® SQL Server® 2008 R2 Feature Pack
Here’s an overview of what the Analysis Services OLE DB Provider is:
The Analysis Services OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 R2 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.
Note: Microsoft SQL Server 2008 R2 Analysis Services OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0, also available on this page.
The provider can be downloaded from the following links:
X86 Package (SQLServer2008_ASOLEDB10.msi)
X64 Package (SQLServer2008_ASOLEDB10.msi)
The latest version of Brent Ozar’s spBlitz script is now available details can be found here:
An excellent start to using and understanding SQL Server DMV’s and better still it’s available for FREE!!
The pdf book and scripts can be
downloaded from here:
In order to use the Source Code Control feature of Team Foundation Server 2012 (including Express Edition) with Visual Studio 2005 or Business Intelligence Development Studio 2005 you need to install the following on your client machine:
- An instance of Team Foundation Server 2012 (or Express edition) up and running.
- Visual Studio 205 SP1 / BIDS 2005 SP1
TFS 2012 Team Explorer
Microsoft Visual Studio Team Foundation Server 2012 MSSCCI Provider 32-bit
To enable the MSSCCI plug within Visual Studio 2005 / BIDS 2005 go to Tools > Options, find “Source Control” and select the “Team Foundation Server MSSCCI provider”.
When attempting to browse an Analysis Services Cube in the BIDS Browser the following error message is displayed:
|The query could not be processed:
|o XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value.
The language will be set to ‘Default’
Select your language ( e.g. English (United Kingdom) ) from the language drop down box and you’ll be able be able to browse the cube.
** UPDATED 11/10/12 **
I’ve come across a more permanent fix for this issue:
1. Open Start – Control Panel, find Location Settings (or Regional Settings or Region and Language).
2. Find the Format setting (which determines date and time formats).
3. Change it from your normal setting (in my case English (United Kingdom)) to English (United States). Apply the change.
4. Straight away set it back to what it was before (in my case English (United Kingdom)). Apply the change.
5. You will need to restart the BIDS if you have it open.
The error should not appear again for that user on the machine the fix was applied to.
As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.
How do I check which operators are setup against my Agent Jobs I hear you say!
Well run this query below to find out. If you have any agent jobs return as ‘No email notification configured’ in the [Notify_level_email] field then an email notification is not setup and you should look into these as a priority (unless you have other methods of monitoring Agent Jobs that is!)
SELECT SJ.NAME, SO.[name],SO.[email_address],
WHEN '1' THEN 'When the job succeeds'
WHEN '2' THEN 'When the job fails'
WHEN '3' THEN 'When the job completes'
ELSE 'No email notification configured'
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO
ON SJ.notify_email_operator_id = SO.ID
ORDER BY SO.ID, SJ.NAME
Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (72 downloads)
The example below shows the operators setup against Agent Jobs and the email address the notification will be sent to, the first row shows that no operator is setup on this job for email notifications, if this job fails no-one may be aware.
There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
1. Database Mail should be configured and working (see this great article on SQL Server Central if you don’t know how to setup Database Mail)
2. Agent Operators should be setup with valid email addresses (see my post on recommended operators to setup)
3. Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’
Database Mail should be configured on your SQL Server, if it isn’t get it done now there are no excuses, this is one of the main methods that can be used for finding out when there are issues with your SQL Server!
It’s a good idea to setup an operator with a distribution email group email address so if a DBA leaves or moves onto another position the distribution group can be easily updated once in Active Directory as opposed to being updated on every SQL Server.
You could also create additional distribution email groups and add them as operators this is useful if other administrators needs to be aware of certain Agent Jobs failing such as say BI Jobs.
New Version Available
A new version of the SQL Server Maintenance Solution by Ola Hallengren’s is now available. The new version supports SQL Server 2012.
One new feature in SQL Server 2012 is AlwaysOn Availability Groups. This feature supports multiple replicas of a database. You can back up any replica and define a preferred backup replica.
SQL Server 2012 also supports online rebuilding of indexes with varchar(max), nvarchar(max), varbinary(max), or XML data types or large CLR types. The new version of the SQL Server Maintenance Solution makes use of this capability.
You can read more about the most recent version of the solution at
or download it at
Updated documentation is now available at
Two good sites for getting SQL Server Build Lists and links to the KB articles
SQL Security – SQL Server Build Lists
SQL Server Central – SQL Server Build Lists
A recent issue I was looking at confused me as we were adding SQL Agent Jobs to run a SSRS report but the agent job was not appearing in the ‘sysjobschedules’ table, eventually the job did appear but why did it not appear straight away.
The reason is the the sysjobschedules table refreshes every 20 minutes, which may affect the values returned by the sp_help_jobschedule stored procedure.
Read the article here on MSDN
It’s useful to use a naming convention on Agent Jobs or Maintenance plans, this ensures they are grouped together and easier to find when you have lots of job or maintenance plans on your SQL Server.
Download (PDF Version)
SQL-Server-Object-Naming-Conventions-Administration (791 downloads)
Download (Word Version)
SQL-Server-Object-Naming-Conventions-Administration (1275 downloads)