I want to generate an error - 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)

I want to generate an error

Author

Topic

swamp
Starting Member

1 Post

Posted - 2006-01-26 : 12:16:52

i have created a dts package that selects rows that have been altered in the previous hour and send them to a csv file
this works like a charm
however I have yet another thing namely

I set the records that have been altered by using a trigger and

i give a value to a field ( i called this field isupdate )
now ofcourse it is possible that no changes have been done in this hour but as the dts is scheduled to run every hour it returns a csv file without any data

I want to prevent the dts to run if there is no data in the query

so i created a stored procedure and i want to let it make an error if no data is selected in the qry so that the others steps in the dts will not be done
this is the code for the sp

CREATE PROCEDURE blablabla AS

declare @recs int

select @recs = (select count( *) from blablabla where IsUpdate = 1)

if @recs > 0
begin
select * from bla bla bla where IsUpdate = 1
end
else
begin
return(0)
end
GO
this sp i have put in the beginning of the dts design
I thought the return 0 would generate the error and thus prevent the other steps in the dts to be run
but it does not give an error in the dts

when i use the qry analyzer it shows me it doe give me the 0 when no data is selected
the dts however does not consider the code to have failed and proceeds with the next step in the dts
and that is just what i want to prevent

any help would be very much apreciated

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-26 : 12:37:18

U can do Something like the following

Declare @cnt int
Set @cnt = (Select Count(*) from UrDataTbl)
if @cnt = 0
Insert into TestTbl (PrimaryKeyNotAcceptingNulls) values(null)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-26 : 12:54:29

Error? Coming right up!


select convert(int,'My Error message')

server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'My Error message' to a column of data type int.

CODO ERGO SUM

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-26 : 13:59:49

None of my code ever generates error messages, so I'm afraid I can't help with this. ;)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 06:25:45

select error from Programming