calling backup to store procedure

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

General SQL Server Forums

New to SQL Server Programming

calling backup to store procedure

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2009-04-03 : 00:28:49

hi
i have use the store procedure of isp_Backup script to backup of db
i have run the sp then error is

Server: Msg 3021, Level 16, State 1, Procedure procname, Line 160
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a backup or restore operation within a transaction.

Server: Msg 3013, Level 16, State 1, Procedure proce name, Line 160
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

my os is window2003 server
sql server2000 sp4
version sql 8.00.2039

i am not understand which tranction is remove

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 03:12:12

quote:
Originally posted by amirs

hi
i have write the store procedure to backup of db
i have run the sp then error is

Server: Msg 3021, Level 16, State 1, Procedure "procname"
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a backup or restore operation within a transaction.

my os is window2003 server
sql server2000 sp4
version sql 8.00.2039

As it says, remove transaction from the stored procedure

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 10:35:12

>>i am not understand which tranction is remove

either in your SP or from within the code that calls your SP look for a transaction block and remove it. ie:

change:

BEGIN TRAN
<sql code>
COMMIT TRAN

to:

--BEGIN TRAN
<sql code>
--COMMIT TRAN

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources