How can I pass the server name as parameter for a XMLA script?

Question

I have a PROCEDURE sp_backupSSAS - it backups SSAS databases. it works fine.

I had previous problems with it, but they were sorted out here: routine to backup ssas databases fails with: The XML for Analysis request timed out before it was completed

generally I try to have SSAS on a server of its own - let's say server_SSAS

therefore I use a different server - let's say SERVER_SQL to backup the SSAS databases in the server_SSAS.

for me to run my procedure sp_backupSSAS that lives on server_SQL I first create a table type:

CREATE TYPE SSAS_DBs_to_Backup AS TABLE 
( DBName sysname not null 
 ,LocationName varchar(1008)
);
GO

This table type is used when I call the procedure. It tells me which database(s) to backup and the location where to put them (in the SERVER_SSAS)

From inside a job on SERVER_SQL:

DECLARE @DBS AS SSAS_DBs_to_Backup 
INSERT INTO @DBS (DBName, LocationName) VALUES ('MY Company BI LIVE','H:\SQLBackups\')
EXEC sp_backupSSAS @DBS

This creates a backup on my server_SSAS which is called SQLBILON1:

enter image description here

That is all working fine, however, I would like to pass the name of the server_SSAS which in this case was SQLBILON1 as a parameter, and NOT have it hard-coded inside the procedure.

how can I achieve this?

the full code of the procedure is here:

--SELECT @@SERVERNAME
PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME() 
PRINT ''
USE MASTER 
GO
--==================================================================================
--create a table type to contain the list of SSAS databases we want to backup
--==================================================================================
CREATE TYPE SSAS_DBs_to_Backup AS TABLE 
( DBName sysname not null 
 ,LocationName varchar(1008)
);
GO
BEGIN TRY
DROP PROCEDURE sp_backupSSAS
END TRY
BEGIN CATCH
END CATCH
GO
CREATE PROCEDURE sp_backupSSAS
 @DBS SSAS_DBs_to_BAckup READONLY,
 @Server VARCHAR(108)
/*
=======================================================================
Script : PROCEDURE sp_backupSSAS
Author : Marcelo Miorelli
Date : 03-NOV-2014
Desc : 
 Backup the SSAS databases that are on table @DBS
 needs the LINKED server to SSAS - in this case [SQLBILON1]
 On the first version I left that static.
Credits: the credits for the Original idea go to Theo Ekelmans
 http://www.sqlservercentral.com/scripts/automatic/97696/
Usage : 
DECLARE @DBS AS SSAS_DBs_to_BAckup 
--INSERT INTO @DBS (DBName, LocationName) VALUES ('Boden BI LIVE','H:\SQLBackups\SQLBILON1\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SQLBILON1\')
EXEC sp_backupSSAS @DBS
=======================================================================
History
Date Action User Desc
-----------------------------------------------------------------------
<Add Date> Created <add your name> <add description of change>
=======================================================================
*/
--======================================
-- describe primary blocks of processing
--======================================
------------------------------------------------
-- describe action of logical groups of commands
------------------------------------------------
-- describe individual actions within a command set
WITH ENCRYPTION
AS 
SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @name VARCHAR(50) -- Cube name 
DECLARE @path VARCHAR(256) -- Backup path
DECLARE @fileName VARCHAR(256) -- Backup filename 
DECLARE @DT VARCHAR(20) -- Used for optional file name timestamp
Declare @XMLA nvarchar(4000) -- The SSAS command in XML format
-- Change timestamp to this format: _YYYY-MM-DD_HHMMSS
Set @DT = '_' + Replace(Replace(Convert(nvarchar, getdate(), 120), ':', ''), ' ', '_');
DECLARE @RADHE TABLE ( i int not null identity(1,1) PRIMARY KEY CLUSTERED,
 CATALOG_NAME SYSNAME NOT NULL)
DECLARE @I INT
DECLARE @Z INT
DECLARE @log NVARCHAR(MAX)
 ,@vCrlf CHAR(2);
SELECT @log = ''
 ,@vCrlf = CHAR(13)+CHAR(10);
INSERT INTO @RADHE (CATALOG_NAME) 
SELECT CATALOG_NAME 
FROM openquery([SQLBILON1], 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as a
SELECT @Z = @@ROWCOUNT
SELECT @I = 1
WHILE @I <= @Z BEGIN
 SELECT @NAME = CATALOG_NAME 
 FROM @RADHE WHERE I = @I
 --====================================================================
 -- check whether the database is contained in @DBS 
 -- get the path from @DBS where you want to backup the database
 -- if yes then backup the database, if not then skip
 --====================================================================
 SELECT @PATH = NULL
 SELECT @path = LocationName FROM @DBS WHERE DBName = @NAME 
 IF (@PATH IS NOT NULL) BEGIN 
 ---Create the XMLA string (add a DT stamp to the filename)
 Set @XMLA = N'
 <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>' + @name + '</DatabaseID>
 </Object>
 <File>' + @path + @name + @DT + '.abf</File>
 <AllowOverwrite>true</AllowOverwrite>
 </Backup>
 ';
 BEGIN TRY
 --print CAST (@XMLA AS NTEXT)
 -- Execute the string across the linked server (SSAS)
 Exec (@XMLA) At [SQLBILON1]
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage
 SELECT @log = @log + '-- Exception was caught --' + CONVERT(VARCHAR(24), GETDATE(), 113) + @vCrlf +
 'The error number:' + coalesce(cast ( ERROR_NUMBER() as varchar(max)), 'No Info') + @vCrlf 
 SELECT @log = @log + 'Severity: ' + coalesce(cast ( ERROR_SEVERITY() as varchar(max)), 'No Info') + @vCrlf +
 'State: ' + coalesce(cast ( ERROR_STATE() as varchar(max)), 'No Info') + @vCrlf 
 SELECT @log = @log + 'Procedure: ' + coalesce(cast ( coalesce(ERROR_PROCEDURE(),'No Info') as varchar(max)), 'No Info') + @vCrlf +
 'Line number: ' + coalesce(cast ( ERROR_LINE() as varchar(max)), 'No Info') + @vCrlf 
 SELECT @log = @log + 'Error Message: ' + cast ( coalesce(ERROR_MESSAGE(),'No Info') as varchar(max)) + @vCrlf 
 SELECT @log = @log + ' -- End of Procedure ' + CONVERT(VARCHAR(24), GETDATE(), 113) + @vCrlf 
 PRINT CAST(@LOG AS NTEXT) + @vCrlf 
 WHILE @@TRANCOUNT > 0 
 ROLLBACK
 END CATCH
 END --IF
 SELECT @I += 1
END 
GO
exec sys.sp_MS_marksystemobject 'sp_backupSSAS'
GO

Answer 1

this procedure runs XMLA statements in a SSAS server from a SQL Server.

first we need to create the linked server to the SSAS. this you can see on the link below:

How can I reduce OLEDB wait type in a SSAS linked server connection?

After that I have done some changes on the procedure I have posted on the question.

I will share the script here, so that it is documented.

--SELECT @@SERVERNAME
PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME() 
PRINT ''
USE MASTER 
GO
BEGIN TRY
exec sp_save @TableName ='sp_backupSSAS',@permissions='Y'
END TRY
BEGIN CATCH
print error_message()
END CATCH
GO
--==================================================================================
--create a table type to contain the list of SSAS databases we want to backup
--==================================================================================
BEGIN TRY
IF NOT EXISTS (select * from sys.types where name = 'SSAS_DBs_to_Backup') 
 CREATE TYPE SSAS_DBs_to_Backup AS TABLE 
 ( DBName sysname not null 
 ,LocationName varchar(1008)
 );
END TRY
BEGIN CATCH
print error_message()
END CATCH
GO
BEGIN TRY
DROP PROCEDURE sp_backupSSAS
END TRY
BEGIN CATCH
END CATCH
GO
CREATE PROCEDURE sp_backupSSAS
 @DBS SSAS_DBs_to_BAckup READONLY,
 @SERVER NVARCHAR(128)
/*
=======================================================================
Script : PROCEDURE sp_backupSSAS
Author : Marcelo Miorelli
Date : 03-NOV-2014
Desc : 
 Backup the SSAS databases that are on table @DBS
 needs the LINKED server to SSAS - in this case [SQLBILON1]
 On the first version I left that static.
Credits: the credits for the Original idea go to Theo Ekelmans
 http://www.sqlservercentral.com/scripts/automatic/97696/
Parameters: the @Server must be an already created linked server to the SSAS instance
 regarding the @DBS
 DBname is the database name
 LocationName is the path how it is seen inside the @Server.
 for instance H:\SSASBackup\ is a folder inside the server SASBIDEV01 
Usage : 
DECLARE @DBS AS SSAS_DBs_to_BAckup 
INSERT INTO @DBS (DBName, LocationName) VALUES ('Boden BI SSAS AutoDeploy 9_01','H:\SSASBackup\')
EXEC sp_backupSSAS @DBS, 'SASBIDEV01'
=======================================================================
History
Date Action User Desc
-----------------------------------------------------------------------
15-sep-2015 added feature marcelo miorelli added the @server as a parameter 
 @server is a linked server to a SSAS server.
 changed the dynamic sql to run XMLA commands on SSAS
=======================================================================
*/
--======================================
-- describe primary blocks of processing
--======================================
------------------------------------------------
-- describe action of logical groups of commands
------------------------------------------------
-- describe individual actions within a command set
WITH ENCRYPTION
AS 
SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @sql NVARCHAR(MAX)
DECLARE @name VARCHAR(50) -- Cube name 
DECLARE @path VARCHAR(256) -- Backup path
DECLARE @fileName VARCHAR(256) -- Backup filename 
DECLARE @DT VARCHAR(20) -- Used for optional file name timestamp
Declare @XMLA nvarchar(4000) -- The SSAS command in XML format
-- Change timestamp to this format: _YYYY-MM-DD_HHMMSS
Set @DT = '_' + Replace(Replace(Convert(nvarchar, getdate(), 120), ':', ''), ' ', '_');
DECLARE @RADHE TABLE ( i int not null identity(1,1) PRIMARY KEY CLUSTERED,
 CATALOG_NAME SYSNAME NOT NULL)
DECLARE @I INT
DECLARE @Z INT
DECLARE @log NVARCHAR(MAX)
 ,@vCrlf CHAR(2);
SELECT @log = ''
 ,@vCrlf = CHAR(13)+CHAR(10);
---------------------------------------------------------------------------------------------
SELECT @SERVER = COALESCE(UPPER(LTRIM(RTRIM(@SERVER))),@@SERVERNAME)
IF NOT EXISTS(
 SELECT * 
 FROM SYS.SERVERS
 WHERE NAME = @SERVER
 AND provider = 'MSOLAP'
 AND is_data_access_enabled = 1
) BEGIN
 SELECT @SQL = 'The server called %s cound not be found, or does not have data access enabled.' + @vCrlf + @vCrlf +
 'sp_backupSSAS usage:' + @vCrlf +
 'DECLARE @DBS AS SSAS_DBs_to_BAckup ' + @vCrlf +
 'INSERT INTO @DBS (DBName, LocationName) VALUES (''Pyramid Demo 2013'',''\\SQLREPLON1\SQLBackups\SQLBILON1\'') ' + @vCrlf +
 'EXEC sp_backupSSAS @DBS, ''SASBI01''' + @vCrlf 
 RAISERROR(@SQL ,16,1,@SERVER)
 RETURN (-1)
END 
---------------------------------------------------------------------------------------------
 SELECT @SQL = 'SELECT CATALOG_NAME 
 FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + 'SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS' + '''' + ')'
 INSERT INTO @Radhe
 exec ( @sql)
SELECT @Z = @@ROWCOUNT
SELECT @I = 1
WHILE @I <= @Z BEGIN
 SELECT @NAME = CATALOG_NAME 
 FROM @RADHE WHERE I = @I
 --====================================================================
 -- check whether the database is contained in @DBS 
 -- get the path from @DBS where you want to backup the database
 -- if yes then backup the database, if not then skip
 --====================================================================
 SELECT @PATH = NULL
 SELECT @path = LocationName FROM @DBS WHERE DBName = @NAME 
 IF (@PATH IS NOT NULL) BEGIN 
 ---Create the XMLA string (add a DT stamp to the filename)
 Set @XMLA = N'
 <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>' + @name + '</DatabaseID>
 </Object>
 <File>' + @path + @name + @DT + '.abf</File>
 <AllowOverwrite>true</AllowOverwrite>
 </Backup>
 ';
 BEGIN TRY
 print CAST (@XMLA AS NTEXT)
 --Execute the string across the linked server (SSAS)
 SELECT @SQL = 'SELECT 
 [n],
 [Login_Name],
 [Login_From],
 [Account_Type] 
 FROM OPENQUERY(' + quotename(@SERVER ) + ',' + '''' + @XMLA + '''' + ')'
 SELECT @SQL = 'exec (' + '''' + @XMLA + '''' + ')' + ' AT ' + quotename(@SERVER )
 print CAST (@sql AS NTEXT)
 exec (@SQL)
 --Exec (@XMLA) At [SQLBILON1]
 print 'backup is done - check the folder ' + @path
 END TRY
 BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState,
 ERROR_PROCEDURE() AS ErrorProcedure,
 ERROR_LINE() AS ErrorLine,
 ERROR_MESSAGE() AS ErrorMessage
 SELECT @log = @log + '-- Exception was caught --' + CONVERT(VARCHAR(24), GETDATE(), 113) + @vCrlf +
 'The error number:' + coalesce(cast ( ERROR_NUMBER() as varchar(max)), 'No Info') + @vCrlf 
 SELECT @log = @log + 'Severity: ' + coalesce(cast ( ERROR_SEVERITY() as varchar(max)), 'No Info') + @vCrlf +
 'State: ' + coalesce(cast ( ERROR_STATE() as varchar(max)), 'No Info') + @vCrlf 
 SELECT @log = @log + 'Procedure: ' + coalesce(cast ( coalesce(ERROR_PROCEDURE(),'No Info') as varchar(max)), 'No Info') + @vCrlf +
 'Line number: ' + coalesce(cast ( ERROR_LINE() as varchar(max)), 'No Info') + @vCrlf 
 SELECT @log = @log + 'Error Message: ' + cast ( coalesce(ERROR_MESSAGE(),'No Info') as varchar(max)) + @vCrlf 
 SELECT @log = @log + ' -- End of Procedure ' + CONVERT(VARCHAR(24), GETDATE(), 113) + @vCrlf 
 PRINT CAST(@LOG AS NTEXT) 
 WHILE @@TRANCOUNT > 0 
 ROLLBACK
 END CATCH
 END --IF
 SELECT @I += 1
END 
GO
exec sys.sp_MS_marksystemobject 'sp_backupSSAS'
GO
print 'The Procedure sp_backupSSAS has been created.'
GO

Regarding the usage:

DECLARE @DBS AS SSAS_DBs_to_BAckup 
INSERT INTO @DBS (DBName, LocationName) VALUES ('Boden BI SSAS AutoDeploy 9_01','H:\SSASBackup\')
EXEC sp_backupSSAS @DBS, 'SASBIDEV01'

that means that this procedure will backup the ssas database called Boden BI SSAS AutoDeploy 9_01 on server SASBIDEV01 and the backup will be saved on server SASBIDEV01 folder *H:\SSASBackup*