Tsql or Script to delete databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Tsql or Script to delete databases

Author

Topic

dgerads@ingdirect.com
Starting Member

2 Posts

Posted - 2008-01-15 : 16:36:34

I'm looking for a stored proc, tsql, or script that will query out the SSAS databases and drop them. We have processes that creates them when ran, and it keeps adding SSAS databases.

Thanks in advance for your help on this. - Dennis

dgerads@ingdirect.com
Starting Member

2 Posts

Posted - 2008-02-28 : 11:56:14

I have found a way using VBScript and putting it into a DTS package (ActiveX):

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()

Dim dsoServer
Dim dsoDatabase

Set dsoServer = CreateObject("DSO.Server")
Set dsoDatabase = CreateObject("DSO.Database")

' Connect to the AS server.
dsoServer.Connect "LocalHost"

' For each MDStore AS database object on the server, remove it.
For Each dsoDatabase In dsoServer.MDStores
dsoServer.MDStores.Remove dsoDataBase.Name
' MsgBox "Database Name: " & dsoDataBase.Name & _
' " - Desc: " & dsoDataBase.Description
Next

Main = DTSTaskExecResult_Success

End Function

- Dennis

Dennis

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-28 : 12:55:15

use master
go

declare @DBNAME as NVarchar(255)
declare @CMD as NVarchar(2000)

declare GetData cursor for
select name from sysdatabases
where name like 'SOME WILDCARD TO SELECT DATABSES (SSAS%) ??'
AND name not in ('master', 'model', 'pubs', 'tempdb', 'voicecom_apps', 'msdb')
open GetData
fetch GetData into @DBNAME;

while @@rowcount = 1
BEGIN
set @CMD = 'DROP DATABASE ' + @DBNAME
-- exec sp_executesql @CMD --UNCOMMENT THIS LIKE TO ACTUALLY DROP THE DATABSE
print @CMD
fetch GetData into @DBNAME;
END
close GetData
deallocate GetData

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-28 : 13:35:56

quote:
Originally posted by jhocutt

use master
go

declare @DBNAME as NVarchar(255)
declare @CMD as NVarchar(2000)

declare GetData cursor for
select name from sysdatabases
where name like 'SOME WILDCARD TO SELECT DATABSES (SSAS%) ??'
AND name not in ('master', 'model', 'pubs', 'tempdb', 'voicecom_apps', 'msdb')
open GetData
fetch GetData into @DBNAME;

while @@rowcount = 1
BEGIN
set @CMD = 'DROP DATABASE ' + @DBNAME
-- exec sp_executesql @CMD --UNCOMMENT THIS LIKE TO ACTUALLY DROP THE DATABSE
print @CMD
fetch GetData into @DBNAME;
END
close GetData
deallocate GetData

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

this script is for dropping databases in the relational engine, not SSAS.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources