Showing posts with label Assembly. Show all posts
Showing posts with label Assembly. Show all posts

2014-04-07

Add AMO without SQLPS

When installing a SQL Server Analysis Services instance without a Database Engine instance to comply with the principle of Least Service the namespace Microsoft.AnalysisServices part of AMO is not available locally, and the PowerShell command
Import-Module -Name SQLPS -DisableNameChecking
fails with the error
Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.
It looks like the SQLPS is not part of a bare Analysis Services installation where the feature selection only is
/FEATURES="AS"
in a command-line installation.

To use the namespace Microsoft.AnalysisServices I found out that all I had to do was to add the type Microsoft.AnalysisServices, but the PowerShell CmdLet Add-Type requires a full assembly name.
The article "Powershell Add-Type – Where’s That Assembly" by Kyle Neier gives the full name for what looks to be SQL Server 2005 with the version 9.n. I am working with SQL Server 2012 that has version 11.n, and would like to prepare for SQL Server 2014 (version 12.n) and beyond. This gives that a simple command like
Add-Type -AssemblyName 'Microsoft.AnalysisServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
is not good enough as it is version-dependant.

Kyle's article showed me where to look for the version and token, and some manual browsing showed me that the assembly is located among the combined (32/64 bit) assemblies. A little split and merge of strings gave this little script to add the type Microsoft.AnalysisServices independant of SQL Server version
$Assembly= $(Get-ChildItem 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.DeploymentEngine').Name.Split('_')
$AssemblyName = "Microsoft.AnalysisServices, Version=$($Assembly[1]), Culture=neutral, PublicKeyToken=$($Assembly[3])"
Add-Type -AssemblyName $AssemblyName

To get the token I take the third item in the array Assembly. The second item is a empty string as there are two underscores ('_') in the folder name between the version part and the token part of the folder name.

I think I am home-safe. That is a least until the path is changed...

2011-03-14

Could not load file or assembly Microsoft.AnalysisServices

After a Service Pack upgrade on a SQL 2005 installation from SP2 to SP4, a job failed with
Executed as user: *****. A .NET Framework error occurred during execution of user-defined routine or aggregate "NameOfUserDefinedStoredProcedure":   System.IO.FileLoadException: Could not load file or assembly 'Microsoft.AnalysisServices, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)  System.IO.FileLoadException:      at nnn.mmm.fff(String s. The step failed.

Searching the internet gave me the MSDN Forum thread "Framework error:Could not load file or assembly 'Microsoft.AnalysisServices, Version=9.0.242.0". Here I was directed to KB949080 and the handling of assemblies.

A look at the assemblies in the database with
SELECT * FROM sys.assemblies;
and their files with
SELECT * FROM sys.assembly_files;
gave me the details to refresh the assembly registrations with
ALTER ASSEMBLY [Microsoft.AnalysisServices]
FROM N'C:\Program Files (x86)\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.AnalysisServices.DLL';


Now everything is working again.

Reference

MSDN Library: "ALTER ASSEMBLY (Transact-SQL)"
KB949080: "... Assembly in host store has a different signature than assembly in GAC..."