Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CR XI report only passes NULL value to stored procedure

Status
Not open for further replies.

leveetated

Programmer
Jan 24, 2008
46
US
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.
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top