Sql-Server

如何將伺服器名稱作為 XMLA 腳本的參數傳遞？

  • January 10, 2017

我有一個 PROCEDURE sp_backupSSAS - 它備份 SSAS 數據庫。 它工作正常。

我以前遇到過問題，但在這裡解決了： 備份 ssas 數據庫的常式失敗，原因是：用於分析的 XML 請求在完成之前超時

通常我會嘗試在自己的伺服器上安裝 SSAS - 比如說server_SSAS

因此我使用不同的伺服器 - 比如說SERVER_SQL來備份server_SSAS中的 SSAS 數據庫。

為了讓我執行位於 server_SQL 上的過程 sp_backupSSAS，我首先創建了一個表類型：

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

當我呼叫過程時使用此表類型。它告訴我要備份哪些數據庫以及放置它們的位置（在SERVER_SSAS中）

從 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

這會在我的 server_SSAS 上創建一個名為 SQLBILON1 的備份：

在此處輸入圖像描述

這一切都很好，但是，我想傳遞server_SSAS的名稱，在這種情況下是 SQLBILON1 作為參數，而不是在過程中硬編碼。

我怎樣才能做到這一點？

該程序的完整程式碼在這裡：

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

此過程從 SQL Server 在 SSAS 伺服器中執行 XMLA 語句。

首先，我們需要創建到 SSAS 的連結伺服器。您可以在下面的連結中看到：

如何減少 SSAS 連結伺服器連接中的 OLEDB 等待類型？

之後，我對我在問題上發布的程序進行了一些更改。

我將在這里分享腳本，以便記錄在案。

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

關於用法：

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'

> > 這意味著此過程將在伺服器 SASBIDEV01 上備份名為 Boden BI SSAS AutoDeploy 9_01 的 ssas 數據庫 > ，並且**備份將保存在伺服器SASBIDEV01 文件夾 H:\SSASBackup > > >

引用自：https://dba.stackexchange.com/questions/114980

相關問答

Sql-Server

如何檢查 SSAS 數據庫的執行狀況？它的備份呢？

  • October 7, 2016
檢視問答
Sql-Server

如何查找 SSAS 數據庫的備份歷史記錄

  • March 7, 2022
檢視問答
Sql-Server

從 SQL Server 中保存的備份估計最佳還原時間

  • February 26, 2022
檢視問答
Sql-Server

DBA 新工作的第一天 - 檢查備份和安全性 - 如何？還應該檢查什麼？

  • February 1, 2022
檢視問答
Sql-Server

不同硬體上的 SQL 完整數據庫備份性能

  • November 18, 2021
檢視問答
Sql-Server

網路備份的替代品

  • November 16, 2021
檢視問答