How can I pick the IDs of all AS databases?

Question

Post reply

How can I pick the IDs of all AS databases?

ratloser99 36160

SSC-Addicted

Points: 430
More actions
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?

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

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

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

ratloser99 36160 SSC-Addicted Points: 430 More actions · Answer 2

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

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

ratloser99 36160 SSC-Addicted Points: 430 More actions · Answer 4

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!