Is this a bug? Is there a preventative measure I could take? I'm looking at a coupla tedious hours work to put 'em all back in to all my reports, and I don't want this to happen again.
I'ma go find the espresso machine.My specific case is this :
All of my reports have a db parameter that specifies the database name, plus one or more parameters that are passed into a stored procedure residing on that database. Therefore, an average parameter list & mappings would look like this :
And then the sp call looks something like this :
declare @.sp nvarchar(255)
set @.sp = @.db + '.dbo.fancy_financial_report_sp'
exec @.sp @.Ledger_Txn, @.Fiscal_Period
I had to modify my sp calls to handle the case where the database name has a period (.) in it, so it would look like this :
set @.sp = '[' + @.db + '].dbo.fancy_financial_report_sp'
Every report I made this change to, however, had all of the aforementioned Report Parameter-to-SP-parameter mappings toasted.
And then I proceeded to spend a coupla hours replacing all my parameters while listening to Bob Dylan's Infidels repeatedly. I suppose Blood On the Tracks would have been more appropriate.
|||The issue may be that Reporting Services discovers parameters, so when you're dynamically setting the stored procedure it cannot discover them. Try hard-coding the sproc to see if you still have the problem.I have seen this happen when there are print statements or multiple recordsets in the sproc too.
Are you performing the code inside the RS dataset or in the sproc? Try passing the database in as a parameter to a central sproc instead of doing it in the report, if this is the case.sql
