Transform task moans about invalid pointer.

[stiej]

Hello,

I have a server conenction, an excel file to put data into, and a transform task between.

The sql code used as the source is simply a select statement that does a select * from my table.

To overcome the fact that maybe sometimes the table is empty therefore no recordset gets returned, i put a @@rowcount check in first then using an IF statement I either output a hardcoded "select '' as A, '' as B, '' as C" just so that I do have some sort of recordset to supply the transform with. Otherwise the recordset itself is output using data from the table. So one way or another, I output a recordset each time, only that sometimes it may be the aforementioned single, hard-coded, row.

Saying this, I still get an error moaning about an invlaid pointer, should the select statement before the @@ROWCOUNT function call return no rows (in the case of the table being empty).

My Source SQL statement is something like this: 1) exec a sp to populate some table. 2) select * on that table. 3) using an IF @@ROWCOUNT = 0 then select '' as A, '' as B etc or select * from the table again to obtain the proper recordset.

And that's it. So why the error if the table is empty? Why does it (seemingly) not therefore do the hard-coded select and output the single row?

Many thanks in advance for any help...

 

[SQLDenis]

do this instead
exec a sp
if exists (select * from table)
select * from table
else
select '' as A, '' as B


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[stiej]

thanks for the quick response. it works now, tho only if i pop the exec statement into it's own execute sql task prior to the transform bit. Otherwise i still end up with the same error. nevermind works this way round so im happy! Thanks again.