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.
| Author |
Topic |
|
thomadma
Starting Member
8 Posts |
 Posted - 2005-01-10 : 17:09:50
|
| Hi,I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?Thanks,Maria |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-10 : 17:33:46
|
| Encapsulate each process in it's own procedure. Each procedure produces a RETURN value for success or failure. You can then capture the return value of each action and have the master script act accordingly. Look up RETURN in Books Online. They have a section called "Return status codes" that will explain the process and give examples. This is what we use for the exact same process you have.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
|
|
|
|
|
Automating a Backup and Restore across Servers