CR XI report only passes NULL value to stored procedure

[leveetated]

Hello CR experts,

CRXI
MS SQL Server 2005

I've been going round and round on a problem over the last week and am at my wit's end! It must be a simple setting I've overlooked.

I simply want to pass a number to a SP, and use that value in a WHERE statement, followed by an INSERT. I have a main report that's passing the value to the subreport, which is using the SP as its datasource.

I know the sub is getting the correct value, but when I run the report the sub only passes NULL to the SP. (I'm using ?parameter to the sub, not the ?Pm-@parameter.) The report hits the SP, then runs the INSERT and passes NULL into the int datatype field that should store the report param's value.

I've already built a similar report so I know it can be done. I've tried switching drivers (tried OLE DB (AD0), SQL Native, ODBC RDO) and I still get NULL.

Anything else I should check, CR wise ones? Any suggestions much appreciated.

Best,

Lee.

 

[leveetated]

I forgot to add that I can execute the SP in MS SQL Server, pass in a value for the parameter, and it runs perfectly.

Many thanks,

Lee.

 

[leveetated]

If anyone else encounters this problem, here's what I had to do to make it work:

- I had the SP as the datasource, thinking that the @param in the report would 'automagically' be passed to its matching counterpart in the SP. I was wrong (even though this method works for another report...that's another story).

- Instead of using the SP as datasource, I wrote a command that executed the SP and passed in the param (e.g. EXEC DBNAME.dbo.my_SP {?param}).

That seemed to be the fix. Hope this helps someone.

Best,

Lee.