Stored Procedure for Data Driven Subscription?

Question

Stored Procedure for Data Driven Subscription?

sgmunson

SSC Guru

Points: 110639
More actions
September 9, 2008 at 6:47 am

#197919

I've found that the T-SQL I want to use for a data driven subscription isn't considered valid by the RS web interface, and so I put it into a stored procedure, and provided an EXEC statement for that sp in that web interfaces query window instead, and it still fails to validate. Is that because an sp doesn't support IDbCommand or IDataReader, which are requirements for an RS dds query?
Or am I doing something wrong? HELP !!!
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

Solved my own problem. I re-engineered the query to eliminate the need for a DELETE statement by taking that criteria into the INSERT INTO SELECT query with a NOT in front of it, and eliminated an IF block. That allowed me to avoid the sproc entirely, and it's now working. However, I sure still would like to know why there was a problem. Feel free to respond if you have any ideas...

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

benlatham SSC Eights! Points: 933 More actions · Answer 2

Hi

If Report Manager (or SSMS) can't retrieve the meta data from query or sp then the validation will fail and not allow you to progress. I had this problem the other day when I had a temp table in my stored procedure. I just rewrote the proc to remove the temp table.

I understand it is only an issue when using Report Manager or SSMS since it needs to run the validation. You could set up the subscription in code using the RS web service.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

Interesting. I didn't have a temp table, but I do have a table variable in my query, and once I eliminated a DELETE FROM statement by moving that statements WHERE criteria to an opposite meaning criteria into the previous INSERT INTO, and eliminated an IF block, it was able to validate the query. RS just seems to be pretty darn picky about what it can retrieve metadata for and what it can't. I choose to consider it a bug, until someone can convince me there's some good reason to look at it another way.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

rush1956 SSC Veteran Points: 232 More actions · Answer 4

hi,

When I am calling an SP as follows

exec admin.sp_retrieve_failfreq_test

in query window of data driven subscription.I get following error

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors

But SP works fine when executed from SQL Plus Prompt.

Thanks

rush1956 SSC Veteran Points: 232 More actions · Answer 5

Any help on calling SP in data driven subscription .............

sgmunson SSC Guru Points: 110639 More actions · Answer 6

RS is VERY PICKY about what it can handle and what it can't. My limited experience suggest that if the SP contains a DELETE anywhere within, that there could be a problem with it. The key may be as I had previously posted with respect to the two requirements for being able to get metadata returned. Some sp's may not be able to support RS's requirements.

Wish I knew more, but you can be quite sure that it's likely you'll have trouble with any kind of dynamic SQL.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

philip_egan SSC Journeyman Points: 97 More actions · Answer 7

I know this is an old thread but it's still up at the top in Google which is how I found it so thought i'd post my outcome.

I had the same problem as above and it turned out that the user credentials used by the data connection in ReportManager didn't have Execute permissions on the Stored Procedure.

SQLVoila Mr or Mrs. 500 Points: 564 More actions · Answer 8

philip_egan - Thursday, April 3, 2014 2:34 AM
I know this is an old thread but it's still up at the top in Google which is how I found it so thought i'd post my outcome.I had the same problem as above and it turned out that the user credentials used by the data connection in ReportManager didn't have Execute permissions on the Stored Procedure.

got here from google 10 years later 🙂 it still happening with SSRS 2016. In my case, I had OPTION(RECOMPILE) in my sp and that did not pass the RS validation. I have insert into #temptable and exec other sp inside this proc etc. those seem fine.