regarding logic in stored proc

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

Transact-SQL (2000)

regarding logic in stored proc

Author

Topic

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-10 : 16:36:52

Hi,

I am using sproc to backup logs for databases in the sproc i am using cursor to get the database names
and passing the dbname parameter into
xp_Sqlmaint procedure ,it's working fine as it should

but when any of the database log backup fails so i am doing like this but this is not working

exec xp_sqlmaint @dbname............

if @@error <> 0
Raiserror(%s 'bacup fa.....16,1)

I am not able do this although i am getting the error on one database
because i think when it executes the proc it thinks it's successfull.

Is that true or am i doing wrong or is there a better way to do this

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-10 : 16:48:50

It is possible that xp_sqlmaint isn't passing the error back to you. So @@ERROR would be 0 if that is the case.

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-10 : 17:15:28

That's the problem i am having
is there any other way to do this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-10 : 17:19:13

Yeah, don't use xp_sqlmaint.

Why not use explicit BACKUP commands? You could use dynamic sql and a cursor to get the desired results.

If you explain what your procedure does, then we could help provide a (better or alternate) solution.

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-10 : 19:11:09

The reason for using xp_Sqlmaint it has switch for reporting
using -Rpt text_fille
where as with the backup statement we can't do that

Thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-11 : 15:27:18

But you can write or append to a text file after the backup.
Sounds like you are trying to make a simple task complicated.

Backups are extremely important and should be as robust as possible. And don't forget to test them periodically - you never know whether one can be restored until you try it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-11 : 22:56:25

nr

how can i write or append to a text file
can u help me with that

Thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-12 : 00:48:44

see
http://www.nigelrivett.net/WriteTextFile.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources