RS.EXE parameters not working

Hi Everyone,

I am having a problem with the RS utility not recognizing my query parameters that I am passing to my timed subscription. I have set up my report with a "WorkOrder" parameter. I have created a trigger that causes my subscription to fire, and that trigger passes in the workorder number to the utility. The subscription fires successfully, but I am getting a blank report.

I set the parameter value to "can be blank" so that I could create my subscription w/o an error. I know that my subscription id is correct when I use the fire method, because I DO get the appropriate report.

I am also having the same problem when I actually log in to the report server and execute the utility from the command line. I believe my report is good. Once the report is generated, I am able to follow my URL, plug in the workorder id, and it works.

What would cause the variable not to be passed in/recognized? This is how I am calling it from the command line:

rs -i C:\ReportingServices\asbuilts.rss -v WorkOrder="10610" -s http://(servername)/ReportServer

Thanks!

Wanda

Wanda,

Here's an example of my command line:

rs -i BOPReportPublisher.rss -s http://myserver/reportserver -v reportFolder=Reports

I place my server first and pass parameter in without quotes.

I hope this helps.

Ham

|||

Thanks, Ham, for responding, but that did not work. I also tried it WITH the quotes in the order you specified, but no luck.

I'm really banging my head on this one...

Any more suggestions?

Wanda

|||Can you post your script code so we can take a look?|||

Wanda,

Also note, that the parameter variables are case sensitive, I just thought I would say that.

Ham

|||

Here is the trigger:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Wanda Wilburn

-- Create date: 12/15/2006

-- Description: Automatically generate SQL Reports based on completion dates entered

-- =============================================

ALTER TRIGGER [azteca].[tPostCompletionDates] ON [azteca].[WOTASK]

FOR UPDATE

AS

DECLARE @.command VARCHAR(100)

DECLARE @.task VARCHAR(20)

DECLARE @.wo VARCHAR(20)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--IF @.@.ROWCOUNT = 0 RETURN

IF UPDATE (actfinishdate)

Begin

SET @.task = (SELECT taskname FROM INSERTED)

SET @.wo = (SELECT workorderid FROM INSERTED)

IF @.task = 'AsBuilts Rec/Approve'

SET @.command = 'rs.exe -i C:\ReportingServices\asbuilts.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

--SET @.command = 'rs.exe -i C:\ReportingServices\asbuilts.rss -v Workorder=10610 -s http://myserver/ReportServer'

IF @.task = 'ManholeCoat Timeline'

SET @.command = 'rs.exe -i C:\ReportingServices\coatsys.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = 'CostConstrucTimeline'

SET @.command = 'rs.exe -i C:\ReportingServices\costcon.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = 'FireProtection Avail'

SET @.command = 'rs.exe -i C:\ReportingServices\firepro.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = '1-Yr Inspect Process'

SET @.command = 'rs.exe -i C:\ReportingServices\release.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = 'Sewer Complete'

SET @.command = 'rs.exe -i C:\ReportingServices\sewerap.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = 'VideoInspectTimeline'

SET @.command = 'rs.exe -i C:\ReportingServices\videoin.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

End

IF UPDATE(actstartdate)

Begin

SET @.task = (SELECT taskname FROM INSERTED)

SET @.wo = (SELECT workorderid FROM INSERTED)

IF @.task = 'Acceptance Letter'

SET @.command = 'rs.exe -i C:\ReportingServices\yr1war.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = 'Slug/Disinfect Line'

SET @.command = 'rs.exe -i C:\ReportingServices\slugln.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = '60 Days Confirmation'

SET @.command = 'rs.exe -i C:\ReportingServices\begin60.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = 'PreCon Meeting'

SET @.command = 'rs.exe -i C:\ReportingServices\precon.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

IF @.task = '1-Yr Correspondence'

SET @.command = 'rs.exe -i C:\ReportingServices\inspect.rss -v WorkOrder="' + @.wo + '" -s http://myserver/ReportServer'

End

EXEC [myserver].[master].[dbo].[xp_cmdshell] @.command

-- Insert statements for trigger here

END

(Note...once again...I have tried this from the command line of the actual report server/changing order of parameters & variables/single-double-no quotes)

The following is the code for the asbuilts.rss file...

Sub Main()

rs.Fireevent("TimedSubscription",baf15621-f564-4774-8e4e-76bd94af1f33")

End Sub

Yes, I am aware of the case-sensitivity, and I have double-triple checked this.

Thanks for all of your help...

Wanda

|||

Wanda,

I do not see the reference for the "workorder" variable in your example of "asbuilts.rss". Is this the complete code?

Ham.

|||

This is it. The subscription for the report contains the variable field. The command line that gets generated from the trigger should fire the subscription with the workorder variable. The rss file is only telling which subscription to fire. Am I missing something that should be included in the rss file (i.e. parameter)?

EXEC [reportservername].[master].[dbo].[xp_cmdshell] @.command

|||

Wanda,

I believe what you need is to setSubscriptionProperties

public void SetSubscriptionProperties (
string SubscriptionID,
ExtensionSettings ExtensionSettings,
string Description,
string EventType,
string MatchData,
ParameterValue[] Parameters
)

You can pass your WorkOrder value in the Parameter value and then use rs.fireevent to launch your subscription.

Ham

|||

Would this code be included in the RSS code, the trigger, or the report?

Wanda

|||

Wanda,

This will be in the RSS code. I have given you a link that should tell you what you need to move forward.

http://msdn2.microsoft.com/en-us/library/aa225857(SQL.80).aspx

Ham

|||Hammer2 is correct. When you run the subscription it is using the parameter values that have been stored previously. If you want to use new parameter values you would have to call SetSubscriptionProperties first. Of course this does not guarantee that somebody else will set the parameter values after you do. There is now way to say run a subscription with these parameters.|||

Ok, I will try this, and I will let you know the results. I guess I assumed that this was being done automatically by the rs.exe and that the code existed to do this if a -v switch/data was passed in.

Thanks. Wanda

|||

Can you give me an example of the call to this method?

I'm assuming you are saying that the "v" parameter that is used in the rs.exe passes the parameter to the .rss file, which, in turn, must call the SetSubscriptionProperties method using that data, correct?

I've tried to find examples but I can't find any.

Thanks! Wanda

|||

Wanda,

I do not have an example on hand, I can get you a reference to a very similar process and you can modified it to your needs. Let me know if this works for you.

http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.listsubscriptions.aspx

Ham