SSAS - How To Change A ServerProperty From SQL Server With Only the IP Address the SSAS Server

Question

I have the following script which is very handy at changing a server property on a SSAS instance installed on the same server:

CREATE PROCEDURE [dbo].[Utility_SetMinIdleSessionTimeout] @TimeoutInSeconds INT
    ,@ServiceAccountUsername NVARCHAR(MAX)
    ,@ServiceAccountPassword NVARCHAR(MAX)
    ,@DataSource VARCHAR(50) = '127.0.0.1'
    WITH EXECUTE AS 'ABC_Admin'
AS
BEGIN
    SET NOCOUNT ON;

    --==================================================
    --Create linked server
    --==================================================
    IF (
            NOT EXISTS (
                SELECT 1
                FROM sys.servers
                WHERE [Name] = 'ServerSettings'
                )
            )
    BEGIN
        EXEC master.dbo.sp_addlinkedserver @server = 'ServerSettings'
            ,@srvproduct = ''
            ,@provider = 'MSOLAP'
            ,@datasrc = @DataSource

        EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'ServerSettings'
            ,@useself = N'False'
            ,@locallogin = NULL
            ,@rmtuser = @ServiceAccountUsername
            ,@rmtpassword = @ServiceAccountPassword

        EXEC master.dbo.sp_serveroption @server = 'ServerSettings'
            ,@optname = N'rpc out'
            ,@optvalue = N'true'
    END

    --==================================================
    --Create and Execute XMLA
    --==================================================
    DECLARE @XMLA XML
        ,@XMLAString NVARCHAR(MAX) = 
        '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
            <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                <Object />
                <ObjectDefinition>
                <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
                    <ID>' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR(MAX)) + '</ID>                
                    <Name>' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR(MAX)) + '</Name>
                    <ServerProperties>
                    <ServerProperty>
                        <Name>MinIdleSessionTimeout</Name>
                        <Value>' + CAST(@TimeoutInSeconds AS NVARCHAR) + '</Value>
                    </ServerProperty>
                    </ServerProperties>
                </Server>
                </ObjectDefinition>
            </Alter>
        </Batch>'

    SET @XMLA = @XMLAString

    EXEC (@XMLAString) AT [ServerSettings]

    --==================================================
    --Clean up 
    --==================================================
    EXEC master.dbo.sp_dropserver @server = N'ServerSettings'
        ,@droplogins = 'droplogins'
END

Now I have SSAS on another server, and I want to run this on that server from SQL Server on my first server. This provides a challenge, I'd have the IP Address of the new server but not the server name, to get this script to work I need to have both(DataSource is the new IP, server name is the new "MachineName" in the XML).

Other posts claim they're is not a way to do this natively in SQL Server. One thing that I notice though, is that when I set every property(both data source and machine name) to the IP address of the new server the proc fails but the error message contains the server name of my new SSAS server, so SQL Server must be aware of it somehow:

OLE DB provider "MSOLAP" for linked server "ServerSettings" returned message "Errors in the metadata manager. The object ID cannot be changed from 'ServerNameABC' to 'NewIPAddress' for the 'ServerNameABC' server. The object ID cannot be changed by an ALTER statement.".

Is there something I'm missing here, is there a easy way for me to achieve what I'm looking here?

Answer 1

(1) Your script creates a linked server

(2) Then executes an XMLA on the linked server

The line SERVERPROPERTY('MachineName') is an sql statement which retrieves the machine name of the server, so, in your case, the current server name is retrieved and substituted in the string.

You can do a print of the @XMLAString variable to cross check the exact XMLA.

Now, when the XMLA is run via the EXEC ... AT ... syntax, it runs on the remote server. This looks to be the reason you are getting an error about the server name.

So, in summary, you could try the following:

• Remove the ID tag
• Use the new server IP address for the Name tag (instead of CAST(SERVERPROPERTY('MachineName') AS VARCHAR(MAX)))

The official docs for ALTER XMLA is not very clear !