다음을 통해 공유

"Deserialization Failed" and Other Errors When Using SQL Server Analysis Services (SSAS) Systems and Databases in a Multi-Version Environment

  • 아티클

Scenario:  IT running multiple versions of SSAS databases, servers, client libraries, and management tools from SQL Server 2008, SQL Server 2008 R2, SQL Server 2012

This article explores some of the errors and behaviors we might encounter when running multiple versions of SSAS in the same environment, particularly when using older tools, such as SQL Server Management Studio (SSMS), to manage newer instances and databases.

To avoid these errors, use the client libraries and management tools that match the version of the servers and databases we're managing.

Using older AMO or SSMS to connect to newer version servers and databases

AMO is a client library used by both custom applications and SSMS to connect to SSAS server instances. Older versions of AMO do not work with newer server modes, architectures, or databases. For example, when using a 2008 R2 version of SSMS to script out a tabular database (introduced in 2012), we’ll get variations of the following error:

       

Error text is:

        Unhandled exception has occurred in a component in our application. If we click Continue, the application will ignore this error and attempt to continue.

        Deserialization Failed: The ‘FormatString’ element in the ‘http://schemas.microsoft.com/analysisservices/2011/engine/300/300’ namespace is unexpected.

Solution is to use a SQL Server 2012 (or later) tools and client libraries with SQL Server 2012 servers and databases.

More about AMO errors in http://support.microsoft.com/kb/2683293 “FIX: Error message when we use AMO to connect to SQL Server 2012 Analysis Services: "Deserialization failed: Requested value '<ServerEdition>' was not found"

Tabular databases can use features not supported in previous releases

Tabular solutions are new in SQL Server 2012, and further enhanced in SQL Server 2012 SP1 to support an additional storage architecture. Changes in the storage architecture mean enhancements throughout, including the ability to use extended or special characters in object names.  If we use an earlier version AMO or SSMS to connect to a tabular database that has special characters, we'll see an error like this:

    

Error text is:

        Unhandled exception has occurred in a component in our application. If we click Continue, the application will ignore this error and attempt to continue.

        Deserialization Failed: The ‘Name’ property cannot contain any of the following characters: ,.;’:/\|?”%$!=[]{}<>.

Solution is to use a SQL Server 2012 or later tools and client libraries. 

Special characters are allowed for tabular databases at compatibility level 1103, but not allowed for 1100 tabular databases, or for any multidimensional database at any compatibility level.  More about “Compatibility level (SSAS Tabular SP1)” http://msdn.microsoft.com/en-us/library/jj674204.aspx  in Books Online.

Need more background?

Running multiple versions of SQL Server side-by-side in production and test environments is a common practice. We might keep previous versions in production while we evaluate and test newer versions, or perhaps we have an existing line of business applications that require a specific version. 

Having multiple versions of a technology means that on any given computer, we might have two or more versions of these components:

  • SQL Server Management Studio (SSMS)
  • SQL Server Data Tools (known as Business Intelligence Development Studio in previous releases)
  • PowerPivot Configuration tools (separate versions for SharePoint 2010 and SharePoint 2013)
  • Analysis Management Objects (AMO)
  • ADOMD.NET
  • MSMDSRV.EXE (the Analysis Services Windows service) that is installed per instance, where each instance is either tabular, multidimensional, or SharePoint deployment mode.

Why do all these versions exist? Per “Upgrade SQL Server Management Tools” in SQL Server Books Online:

SQL Server Client Tools, such as SQL Server Management Studio, SQL Server Profiler, the Database Engine Tuning Advisor, sqlcmd, osql, and so on, are not upgraded to SQL Server 2012. Instead, Client Tools run side-by-side with tools from previous SQL Server versions. SQL Server 2012 supports importing settings from earlier versions of SQL Server Client Tools.

Older and newer versions run side by side so that we can manage older instances using the tools with which they were installed.

As a general rule, using more recent servers and client libraries provides the greatest flexibility because servers and libraries are almost always backward compatible, whereas forward compatibility (such as using an older server to host a newer database) is never supported.

When in doubt, use the version of the tool that corresponds to the server and database version. And if you forget and use the wrong tool, hopefully you'll recognize the errors at the top of this article and quickly determine that a newer tool is all you need.