Sql-Server

備份 ssas 數據庫的常式失敗，原因是：用於分析的 XML 請求在完成之前超時

  • June 7, 2017

我開發了自己的儲存過程，通過一個 LINKED SERVER，根據表值參數備份 SSAS 數據庫。

儲存過程的程式碼如下。

問題：

它適用於不太大的數據庫（可以說最多 4 GB），但是當數據庫更大（12 GB）時，它會給我以下錯誤消息：（我從計劃每晚執行的作業中執行它）。我想知道可能缺少什麼。

錯誤：

Executed as user: mycompany\SQLAgent
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">                  
<Object>                <DatabaseID>MYDATABASE BI LIVE</DatabaseID> </Object>               
<File>E:\SQLBackups\mydatabase BI LIVE_2014-11-04_235858.abf</File>
<AllowOverwrite>true</AllowOverwrite> </Backup>
[SQLSTATE 01000] (Message 0)  OLE DB provider "MSOLAP" for linked server "MYSSASSERVER" returned message "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". [SQLSTATE 01000] 
(Message 7412)  -- Exception was caught --05 Nov 2014 00:08:58:940  
The error number:7215  Severity: 17  State: 1  Procedure: sp_backupSSAS  
Line number: 126  Error Message: Could not execute statement on remote server 'myssasserver'.   
-- End of Procedure 05 Nov 2014 00:08:58:940 [SQLSTATE 01000] (Message 0).  The step succeeded.

程序 SP_BACKUPSSAS：

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_backupSSAS]    Script Date: 05/11/2014 10:19:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DROP PROCEDURE sp_backupSSAS

ALTER PROCEDURE [dbo].[sp_backupSSAS]
  @DBS  SSAS_DBs_to_BAckup READONLY


/*
=======================================================================
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 [SSASSERVER]
           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 ('MYCOMPANY BI LIVE','H:\SQLBackups\SSASSERVER\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SSASSERVER\')

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


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([SSASSERVER], '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 [SSASSERVER]

           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

參數定義：

USE [master]
GO

CREATE TYPE [dbo].[SSAS_DBs_to_BAckup] AS TABLE(
   [DBName] [sysname] NOT NULL,
   [LocationName] [varchar](1008) NULL
)
GO

如何呼叫儲存過程的範例：

DECLARE @DBS AS SSAS_DBs_to_BAckup 
--INSERT INTO @DBS (DBName, LocationName) VALUES ('MYCOMPANY BI LIVE','H:\SQLBackups\SSASSERVER\')
INSERT INTO @DBS (DBName, LocationName) VALUES ('Pyramid Demo 2013','\\SQLREPLON1\SQLBackups\SSASSERVER\')

EXEC sp_backupSSAS @DBS

當我右鍵點擊 SSMS 中的 SSAS 伺服器並查看屬性時，我看到的是：

在此處輸入圖像描述

除了@James 就送出超時和強制送出超時的 SSAS 設置的評論提出的意見之外，我在 SQL Server 上所做的更改最終使它工作。

EXEC sys.sp_configure N'remote query timeout (s)', N'6000'
GO
RECONFIGURE WITH OVERRIDE
GO

這與您在下圖中看到的值相同：您通過 sql server server 到達那裡，右鍵點擊屬性，連接。

在此處輸入圖像描述

以及在分析伺服器屬性上，“送出超時”和“強制送出超時”，如下圖所示。 在此處輸入圖像描述

現在我已經備份了 SSAS 數據庫並在開發伺服器中恢復了它，結果證明沒問題。

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

相關問答

Sql-Server

我需要一種方法來授予經過 sql 身份驗證的使用者對 windows 目錄的讀取權限

  • October 5, 2020
檢視問答
Sql-Server

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

  • June 6, 2019
檢視問答
Sql-Server

SSMS 連接，連結伺服器失敗

  • February 13, 2019
檢視問答
Sql-Server

SSAS 數據庫的管理員權限

  • November 22, 2018
檢視問答
Sql-Server

如何為連結伺服器啟用分佈式事務？

  • December 26, 2017
檢視問答
Sql-Server

從 SQL Server 到 SSAS 的連結伺服器不起作用 - 缺少什麼？

  • November 2, 2017
檢視問答