CODEWITHSUNDEEP
sql-serverreporting-services
Eji1700
Eji1700

Reputation: 43

Modify report subscription parameters directly through SQL

I'm attempting to update report subscription with a SQL task started in SSIS (which would have various parameters passed to it through an earlier script).

I've found some documentation on this, and can even get the parameters to change in the record, but when i actually run the output job it says it worked, but nothing outputs, even if i replace with identical values.

I suspect the issue is a formatting error for a rather random reason. I've been pasting the xml record (which report server stores as ntext) into an XML VS code tab. Before I edit the record it'll output on one line and i'll be forced to format it, after I make the update it autoformats when i paste into the same tab. If i blow out the update (by just resubscribing the report through the UI) it's back to a one line copy paste

I've jumped through a few hoops on this one. While most of the documentation i have found on it seems to imply I can just update the field normally(Set blah = 'thing'), if I do that it just breaks (literally says the XML format isn't correct if i pull up the subscription in the UI).

As the field is ntext i've started casting the entire thing as ntext. This keeps the UI working, but results in the issue described. Job runs, says its complete, nothing outputs, now autoformats when pasting to vscode, and I can go into the UI and just hit "apply" and renter my password for it to actually set the values correctly (it'll work after I do that, and it'll have the values I passed pre filled in)

I threw in a COLLATE segment just to test, but that hasn't seemed to change anything.

Here's what i'm currently running

UPDATE [ReportServer].[dbo].[Subscriptions]
SET Parameters =
CAST('<ParameterValues>
    <ParameterValue>
        <Name>Region</Name>
        <Value>'+
        (Select Region from [ReportServer].[dbo].[ParamOvershorts])
        +'</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>GMName</Name>
        <Value>'+
        (Select GMName from [ReportServer].[dbo].[ParamOvershorts])
        +'</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>EndDate</Name>
        <Value>2/8/2019 12:00:00 AM</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>StartDate</Name>
        <Value>2/1/2019 12:00:00 AM</Value>
    </ParameterValue>
</ParameterValues>' COLLATE Latin1_General_100_CI_AS_KS_WS AS ntext)
where SubscriptionID = 'ID is here'

Expected output should be the report with the new parameters.

Actual output is it says job ran successfuly (so the event was added) but nothing outputs.

Edit-

Found a solution, see below

Upvotes: 1

Views: 1275

Answers (1)

Eji1700
Eji1700

Reputation: 43

Solved it. Code first-

UPDATE Subscriptions
SET Parameters =(
    SELECT REPLACE(CAST(Parameters as varchar(1000)),'GMPARAMETER', (select gmname from ParamOvershorts)) from Subscriptions
) WHERE SubscriptionID = ID GOES HERE

Whatever the issue is with the type/formatting, the replace function doesn't break it, so instead of having the actual Param xml blocks in the query, I first prep the subscription by replacing all the parameters with generic names (GMPARAMETER in this case), and then use the replace function to flip the parameter, execute the report, then flip the parameter back to the generic name so it can find it next time.

Not exactly ideal since i'm doing a bunch of replace actions rather than just entering exactly what I want each time, and i'll need to prep every report manually by going in and putting in the placeholder values first, but it is working.

I'd still love to know why my initial solution was failing though.

Upvotes: 1

Related Questions