Derek, 
With the error provided above, I'm assuming that you're trying to pull data from an oracle data source (hence the oracle error #)... The Report Module should work against an oracle data source but I'm not sure which version of the module you're using.. 
I don't think you will be able to do the select * from storedprocedure like you've mentioned above (unless you put that in as an example).. In general, if you're calling a stored proc, you'll have to provide the parameters and make sure that one of the parameters in the sproc itself is a cursor type to return back the results...

SAnjay

You can do both - the trick with stored procedures is getting it to recognize the cursor you're pushing the output with (from the stored proc)... I know with the oracle version of the reports module, I made some tweaks to get it to recognize the output parameters (this is in the core oracle dataprovider engine itself) so that this would work as expected but not sure if some workaround was made on the sql server version that's out there.
Sanjay