select count(*) for success in DTS

[influent]

I have a DTS package that copies a table from one server to several other databases on other servers. I want to add an "Execute SQL Task" to it that does this:

SELECT COUNT(*)
FROM table
AS @count
IF (@count) > 0
<return success so that the package continues>

The idea is that if the table is empty, it won't be copied. How do I do this so that the ON SUCCESS workflow works?

 

[wilk]

a idea could be to do the count as a if statement and if it is 0 then raise error then you could use the on failure method in the dts to capture this.

No idea if this will work but its a idea.

 

[influent]

I figured out one way, but I think there is probably a better way. What I did is this:

DECLARE @count int
Set @count = 1 / (select count(*) from tblItem)

An ON FAILURE occurs if the count equals zero because @count becomes undefined.

 

[mbaza3]

If you are in SQL 2000 the divide by zero error will work. However if you are in SQL 7 it will not. If the error is not generated in the first line of the execute sql task code, the task will not fail not matter what happens in the execute sql task.

Ex.

execute sql task:

select @@version
raiserror('failed',16,1)

This will work in SQL 2000, but not in SQL 7

 

[influent]

Wow, it's a good thing you told me that since I tested it in 2000 but implented it in 7.

thanks

 

[mbaza3]

I would test it in Sql 7.0 as well. The interesting thing is when I contactd Microsoft about this they could not duplicate the error. Maybe it will not fail in your environment. I would be curious to know.

Thanks.
Joe

 

[influent]

I just tested it on my SQL Server 7 server, and it worked. Maybe your server was in 6.5 compatability mode or something.