Broken Subscriptions Due to Empty Table

Hi,

I was wondering what the best way was to deal with subscriptions breaking due to an empty table. I have subscriptions that people have scheduled to go out daily, but on certain days the table may be empty, in this case the subscription doesn't read the parameters for the report and then the subscription breaks.

My original solution involved creating a #temp table with the same columns as the original table and inserting one row into it which I'd union with the original table, this row in the temp table had all its values set to 0. The solution worked when I ran it in SQL Server Management Studio but it seems SRS doesn't like the INSERT INTO statement, which is the error I get, but I've read on these forums that it doesn't like #temp tables either. I proceeded to use a stored procedure with all the code in it, but I might have trouble filtering on multi value parameters, because at times these parameter lists get real big, plus I have to do this for multiple reports and don't want to get into creating stored procedures for each report.

Following is what the code I used look something like that executes and does the job in Management Studio but not SRS. I'm mainly just looking for the easiest and cleanest way to do this, since it'll have to be done across multiple reports, so disregard the code if there's an easier way to do it. Thanks in advance.

create table #dummytable

(
name varchar(35),

country varchar(35),

idnumber (int)

)

GO

insert into #dummytable (name),values('0');

select name, country, idnumber

from originaltable

where name in (@.name)

union

select name = 0, country = 0, idnumber = 0

from #dummytable

drop #dummytable

Another solution might be to do a union with a single known row that will always be delivered.

If the delivery query returns 0 rows, what would you want the subscription to do?

Thanks,

-Lukasz

|||

>> SRS doesn't like the INSERT

Instead of creating a temp table and INSERTing, why not just do a SELECT of literals?

Code Snippet

SELECT Name, Country, idnumber FROM

OriginalTable WHERE Name IN (@.Name)

UNION

SELECT SPACE(0),SPACE(0), 0

>L<

|||

Thank you Lisa, that does get the job done, much appreciated.