Hi,
I have a report with 25 subreports which needs to generate one pdf file on
schedule basis (ie. schedule report to render in to PDF format based on some
time and date setting).
Is there any way of doing this?
Thanks in advance for the helper.
Regards.
AnithaYou need to subscribe to the report. As part of the subscription parameters
you can define the schedule of when you want the report to be automatically
generated.
Keep in mind that you have to also define a dafault value for each parameter
in your report.
Hope this helps,
Tigan
"Anitha Naidu" wrote:
> Hi,
> I have a report with 25 subreports which needs to generate one pdf file on
> schedule basis (ie. schedule report to render in to PDF format based on some
> time and date setting).
> Is there any way of doing this?
> Thanks in advance for the helper.
> Regards.
> Anitha
>
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Scheduling a summary Stored Procedure
I had stored procedure[this read the data from couple of table and summarize
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhu
If the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>
|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own[vbcol=seagreen]
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> summarize
> half
start
>
|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my[vbcol=seagreen]
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> the
proc[vbcol=seagreen]
> own
to[vbcol=seagreen]
> start
to
>
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhu
If the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>
|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own[vbcol=seagreen]
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> summarize
> half
start
>
|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my[vbcol=seagreen]
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> the
proc[vbcol=seagreen]
> own
to[vbcol=seagreen]
> start
to
>
Scheduling a summary Stored Procedure
I had stored procedure[this read the data from couple of table and summarize
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhuIf the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > I had stored procedure[this read the data from couple of table and
> summarize
> > them based on the input paramter] which needs to be executed for every
> half
> > hour.I need to pass some input parameter to that proc. I am planning to
> > schedule this proc using below system procs
> > sp_add_jobschedule
> > sp_add_job
> > I need to pass some input parameter to the stored proc. I need give
start
> > interval and end interval and some configurable values[int 1...int 5) to
> > filter out columns. These values can be modifiable by the end user
> > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > int5...)
> > How do I pass this input params to my stored proc, when I shceduled .?
> > Is it the right approch to do this kind of summary procedure ?
> > Thanks in advance for your thoughts and comments appreciate it
> >
> > prabhu
> >
> >
> >
> >
>|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> > If the procedure you need to run have changing parameters' values, what
> > application supplies the parameters' values? That application should be
> the
> > job scheduled (within SQL server or outside).
> >
> > If you have a few sets of such values that are fed to the same stored
proc
> > to be run at different time, you can create separate jobs, each has its
> own
> > schedule and parameter values.
> >
> >
> >
> > "prabhu" <> wrote in message
> > news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > > I had stored procedure[this read the data from couple of table and
> > summarize
> > > them based on the input paramter] which needs to be executed for every
> > half
> > > hour.I need to pass some input parameter to that proc. I am planning
to
> > > schedule this proc using below system procs
> > > sp_add_jobschedule
> > > sp_add_job
> > > I need to pass some input parameter to the stored proc. I need give
> start
> > > interval and end interval and some configurable values[int 1...int 5)
to
> > > filter out columns. These values can be modifiable by the end user
> > > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > > int5...)
> > > How do I pass this input params to my stored proc, when I shceduled .?
> > > Is it the right approch to do this kind of summary procedure ?
> > > Thanks in advance for your thoughts and comments appreciate it
> > >
> > > prabhu
> > >
> > >
> > >
> > >
> >
> >
>
them based on the input paramter] which needs to be executed for every half
hour.I need to pass some input parameter to that proc. I am planning to
schedule this proc using below system procs
sp_add_jobschedule
sp_add_job
I need to pass some input parameter to the stored proc. I need give start
interval and end interval and some configurable values[int 1...int 5) to
filter out columns. These values can be modifiable by the end user
i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
int5...)
How do I pass this input params to my stored proc, when I shceduled .?
Is it the right approch to do this kind of summary procedure ?
Thanks in advance for your thoughts and comments appreciate it
prabhuIf the procedure you need to run have changing parameters' values, what
application supplies the parameters' values? That application should be the
job scheduled (within SQL server or outside).
If you have a few sets of such values that are fed to the same stored proc
to be run at different time, you can create separate jobs, each has its own
schedule and parameter values.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I had stored procedure[this read the data from couple of table and
summarize
> them based on the input paramter] which needs to be executed for every
half
> hour.I need to pass some input parameter to that proc. I am planning to
> schedule this proc using below system procs
> sp_add_jobschedule
> sp_add_job
> I need to pass some input parameter to the stored proc. I need give start
> interval and end interval and some configurable values[int 1...int 5) to
> filter out columns. These values can be modifiable by the end user
> i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> int5...)
> How do I pass this input params to my stored proc, when I shceduled .?
> Is it the right approch to do this kind of summary procedure ?
> Thanks in advance for your thoughts and comments appreciate it
> prabhu
>
>|||Thank for the response Ran.
Most of the time parameters are going to have same value. But I cannot hard
code these values. Instead I need to read it from the file[ or Is there
place I can keep the values for the stored proc parameter.?] and invoke my
summary proc.
So the approach should be, the SP which is feeding the value should be
scheduled which intern call the summary procedure. Let me know if you have
any thoughts...
"Quentin Ran" <xyz@.abc.com> wrote in message
news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> If the procedure you need to run have changing parameters' values, what
> application supplies the parameters' values? That application should be
the
> job scheduled (within SQL server or outside).
> If you have a few sets of such values that are fed to the same stored proc
> to be run at different time, you can create separate jobs, each has its
own
> schedule and parameter values.
>
> "prabhu" <> wrote in message
> news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > I had stored procedure[this read the data from couple of table and
> summarize
> > them based on the input paramter] which needs to be executed for every
> half
> > hour.I need to pass some input parameter to that proc. I am planning to
> > schedule this proc using below system procs
> > sp_add_jobschedule
> > sp_add_job
> > I need to pass some input parameter to the stored proc. I need give
start
> > interval and end interval and some configurable values[int 1...int 5) to
> > filter out columns. These values can be modifiable by the end user
> > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > int5...)
> > How do I pass this input params to my stored proc, when I shceduled .?
> > Is it the right approch to do this kind of summary procedure ?
> > Thanks in advance for your thoughts and comments appreciate it
> >
> > prabhu
> >
> >
> >
> >
>|||Or rather, you want to set up a table which holds the parameter values.
Your job will query the table to construct the code.
"prabhu" <prabahar.ignatius@.inin.com> wrote in message
news:OQk2W3OnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Thank for the response Ran.
> Most of the time parameters are going to have same value. But I cannot
hard
> code these values. Instead I need to read it from the file[ or Is there
> place I can keep the values for the stored proc parameter.?] and invoke
my
> summary proc.
> So the approach should be, the SP which is feeding the value should be
> scheduled which intern call the summary procedure. Let me know if you have
> any thoughts...
>
> "Quentin Ran" <xyz@.abc.com> wrote in message
> news:ezt3LrOnEHA.536@.TK2MSFTNGP11.phx.gbl...
> > If the procedure you need to run have changing parameters' values, what
> > application supplies the parameters' values? That application should be
> the
> > job scheduled (within SQL server or outside).
> >
> > If you have a few sets of such values that are fed to the same stored
proc
> > to be run at different time, you can create separate jobs, each has its
> own
> > schedule and parameter values.
> >
> >
> >
> > "prabhu" <> wrote in message
> > news:uvHi57LnEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > > I had stored procedure[this read the data from couple of table and
> > summarize
> > > them based on the input paramter] which needs to be executed for every
> > half
> > > hour.I need to pass some input parameter to that proc. I am planning
to
> > > schedule this proc using below system procs
> > > sp_add_jobschedule
> > > sp_add_job
> > > I need to pass some input parameter to the stored proc. I need give
> start
> > > interval and end interval and some configurable values[int 1...int 5)
to
> > > filter out columns. These values can be modifiable by the end user
> > > i.e exec my _storedpoc(startinterval, endinterval, int1, int2, int3,
> > > int5...)
> > > How do I pass this input params to my stored proc, when I shceduled .?
> > > Is it the right approch to do this kind of summary procedure ?
> > > Thanks in advance for your thoughts and comments appreciate it
> > >
> > > prabhu
> > >
> > >
> > >
> > >
> >
> >
>
Scheduling a Report based on an event
Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If it's possible to detect that your stored proc is finished within SQL
(either because it sets a flag in a table or be the mire presence of data it
creates or something) there might be a way. Might not be the optimal
solution, but your SQL dataset could reference that flag so that no data is
returned at all to the report if the flag doesn't exist. You'd have to set
it up so that the data table the report references doesn't exist at all until
the stored proc is finished. Then schedule your report job to run multiple
times near the time when you expect the SQL proc to finish. Without the data
table existing, your Rpt Services job should just error out and product
nothing. Again, not an elegant solution, but if you're desperate might be
something to toy around with ... a place to start.
sebring1130
"SqlJunkies User" wrote:
> Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>|||Sure this is real easy to do.
Create a schedule that has completed in the past - so effectively it will
never fire. Associate this schedule with a Report.
Now what happens is that a SQL Agent Job is created - that maps to the
schedule. You can run SQL Agent Jobs from the SQL Agent Management interface
by hand - or you can cause that job to run through T-SQL.
All that the SQL Agent Job does is create an entry in the Report Server's
Event table at the scheduled time. The Report Server Windows Service is
polling the Event table every 10 seconds or so - and if there are any events
to process it gets on and processes them.
So what you do is either include in your long running stored procedure a
call that will create the required entry in the Event table directly - or a
call that fires the SQL Agent Job.
- One word of warning though if you start editing the schedule in the Report
Manager, then the Report Manager can end up re-creating the SQL Agent Jobs -
and you lose reference to the actual Job.
However if you are disciplined enough then this approach works fine -
(Schedule in the past, have your own process force the SQL Agent Job to run)
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...
> Is it possible to schedule a report based on a flag or stored procedure
> completing. Currently have an overnight load process which must complete
> before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If it's possible to detect that your stored proc is finished within SQL
(either because it sets a flag in a table or be the mire presence of data it
creates or something) there might be a way. Might not be the optimal
solution, but your SQL dataset could reference that flag so that no data is
returned at all to the report if the flag doesn't exist. You'd have to set
it up so that the data table the report references doesn't exist at all until
the stored proc is finished. Then schedule your report job to run multiple
times near the time when you expect the SQL proc to finish. Without the data
table existing, your Rpt Services job should just error out and product
nothing. Again, not an elegant solution, but if you're desperate might be
something to toy around with ... a place to start.
sebring1130
"SqlJunkies User" wrote:
> Is it possible to schedule a report based on a flag or stored procedure completing. Currently have an overnight load process which must complete before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>|||Sure this is real easy to do.
Create a schedule that has completed in the past - so effectively it will
never fire. Associate this schedule with a Report.
Now what happens is that a SQL Agent Job is created - that maps to the
schedule. You can run SQL Agent Jobs from the SQL Agent Management interface
by hand - or you can cause that job to run through T-SQL.
All that the SQL Agent Job does is create an entry in the Report Server's
Event table at the scheduled time. The Report Server Windows Service is
polling the Event table every 10 seconds or so - and if there are any events
to process it gets on and processes them.
So what you do is either include in your long running stored procedure a
call that will create the required entry in the Event table directly - or a
call that fires the SQL Agent Job.
- One word of warning though if you start editing the schedule in the Report
Manager, then the Report Manager can end up re-creating the SQL Agent Jobs -
and you lose reference to the actual Job.
However if you are disciplined enough then this approach works fine -
(Schedule in the past, have your own process force the SQL Agent Job to run)
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...
> Is it possible to schedule a report based on a flag or stored procedure
> completing. Currently have an overnight load process which must complete
> before the report starts. Any suggestions would be most appreciated
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
Friday, March 9, 2012
Schedule Stored Procedure: Changing one Field based on another
Hi all,
I want to do something that I can't get my head around. Here is the info..
I have a table, Tips. This is a table holding playing tips for a department of music web site. The tips are broken down into areas (brass, woodwind, etc...). Each area has its own .aspx page which has a user control that will display a "tip of the week." That tip will be taken from the table. Columns are:
ID, int, pk
AreaID, int
CategoryID, int
Active, bit
Selected, bit
Author, nvarchar
TipText, text
For what I want to do the only fields that are in play are the ID, AreaID and Selected. The table will have multiple rows for each AreaID, and at any one given time only one of the multiple rows with the same AreaID will be selected (Selected = 1). The others will be 0.
I'm trying to figure out how to write a stored procedure that, scheduled to run weekly, will select one random tip for each disctinct area id (setting selected = 1) that is not currently selected and then deselecting the previously selected items (setting selected = 0).
Doing this for a single record in the table would be easy. The tricky part for me is doing this for each of the distinct AreaIDs.
Ideas?
RichardWow. No replies.
Did I not ask the question right? I'm still trying to figure this out. If it's a stupid question I'd appreciate a heads up about where I should look to find this info.
Thanks!
Richard|||Have you had any luck? This is actually trickier than it sounds and I am not coming up with anything clever. I think you are going to have to resort to using a cursor -- the cursor will contain a unique set of AreaIDs, and you will loop through it.
What I was able to come up with was this (note I didn't put the cursor creation and looping code in there because I don't know the syntax off of the top of my head -- BOL is our friend):
-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
tips
SET
Selected = 2
WHERE
Selected = 1-- create a cursor consisting of the unique AreaIDs
-- then loop through cursor, updating Selected for each AreaID
UPDATE
tips
SET
Selected = 1
FROM
tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.cursor_AreaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
Selected <> 2 AND
AreaID = @.cursor_AreaID-- when done looping, make the originally selected IDs unselected
UPDATE
tips
SET
Selected = 0
WHERE
Selected = 2
I am hopeful this could be done without a cursor but it's not coming to me...
Terri|||Terri,
Clever! I didn't think about the setting the 0's to 2 part. I don't know what a cursor is, but I'll poke around and figure it out - give me a day or so. :) The rest make perfect sense, and should help me get it done. Thanks for your help!
Richard|||Since I now know you are still working on this, I still stand by my suggestion, but I can give you code to use instead of using a cursor (which is resource intensive). Same approach, but you would put all of your AreaIDs into a #temp table instead and loop though that:
DECLARE @.areaID int-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
tips
SET
Selected = 2
WHERE
Selected = 1-- create a temp table consisting of the unique AreaIDs
CREATE TABLE #Temp (areaID int)
INSERT INTO #Temp SELECT DISTINCT areaID FROM tips-- get first areaID to process
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID-- loop
WHILE @.areaID IS NOT NULL
BEGIN-- update Selected for each AreaID
UPDATE
tips
SET
Selected = 1
FROM
tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.areaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
Selected <> 2 AND
AreaID = @.AreaID-- get next AreaID to process
DELETE FROM #temp WHERE areaID = @.areaID
SELECT @.areaID = NULL
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID
END-- when done looping, make the originally selected IDs unselected
UPDATE
tips
SET
Selected = 0
WHERE
Selected = 2-- drop temporary table
DROP TABLE #TEMP
Still wishing this could be done in one statement...
Terri|||Terri,
Worked perfectly! All I had to do is change the column type from bit to int (couldn't update all the 1's to 2 in a bit field). Thank you!!
RH|||That's good news; thanks for updating us! (And yet another reason why I don't like bit fields ;-)) You should be able to use a tinyint here and save some storage space.
Terro
Subscribe to:
Posts (Atom)