November 11, 2009 at 12:38 pm
set @sql = 'ALTER DATABASE ' + @dbname + ' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
exec (@sQL)
I build the above SQL statement then use "EXEC" to execute.
The problem I am having is I need to know if the ALTER DATABASE is failing, because if it is I want to do something else instead of just aborting the complete job stream.
Any ideas?
November 11, 2009 at 12:40 pm
Couple of options.
You could do a second command that would select the characteristic desired for the stated database and find out if it's changed or not.
You could switch to sp_exececutesql and use an output parameter that returns the error code.
Might be other options, but those are the first ones that came to mind.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 12:52 pm
What is happening is I am getting a hard error such as the database does not exist...but the job just stops.
I want to catch that error so I can continue with the other databases in my list.
November 11, 2009 at 1:41 pm
How does the variable @dbname get assigned a value? Is it an input parameter?
If so, I'd do something like add this before executing the command:
if exists (select * from sys.databases where name = @dbname)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 2:27 pm
As you are scripting this up I presume you need to do it on a regular basis. Out of curiosity why do you have a need to regularly put your databases in restricted mode?
I only ask in case the need to do this is redundant.
---------------------------------------------------------------------
November 12, 2009 at 6:09 am
Yes we have to lock them down due to a process/program that runs against them everyday.
These databases are more like reporting databases and is only used for this program and reports that get created and emailed to users.
We copy the production databases over to this one every night.
November 12, 2009 at 7:59 am
fair enough 🙂
---------------------------------------------------------------------
November 13, 2009 at 6:27 am
if you're runnin sql 2005/2008 you could catch the error usin a try/catch block as one option. Also you could query sysdatabases in an if block around the call and then only alter the database if it exists. That would be a little more proactive then try/catch.
November 13, 2009 at 8:57 am
unfortunetly it is sql2000
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply