[dba-SQLServer] DTS works like a transaction?

Not sure what you refer to as "such a beast", but error control is
usually implemented within procedures as:

-- Within the procedure...
	begin transaction
	--...action query or DDL statement...
	if @@error != 0 begin
	   raiserror ('your custom message goes here', 15, 1)
	   rollback transaction
	   return (1)
	end
	commit transaction
	return (0)

-- Within the script of other stored procedure that calls the above...
	declare @RetVal tinyint
	exec @RetVal = sp_your_stored_procedure @param1, @param2,
param...n
	if @RetVal != 0 begin
	   -- Here you make determination whether to procede 
	   -- with the rest of the logic or terminate/divert 
	   -- the execution of the script.
	end

Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Martin
Reid
Sent: Monday, May 03, 2004 10:00 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] DTS works like a transaction?

Robert

Do you have an example fo such a beast? I am particulary interested in
something that carries out operation 1 then 2 then 3 etc?

Martin



----- Original Message ----- 
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Monday, May 03, 2004 2:50 PM
Subject: RE: [dba-SQLServer] DTS works like a transaction?


> DTS can be very elaborate and very uncontrollable at the same time.
But
> if you store all the calls into a SQL Task within the package and
> structure each sp with proper error validation/error raising
constructs,
> as well as return code validation after execution of each sp, - your
> package will be a smooth sailing.
>
> Robert Djabarov
> SQL Server & UDB
> Sr. SQL Server Administrator
> Phone: (210)  913-3148
> Pager: (210) 753-3148
> 9800 Fredericksburg Rd. San Antonio, TX  78288
> www.usaa.com
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of S D
> Sent: Monday, May 03, 2004 7:04 AM
> To: sqlserver667
> Subject: [dba-SQLServer] DTS works like a transaction?
>
> Hi group,
>
> I'm creating an impact analysis for my first really big SQL server
> project.
> I need to fire a lot (30+) sp's. I want to do this using a DTS
package.
> I think a job is not intended for this and a job is too slow.
>
> Question regarding DTS:
> Does a DTS work like a transaction?
> So if one SP fails will all actions rollback?
>
> TIA
>
> Sander
>
>
> ---------------------------------
> Do you Yahoo!?
> Win a $20,000 Career Makeover at Yahoo! HotJobs
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com