November 26, 2007 at 7:41 am
I attached a query that runs fine when all of the databases on an instance are online, but when even a single database is offline, it errors out. I need some way to add at the beginning something like "if db = offline, skip". Is there anyway to make this work? Shouldn't there be a list of the DB's on the instance someplace and there state? If so, couldn't I just say run the script against the db's that are online? Any help is greatly appreciated.
----------------------------Begin Script-----------------------------
declare @dbname varchar(200)
declare @mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go
----------------------------End Script-----------------------------
November 26, 2007 at 7:46 am
select * from sys.databases where [state] = 0
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 26, 2007 at 8:08 am
What do you mean that I can just open it? The script is run as part of a BI package that is designed to run on over 100 databases. Also, selecting from the sys.* is not an option as it is designed to run on SQL Server 2000 instances as well.
-Kyle
November 26, 2007 at 8:14 am
Well, you never mentioned that it has to work on 2000 and 2005.
There may be a better way, but this works, as any database that isn't available will return a NULL version.
SELECT * FROM MASTER.dbo.sysdatabases WHERE version IS NULL
Oh, and the Envelope thing is my tag line, not directly related to your question.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 26, 2007 at 2:36 pm
change the database cursor to:
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb') and databasepropertyex(name,'Status') = 'ONLINE'
Order by name
* Noel
November 26, 2007 at 2:39 pm
The script is run as part of a BI package that is designed to run on over 100 databases. Also, selecting from the sys.* is not an option as it is designed to run on SQL Server 2000 instances as well.
-Kyle
November 26, 2007 at 2:41 pm
Kyle Schlapkohl (11/26/2007)
The script is run as part of a BI package that is designed to run on over 100 databases. Also, selecting from the sys.* is not an option as it is designed to run on SQL Server 2000 instances as well.-Kyle
The script I posted runs VERY WELL on 2000 and 2005 😉
Cheers,
* Noel
November 26, 2007 at 2:43 pm
My mistake, sorry and thanks for the information. It is of great help.
-Kyle
November 26, 2007 at 2:45 pm
Happy to help 😀
* Noel
November 27, 2007 at 7:43 am
Beautiful, the script works great. The final one looks like this...
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb','master','model','msdb') and databasepropertyex(name,'Status') = 'ONLINE'
Order by name
The only change being to add master, model, and msdb to the things it is not supposed to get. Once again thanks for all of your help.
-Kyle
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply