Post reply

How can I pick the IDs of all AS databases?

  • November 6, 2015 at 12:59 am

    #304984

    Hi,

    I have written a script to create a job that saves the AS databases in a backup file, like this (it's only a snippet with examples):

    ...

    DECLARE @db1 nvarchar (128) = N''DBName''

    DECLARE @db1_id nvarchar (128) = N''DBName''

    DECLARE @step1 nvarchar (128) = @category +'' ''+@db1

    DECLARE @sql_step1 nvarchar(max) = N''<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

    <Object>

    <DatabaseID>''+@db1_id+''</DatabaseID>

    </Object>

    <AllowOverwrite>1</AllowOverwrite>

    <File>\\xxx.xxx.xxx.xxx\path\''+@as+''\''+@day+''\''+@db1+''.abf</File>

    </Backup>''

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

    @job_id=@jobId,

    @step_name=@step1,

    @step_id=2,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0,

    @subsystem=N''ANALYSISCOMMAND'',

    @command=@sql_step1,

    @server=N''.'',

    @database_name=N''master'',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    ...

    The jobs works well, but it's not flexible, because it may happen that the AS databases are changed, deleted or the controllers create new ones. Until now I have to customize the job manually.

    So I like to pick the IDs of any AS databases on the AS server and want to put it (in T-SQL) in a cursor into a variable for making the job creation script more flexible.

    Do you have any ideas?

  • November 6, 2015 at 1:37 am

    #1838158

    Hi,

    The following query will return the list of databases on your AS instance.

    SELECT catalog_name as [DatabaseName] FROM $SYSTEM.DBSCHEMA_CATALOGS

    You'll have to create a linked server to the AS server and call a stored procedure (SP) that would dynamically execute the query because it executes in a MDX query editor. Then it's easy to store the SPs results in a temp table.

    Igor Micev,
    My blog: www.igormicev.com

  • November 6, 2015 at 2:27 am

    #1838161

    Thank you. I created the linked server "TESTAS" and the connection test is okay.

    SELECT * INTO [Test].[dbo].[TestAS] FROM

    OPENQUERY([TESTAS],

    'SELECT [CATALOG_NAME] FROM $system.DBSCHEMA_CATALOGS')

  • November 6, 2015 at 2:57 am

    #1838170

    This code works at my server:

    if object_id('tempdb..[#tmpASDbs]') is not null

    drop table #tmpASDbs

    create table #tmpASDbs(ASDBName nvarchar(100))

    declare @dynSql nvarchar(4000)

    set @dynSql =N'INSERT INTO #tmpASDbs(ASDBName)

    SELECT * FROM OPENQUERY(LINKED_OLAP,''SELECT [catalog_name] as [DatabaseName] FROM $SYSTEM.DBSCHEMA_CATALOGS'')'

    exec sp_executesql @dynSql

    select * from #tmpASDbs

    Igor Micev,
    My blog: www.igormicev.com

  • November 6, 2015 at 4:25 am

    #1838183

    To get the object-ID for my job I would use something like this:

    SELECT *

    FROM

    OPENQUERY([TESTAS],'SELECT OBJECT_ID FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY WHERE OBJECT_PARENT_PATH = ''servername.Databases''')

    Igor, thank you for your great support!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply