Running a job from a Report using a button? (Is that possible)

Here is the deal. We have a mapping table that stores basic info (like
4 columns). These columns are used to populate parameter boxes in a
report (some parameters depend on the values of others and so we had
to use a mapping table for performance increase). The mapping table
is loaded hourly using a job that calls an SSIS package. Simple
stuff. However, let's assume the user logs in at 12:30 and there is
new data that is not showing, they cannot wait 30 minutes until the
job runs again. At the same time, we can't make the job run that
frequently. So we want to set up a "button" that the users can click
on and it will allow the job to run. We cannot have the job run every
time the report is loaded, takes too much time and would defeat the
purpose of performance enhancement.
So far I have this script in a dataset that points to the msdb
database on our reporting box:
exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
ID!!! I've pressed the ... button near the dataset to try to hard
code the parameter there, no luck.
Also, what would I use to add the user functionality, namely them
being able to click on something to invoke the job?
Thank you so much.I would like add buton to rerun an SSIS package. How did you add a button in
RS?
"Ayman" wrote:
> Here is the deal. We have a mapping table that stores basic info (like
> 4 columns). These columns are used to populate parameter boxes in a
> report (some parameters depend on the values of others and so we had
> to use a mapping table for performance increase). The mapping table
> is loaded hourly using a job that calls an SSIS package. Simple
> stuff. However, let's assume the user logs in at 12:30 and there is
> new data that is not showing, they cannot wait 30 minutes until the
> job runs again. At the same time, we can't make the job run that
> frequently. So we want to set up a "button" that the users can click
> on and it will allow the job to run. We cannot have the job run every
> time the report is loaded, takes too much time and would defeat the
> purpose of performance enhancement.
> So far I have this script in a dataset that points to the msdb
> database on our reporting box:
> exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
> I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
> ID!!! I've pressed the ... button near the dataset to try to hard
> code the parameter there, no luck.
> Also, what would I use to add the user functionality, namely them
> being able to click on something to invoke the job?
> Thank you so much.
>|||Hi,
I think the problem is the "@.". Try to use a sproc instead of a text
command.
"Ayman" <aymantg@.gmail.com> schrieb im Newsbeitrag
news:1184855299.083046.294670@.z24g2000prh.googlegroups.com...
> Here is the deal. We have a mapping table that stores basic info (like
> 4 columns). These columns are used to populate parameter boxes in a
> report (some parameters depend on the values of others and so we had
> to use a mapping table for performance increase). The mapping table
> is loaded hourly using a job that calls an SSIS package. Simple
> stuff. However, let's assume the user logs in at 12:30 and there is
> new data that is not showing, they cannot wait 30 minutes until the
> job runs again. At the same time, we can't make the job run that
> frequently. So we want to set up a "button" that the users can click
> on and it will allow the job to run. We cannot have the job run every
> time the report is loaded, takes too much time and would defeat the
> purpose of performance enhancement.
> So far I have this script in a dataset that points to the msdb
> database on our reporting box:
> exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
> I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
> ID!!! I've pressed the ... button near the dataset to try to hard
> code the parameter there, no luck.
> Also, what would I use to add the user functionality, namely them
> being able to click on something to invoke the job?
> Thank you so much.
>