Skip to Content
0
Former Member
Sep 15, 2011 at 08:24 AM

Delete table in SQL command

19 Views

Hello,

As part of my report I populate a table then reference it in several following subreports.

The issue I'm having is that I need to clear all the data from my table after using it to allow the report to run again (and not get multiple results)

To do this I have tried to add a sub report at the very end (and start) of my report with the SQL command truncate table <tablename> or delete <tablename> or delete <tablename> where 1 = 1.

When I try truncate I get an error saying that I don't have permission (despite granting it but hey...) which is why I switched to delete, this runs fine but the table doesn't get deleted...

Any thoughts as to why this isn't working/how I can clear my table? I have thought of setting up a separate scheduled job to delete the table contents however I do also need to run the report several times with different parameters so this isn't an ideal solution.

I have tried adding a select after my delete statement as I thought if it had something to display on the report it would have to look at it but this didn't make any difference.

Any help will be much appreciated!

Thanks

Chris