DTS on SQL 7 and RAISERROR - SQL Server Forums

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

Import/Export (DTS) and Replication (2000)

DTS on SQL 7 and RAISERROR

Author

Topic

Jay99

468 Posts

Posted - 2002-03-27 : 16:41:34

I am having trouble with the execute sql task on SQL 7 sp3 . . .

Let's say I have ...


create ProcA
as
exec ProcB
if @@error <> 0
    raiserror('Error in ProcA',16,1)
go

create ProcB
as
raiseror('Error in ProcB',16,1)
go

If I create an execute sql task like this...


exec ProcA

... it reports success on execution.

If I create the task like this...


exec ProcB

... it reports failure on execution.

In fact, if I create the task like this...


select '<O>'
raiserror('Error in task',16,1)

... it reports success.

If I create the task like this...


raiserror('Error in task',16,1)
select '<O>'

... it reports failure.

Now through my mind-bending powers of reason, it seems like an Execute SQL Task will only fail if the FIRST thing it gets back is an error message, rather than relying on the @@error, like I would expect. I have confirmed that the above examples work as expected in 2k.

Here is what I am looking for:
1. Can anyone confirm my findings?
2. Am I missing something or is this behavior unexpected?
3. Does anyone have a good work-around that will allow me to control my package flow based on the success or failure of a called Proc?

Jay
<O>

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-27 : 17:13:11

Probably has to do with:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q238523&SD=MSKB&

Though that says it is fixed in SP2. Workarounds suggested in the article.

-Chad

Jay99

468 Posts

Posted - 2002-03-28 : 09:34:41

I tried the workaround and used an process task to call osql to call procA. From the commandline I clearly see the 'Error in ProcB' message, but DTS reports sucdess on the step.

Let me ask a different question. I am using DTS only because I want to execute 3 stored procedures at the same time. Is there a way to do that in a job step?

Jay
<O>

Jay99

468 Posts

Posted - 2002-03-28 : 10:09:51

This is driving me insane ... I am having the same experience using a SQL Task, writing an ActiveX task that opens a connection and runs the proc, writing a Process Task that runs osql or isql to run the proc . . .

Has anyone written a DTS Package in SQL Server 7(sp3), that can fail a task when RAISERROR is issued in a stored procedure (and RAISERROR is not the first command in the proc)? Has anyone every writtent a SQL 7 DTS Package that does any error handling?

Jay
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-28 : 11:55:10

How about throwing an error with a severity higher than 16?

Jay99

468 Posts

Posted - 2002-03-28 : 12:04:54

quote:

How about throwing an error with a severity higher than 16?

No dice . . .

Looks like I will have to use an ActiveX task to create a connection and a command and then instead of raising and error, use an output parm.

DTS in SQL 7 is very immature.

Rob how bout my second question? The only reason I am using DTS is so that I can run the same proc 3 times with different parms. They may take quite some time and should be run in parallel. They need to be scheduled. Is there a way to do that in a job?

Jay
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-28 : 13:12:34

This probably won't work, but the only thing I can think of is to have 3 separate jobs that are sceduled to go off at the same time, or within a few seconds of each other. You might also be able to have the 1st job fire off the 2nd and 3rd jobs, and then continue on its own.

Jay99

468 Posts

Posted - 2002-03-28 : 13:41:15

I hate having to pick between unfavorable bad options . . . I though about the multiple job thing, but I don't want to end up with 40 or 50 jobs . . .

My latest bad idea is


create proc proca
as
...do stuff...

if @@error <> 0
begin
   alter proc ProcA_Error
   as
   raiserror
end
else
begin
   alter proc ProcA_Error
   as
   print 'no error'
end

Then have the task running ProcA on Success exec ProcA_error . . .

stoopid,stoopid,stoopid

Jay
<O>

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-03-28 : 16:55:47

Jay,
I am running SQL 7 sp3 on about 12 servers and can not reproduce your problem on any of them. When I run "exec procA" in a SQL Task my package fails and says error in procB. Is it possible that the server is on SP3, but your client tools are not? The article that chadmat pointed out could be occuring if you're actaully creating a sp1 or sp0 dts package because your client tools haven't been upgraded.

Jeff Banschbach
Consultant, MCDBA

Jay99

468 Posts

Posted - 2002-03-28 : 18:54:27

quote:

Jay,
I am running SQL 7 sp3 on about 12 servers and can not reproduce your problem on any of them. When I run "exec procA" in a SQL Task my package fails and says error in procB. Is it possible that the server is on SP3, but your client tools are not? The article that chadmat pointed out could be occuring if you're actaully creating a sp1 or sp0 dts package because your client tools haven't been upgraded.

Jeff Banschbach
Consultant, MCDBA

Really? crap . . . both client and server are my desktop machine and its at SP3.

quote:

In fact, if I create the task like this...
select '<O>'
raiserror('Error in task',16,1)
... it reports success.

If I create the task like this...
raiserror('Error in task',16,1)
select '<O>'
... it reports failure.

What about that? Both failure for you?

Jay
<O>

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-03-29 : 11:11:07

Both of those fail for me. I don't have any other ideas as to why this would be happening, but I'll do some more digging.

Jeff Banschbach
Consultant, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources