<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Advertise
Write for us
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
General
»
Dynamic SQL Statement Error Trapping
Dynamic SQL Statement Error Trapping
Rate Topic
Display Mode
Topic Options
Author
Message
Carol Adams
Carol Adams
Posted Wednesday, November 11, 2009 12:38 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, March 15, 2010 4:37 PM
Points: 49,
Visits: 130
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?
Post #817401
GSquared
GSquared
Posted Wednesday, November 11, 2009 12:40 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,681,
Visits: 4,953
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.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #817404
Carol Adams
Carol Adams
Posted Wednesday, November 11, 2009 12:52 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, March 15, 2010 4:37 PM
Points: 49,
Visits: 130
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.
Post #817409
GSquared
GSquared
Posted Wednesday, November 11, 2009 1:41 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,681,
Visits: 4,953
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)
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #817433
george sibbald
george sibbald
Posted Wednesday, November 11, 2009 2:27 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 3:54 PM
Points: 2,387,
Visits: 5,202
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.
Post #817460
Carol Adams
Carol Adams
Posted Thursday, November 12, 2009 6:09 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, March 15, 2010 4:37 PM
Points: 49,
Visits: 130
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.
Post #817760
george sibbald
george sibbald
Posted Thursday, November 12, 2009 7:59 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 3:54 PM
Points: 2,387,
Visits: 5,202
fair enough
Post #817854
Michael H Lutz
Michael H Lutz
Posted Friday, November 13, 2009 6:27 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, February 02, 2010 9:01 AM
Points: 13,
Visits: 53
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.
Post #818444
Carol Adams
Carol Adams
Posted Friday, November 13, 2009 8:57 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, March 15, 2010 4:37 PM
Points: 49,
Visits: 130
unfortunetly it is sql2000
Post #818592
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use
