A report seems to ignore parameters passed to it by an XML job
Hint Ref: 020806110005
Hint Date: 11/06/2008
Hint Details:
PROBLEM:
A customer reported that they were unable to pass a parameter from an XML to a report the job ran even though the syntax of the XML job was correct, the XML variable had data in it, and the report did not ask for the parameter to be entered manually by the user when it ran.
When the report ran, the parameter simply did not appear to contain any data. If the report was previewed in the report builder once a run time default had been set for the parameter, the report displayed correctly.
ISSUE:
The customer was using an XML job in order to write data to a temporary table before the report queried it. In order to keep the SQL in the job in order, they wanted to turn on the 'Preserve Space' option. This they did by right-clicking on the <Job> tag.
However, by doing so they inadvertently turned on the 'Preserve Space' option for ALL the commands in the XML job and thus the writing of the parameters to the report. In doing so, they stopped the parameters from being written to the report in a format it could understand.
SOLUTION:
The job was corrected by removing the 'Preserve Space' option from the <Job> tag and instead applying it directly to the <SQLExec> tag that held the SQL query.