Category Archives: SQL Server

SQL

Connecting to SSAS 2005 from Excel 2010 Error: “XML for Analysis parser: The DbpropMsmdSubqueries property was not recognized”

Issue

You may receive this error message “XML for Analysis parser: The DbpropMsmdSubqueries property was not recognized” when connecting from Excel 2010 to SSAS 2005

Cause

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

Resolution

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)

VS2005 & BIDS 2005 Compatibility with TFS 2012

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:

Pre-requisites:

  • An instance of Team Foundation Server 2012 (or Express edition) up and running.
  • Visual Studio 205 SP1 / BIDS 2005 SP1

TFS 2012 Team Explorer
Download: http://www.microsoft.com/en-gb/download/details.aspx?id=30656

Microsoft Visual Studio Team Foundation Server 2012 MSSCCI Provider 32-bit
Download: http://visualstudiogallery.msdn.microsoft.com/b5b5053e-af34-4fa3-9098-aaa3f3f007cd

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”.

BIDS Browser Error – The query could not be processed XML for Analysis parser The LocaleIdentifier property is not overwritable and cannot be assigned a new value.

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.

 

Resolution:

The language will be set to ‘Default’
BIDS Browser Default Language

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.
BIDS Browser UK Language

 

** 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.

Finding which Operators are setup against SQL Agent Job Notifications

As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.

Agent Job Properties

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],
[notify_level_email] =
CASE notify_level_email
     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'
END
FROM
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO 
ON SJ.notify_email_operator_id = SO.ID
ORDER BY SO.ID, SJ.NAME

Download  Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (328 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.

Agent Job Notifications

 

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’

SQL Agent Operators Recommended Setup

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.

DBA Team Operator

 

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.

DBA Team BI Operator

Update to Ola Hallengren’s SQL Server Maintenance Solution available – Support for SQL Server 2012

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
http://ola.hallengren.com/versions.html

or download it at

http://ola.hallengren.com/scripts/MaintenanceSolution.sql

 

New Documentation

Updated documentation is now available at

http://ola.hallengren.com/sql-server-backup.html
http://ola.hallengren.com/sql-server-integrity-check.html
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

 


Microsoft SQL Server 2012 Courses Released on MVA

5 new SQL Server 2012 courses are available on Microsoft Virtual Academy’s site http://www.microsoftvirtualacademy.com/

Follow the below links to read more about them:

Breakthrough Insights using Microsoft SQL Server 2012 : Analysis Services and Credible, Consistent data

Breakthrough Insights using Microsoft SQL Server 2012 : Reporting Services and Visualization

Breakthrough Insights using Microsoft SQL Server 2012 : Scalable Data Warehouse and Beyond Relational

Cloud on your terms with Microsoft SQL server 2012 : Scale On Demand

Mission Critical Confidence using Microsoft SQL Server 2012

SQL Agent Jobs refresh times when using sysjobschedules table

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

 

SQL Server Administration – SQL Object Naming Convention

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 (1015 downloads)

Download (Word Version) SQL-Server-Object-Naming-Conventions-Administration (1522 downloads)