T-SQL Script: run

Showing posts with label run. Show all posts

Friday, March 30, 2012

scheduling the job to process a dts package

I am new to sqlserver.

Could anybody please help me by giving me a syntax for the command in job schedular to run dts package

i enter the job name in the first screen of the wizard.

I entered the step name. type Transact SQL

database msdb

What should i enter in command text box

My dts package name is fullcube.

Thanks in advance

The easiest way to schedule a DTS package in SQL 2000 is to navigate to the package in Enterprise Manager, right click on the package and choose the "Schedule Package..." option.

This will create a SQL Agent job to execute the package, by default I think it encrypts the command line.

The other way to execute a DTS package is to use the DTexec command line utility. To get a list of the parameters for this utility, type in "DTEXEC /?" to a command line window. If you wanted to you could manually do what the "Schedule Package..." screen does and create a SQL job that has a CmdExec step that calls DTExec.

Scheduling Subscription On Day 30 of a Month

I tried to setup a monthly subscription, to be run on Day 30 of a month.
After clicking the radio button for "Month", then click the radio button for
"On Calendar Days", then enter the day 30, and press OK. The subscription
will return the following error message:To create a schedule that runs on
multiple days, you must choose which days to use.
This message will not appear, if I enter any other day, up to and including
day 29.I have to deselect Month of Februrary, which does not have 30 days
"LBJOHN" wrote:
> I tried to setup a monthly subscription, to be run on Day 30 of a month.
> After clicking the radio button for "Month", then click the radio button for
> "On Calendar Days", then enter the day 30, and press OK. The subscription
> will return the following error message:To create a schedule that runs on
> multiple days, you must choose which days to use.
> This message will not appear, if I enter any other day, up to and including
> day 29.
>

scheduling stored procedure

how can i schedule a sp to run every 10 minutescreate an Agent Job with a single step that calls the sproc.|||thanks how do i do that in sql 2005

i'm using management studio|||Start by either reading Books Online or by right-clicking the Agent tab.

Scheduling SQL 2005 SSIS packages

I have created and run SSIS packages from my workstation that are successful. When I Build and then Deploy the same package to the server and schedule it, it fails. I am using SQL authentication connecting from a SQL 2000 database to a SQL 2005 database via OLE DB connections.

The following is the exported result of the package...

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by User JDEREP. The last step to run was step 1 (step1).,00:00:01,0,0,,,,0

05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:46:06.38 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:46:05 PM Finished: 3:46:06 PM Elapsed: 0.75 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:30:01.15 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:00 PM Finished: 3:30:01 PM Elapsed: 0.734 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:20:01.55 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:20:00 PM Finished: 3:20:01 PM Elapsed: 0.703 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:18:01.53 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:18:00 PM Finished: 3:18:01 PM Elapsed: 0.766 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0

Any help would be greatly appreciated...

Jim

Additionally, when I run this from the command line, I get the following

DTSER_FAILURE (1)

Jim

How are you setting the database connections in your package?|||

The package was created in Visual Studio 2005, the database connections were created within the package under Data Sources by using the Data Source Wizard. Then in the editor for the Execute SQL Task and the Data Flow task, the connection is selected.

The Execute SQL Task is a SQL statement to trucate the destination table.

Then on the Data Flow I select a source table and a destination table for the ETL - actually just copying the data from one table in one db to another table in a different db.

|||Take a look at the topic "Setting the Protection Level of Packages" in Books Online. Depending on the ProtectionLevel setting, the package may not be saving the password for the database, or it may require it to be run under your user account to decrypt the password.|||

Hello,

To run packages as sql agent jobs, I have deployed the package in the ssis server in sql server MSDB with the option 'Rely on server storage for encryption'

I have also added packages that use a package configuration file, the packages refer to the config file via an environment variable. the environment variable is set on the server. The package is also deployed in the ssis server in sql server MSDB with the option rely on server storage for encryption. I have not yet tested it a job that runs this 2nd type of packages , realy uses this package configuration file, or if it is using whatever happend to be in the connection string of the package when I deployed it.

|||does it work?|||

Yes,

the packages definitely use the config file.

Scheduling SQL 2005 SSIS packages

The following is the exported result of the package...

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

Any help would be greatly appreciated...

Jim

Additionally, when I run this from the command line, I get the following

DTSER_FAILURE (1)

Jim

How are you setting the database connections in your package?|||

The Execute SQL Task is a SQL statement to trucate the destination table.

Then on the Data Flow I select a source table and a destination table for the ETL - actually just copying the data from one table in one db to another table in a different db.

Hello,

To run packages as sql agent jobs, I have deployed the package in the ssis server in sql server MSDB with the option 'Rely on server storage for encryption'

|||does it work?|||

Yes,

the packages definitely use the config file.

Scheduling Running Of Proc's

Hello, I am trying to figure out the most appropriate means to schedule a
procedure to run at a specified interval during the day.
I see the SQL Server has "SQL Server Agent" which appears to allow you to
schedule jobs to be run at periodic times, but unfortuneately the smallest
interval that I can find is every hour and I was looking to schedule this to
run every few minutes. What other means is available to me to schdeule the
procedure to run every few minutes?
I did play around with the "SQL Server Agent" and the scheduling seems to work
just fine, but the email notification never occurs. The message that I
located for why the email notification did not occur is as follows "The job
succeeded. The Job was invoked by Schedule 1 (MyFirstSchedule). The last
step to run was step 1 (First Step). NOTE: Failed to notify 'JimHeavey' via
network popup. NOTE: Failed to notify 'JimHeavey' via email."
What do I not have set up correctly to cause the email notification not to
occur?
Thanks in advance for your assistance!!!!!!!!!!You can schedule a job to run every minute if you want. Does your SQL mail
work outside of jobs? If you go into operators and try to send a test email,
it is successful?
Also, is your SQL server on a different subnet that where you are logged in?
Perhaps the routers are blocking the type of traffic that is produced by the
net send's (network popup's)
To test this you could go on the server and type in a command prompt, "net
send yourloginname hello" and see if it pops up on your screen.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jim Heavey" <Annomous@.hotmail.com> wrote in message
news:O8v7OkumDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Hello, I am trying to figure out the most appropriate means to schedule a
> procedure to run at a specified interval during the day.
> I see the SQL Server has "SQL Server Agent" which appears to allow you to
> schedule jobs to be run at periodic times, but unfortuneately the smallest
> interval that I can find is every hour and I was looking to schedule this
to
> run every few minutes. What other means is available to me to schdeule
the
> procedure to run every few minutes?
> I did play around with the "SQL Server Agent" and the scheduling seems to
work
> just fine, but the email notification never occurs. The message that I
> located for why the email notification did not occur is as follows "The
job
> succeeded. The Job was invoked by Schedule 1 (MyFirstSchedule). The last
> step to run was step 1 (First Step). NOTE: Failed to notify 'JimHeavey'
via
> network popup. NOTE: Failed to notify 'JimHeavey' via email."
> What do I not have set up correctly to cause the email notification not to
> occur?
> Thanks in advance for your assistance!!!!!!!!!!
>
>
>|||OK how do you configure the job to run every minute?
I am running the MSDE version of SQL server and I have gotten all of the tools
from an accedemic version of the software from school. It would not load the
full version of the product on my XP machine for some reason. But to me it
looks like full blown version of SQL server.
I have run anything through "SQL mail". Does this have to be configure for
the mail feature to work for job scheduling? Did not seem to mind going
through the script. How do I go about setting that up?
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:OzojzxumDHA.2820@.TK2MSFTNGP10.phx.gbl...
> You can schedule a job to run every minute if you want. Does your SQL mail
> work outside of jobs? If you go into operators and try to send a test email,
> it is successful?
> Also, is your SQL server on a different subnet that where you are logged in?
> Perhaps the routers are blocking the type of traffic that is produced by the
> net send's (network popup's)
> To test this you could go on the server and type in a command prompt, "net
> send yourloginname hello" and see if it pops up on your screen.
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Jim Heavey" <Annomous@.hotmail.com> wrote in message
> news:O8v7OkumDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Hello, I am trying to figure out the most appropriate means to schedule a
> > procedure to run at a specified interval during the day.
> >
> > I see the SQL Server has "SQL Server Agent" which appears to allow you to
> > schedule jobs to be run at periodic times, but unfortuneately the smallest
> > interval that I can find is every hour and I was looking to schedule this
> to
> > run every few minutes. What other means is available to me to schdeule
> the
> > procedure to run every few minutes?
> >
> > I did play around with the "SQL Server Agent" and the scheduling seems to
> work
> > just fine, but the email notification never occurs. The message that I
> > located for why the email notification did not occur is as follows "The
> job
> > succeeded. The Job was invoked by Schedule 1 (MyFirstSchedule). The last
> > step to run was step 1 (First Step). NOTE: Failed to notify 'JimHeavey'
> via
> > network popup. NOTE: Failed to notify 'JimHeavey' via email."
> >
> > What do I not have set up correctly to cause the email notification not to
> > occur?
> >
> > Thanks in advance for your assistance!!!!!!!!!!
> >
> >
> >
> >
> >
>

Scheduling reports to run

Is there a way to schedule reports to run on a specif day and time and save them to excel files?

Hello John,

Yes, of course you can.

Open Report Manager. (http://ServerName/Reports) and drill down to your report you want to schedule. Click on the Properties tab from the tob and the Data Sources tab from the left. Verify that you connection is correct and select 'Credentials stored securely in the report server' and give it an account to run the report. Then go to the Subscriptions tab from the top and hit 'New Subscription'. If you want to save to Excel, then select 'Report Server File Share' and set your options. Down at the bottom, you can set the schedule of the report.

Hope this helps.

Jarret

|||Thanks, Jarret. That's easy enough.sql

Scheduling Reports

How are reporting services reports scheduled to run and email? I don't see
any links in the SQL Server Business Intelligence Development Studio. Are
there plug ins or something else from microsoft that could be missing? I have
SQL Server 2005 SP1 running on my PC (windows 2003). Any step by step
documentation?Reports are scheduled through Report Manager... Go to
http://<yourserver>/reports
Then select a report.
You should see a subscriptions tab, where you can schedule.
To schedule a report, the data source MUST use Credentials Stored Securely
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"dbach" wrote:
> How are reporting services reports scheduled to run and email? I don't see
> any links in the SQL Server Business Intelligence Development Studio. Are
> there plug ins or something else from microsoft that could be missing? I have
> SQL Server 2005 SP1 running on my PC (windows 2003). Any step by step
> documentation?
>

scheduling question for SQL Server agent job for 1/1,4/1,7/1,10,1

HI I am executing a stored procedure from a job using the server agent but
can not create the correct run times. I only want it to run 4 times a year,
the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
anyone has any ideas?
Thanks.
--
Paul G
Software engineer.I've never used that scheduler but I got curious. Assuming you are
using SQL Server 2000, it appears that if you choose Occurs Monthly,
then Day [1] of every [3] months, with a start date of 110/1/2006, it
would do what you ask.
But that is not the voice of experience, just the way it looks.
Roy Harvey
Beacon Falls, CT
On Thu, 7 Sep 2006 15:53:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>HI I am executing a stored procedure from a job using the server agent but
>can not create the correct run times. I only want it to run 4 times a year,
>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
>anyone has any ideas?
>Thanks.|||What's that start date Roy?
I think that perhaps Roy meant a start date of 10/1/2006...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:bub1g2tvcgqffb148lblutfmm1f519338p@.4ax.com...
> I've never used that scheduler but I got curious. Assuming you are
> using SQL Server 2000, it appears that if you choose Occurs Monthly,
> then Day [1] of every [3] months, with a start date of 110/1/2006, it
> would do what you ask.
> But that is not the voice of experience, just the way it looks.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 7 Sep 2006 15:53:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>>HI I am executing a stored procedure from a job using the server agent but
>>can not create the correct run times. I only want it to run 4 times a
>>year,
>>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering
>>if
>>anyone has any ideas?
>>Thanks.|||On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What's that start date Roy?
>I think that perhaps Roy meant a start date of 10/1/2006...
What? You mean your calendar does not have month 110?
Thanks for catching that, Arnie.
Roy|||yep seems to work thanks.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
> >What's that start date Roy?
> >
> >I think that perhaps Roy meant a start date of 10/1/2006...
> What? You mean your calendar does not have month 110?
> Thanks for catching that, Arnie.
> Roy
>

scheduling question for SQL Server agent job for 1/1,4/1,7/1,10,1

HI I am executing a stored procedure from a job using the server agent but
can not create the correct run times. I only want it to run 4 times a year
,
the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering if
anyone has any ideas?
Thanks.
--
Paul G
Software engineer.I've never used that scheduler but I got curious. Assuming you are
using SQL Server 2000, it appears that if you choose Occurs Monthly,
then Day [1] of every [3] months, with a start date of 110/1/2006, i
t
would do what you ask.
But that is not the voice of experience, just the way it looks.
Roy Harvey
Beacon Falls, CT
On Thu, 7 Sep 2006 15:53:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:

>HI I am executing a stored procedure from a job using the server agent but
>can not create the correct run times. I only want it to run 4 times a yea
r,
>the morning of 1/1,4/1,7/1 and 10/1 for each year. Anyhow just wondering i
f
>anyone has any ideas?
>Thanks.|||What's that start date Roy?
I think that perhaps Roy meant a start date of 10/1/2006...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:bub1g2tvcgqffb148lblutfmm1f519338p@.
4ax.com...[vbcol=seagreen]
> I've never used that scheduler but I got curious. Assuming you are
> using SQL Server 2000, it appears that if you choose Occurs Monthly,
> then Day [1] of every [3] months, with a start date of 110/1/2006,
it
> would do what you ask.
> But that is not the voice of experience, just the way it looks.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 7 Sep 2006 15:53:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>|||On Thu, 7 Sep 2006 21:35:20 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>What's that start date Roy?
>I think that perhaps Roy meant a start date of 10/1/2006...
What? You mean your calendar does not have month 110?
Thanks for catching that, Arnie.
Roy

Wednesday, March 28, 2012

Scheduling perl scripts with sqlagent

SQL2K SP3a Win2k Pro SP4
Probably one for people using perl for scripting.
I'm having a problem scheduling perl scripts to run with sql agent.
All permissions are OK, PERL5LIB is set and the script will run perfectly
from the command line
When running through sql agent the following:
cmd /c "perl s:\production\getdbspaceused.pl"
It seems it won't pick up the PERL5LIB environment variable. Any ideas,
(without explicitly putting the path to the modules directory) or am I going
to have to use windows scheduler'
I've got task scheduler to run this (but i want the success/failure
notification available from sql agent)
TIA.
KevinI use ActivePerl from www.activestate.com, and have never
had any problem scheduling a Perl script via SQLServer
Agent, though I use Windows Task Scheduler more often to
run Perl scripts. If you have ActivePerl installed on the
server, you can just run a Perl script as (plus whatever
command line switches you may have) using an Operating
System Command job step type.
perl yourScript.pl
perl.exe should already be on the system path.
Linchi
>--Original Message--
>SQL2K SP3a Win2k Pro SP4
>Probably one for people using perl for scripting.
>I'm having a problem scheduling perl scripts to run with
sql agent.
>All permissions are OK, PERL5LIB is set and the script
will run perfectly
>from the command line
>When running through sql agent the following:
> cmd /c "perl s:\production\getdbspaceused.pl"
>It seems it won't pick up the PERL5LIB environment
variable. Any ideas,
>(without explicitly putting the path to the modules
directory) or am I going
>to have to use windows scheduler'
>I've got task scheduler to run this (but i want the
success/failure
>notification available from sql agent)
>
>TIA.
>Kevin
>
>.
>|||Hello Kevin,
Could you please try to run cmd /c "perl s:\production\getdbspaceused.pl"
using xp_cmdshell in the Query Analyser ?
Does that work without any problem ? If yes, you might want to create a Job
to run the same cmd and that way you can use the
SQL Agent successs/failure notifications.
Honestly, I have no experience with running the PERL scripts, but just
wanted to see if above works. Please let me know.
Thanks for using MSDN Managed Newsgroups.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <KJGNews@.hotmail.com>
| Subject: Scheduling perl scripts with sqlagent
| Date: Wed, 21 Jan 2004 09:21:02 -0000
| Lines: 24
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OTbE$9$3DHA.1428@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 62.190.182.219
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08
.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:325288
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| SQL2K SP3a Win2k Pro SP4
|
| Probably one for people using perl for scripting.
|
| I'm having a problem scheduling perl scripts to run with sql agent.
|
| All permissions are OK, PERL5LIB is set and the script will run perfectly
| from the command line
|
| When running through sql agent the following:
| cmd /c "perl s:\production\getdbspaceused.pl"
|
| It seems it won't pick up the PERL5LIB environment variable. Any ideas,
| (without explicitly putting the path to the modules directory) or am I
going
| to have to use windows scheduler'
|
| I've got task scheduler to run this (but i want the success/failure
| notification available from sql agent)
|
|
| TIA.
| Kevin
|
|
||||Thanks everybody for the help.
I came in this morning and the job has run, successfully and I don't know
why, although
I'm happy about this. Can only assume it took a period of time before the
environment
variable was recognised by the OS?
Anyway, thanks once again.
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:AZhoRpJ4DHA.3648@.cpmsftngxa07.phx.gbl...
>
> Hello Kevin,
> Could you please try to run cmd /c "perl s:\production\getdbspaceused.pl"
> using xp_cmdshell in the Query Analyser ?
> Does that work without any problem ? If yes, you might want to create a
Job
> to run the same cmd and that way you can use the
> SQL Agent successs/failure notifications.
> Honestly, I have no experience with running the PERL scripts, but just
> wanted to see if above works. Please let me know.
> Thanks for using MSDN Managed Newsgroups.
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <KJGNews@.hotmail.com>
> | Subject: Scheduling perl scripts with sqlagent
> | Date: Wed, 21 Jan 2004 09:21:02 -0000
> | Lines: 24
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> | Message-ID: <OTbE$9$3DHA.1428@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: 62.190.182.219
> | Path:
>
cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08
> phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:325288
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | SQL2K SP3a Win2k Pro SP4
> |
> | Probably one for people using perl for scripting.
> |
> | I'm having a problem scheduling perl scripts to run with sql agent.
> |
> | All permissions are OK, PERL5LIB is set and the script will run
perfectly
> | from the command line
> |
> | When running through sql agent the following:
> | cmd /c "perl s:\production\getdbspaceused.pl"
> |
> | It seems it won't pick up the PERL5LIB environment variable. Any ideas,
> | (without explicitly putting the path to the modules directory) or am I
> going
> | to have to use windows scheduler'
> |
> | I've got task scheduler to run this (but i want the success/failure
> | notification available from sql agent)
> |
> |
> | TIA.
> | Kevin
> |
> |
> |
>

Scheduling perl scripts with sqlagent

SQL2K SP3a Win2k Pro SP4
Probably one for people using PERL for scripting.
I'm having a problem scheduling PERL scripts to run with sql agent.
All permissions are OK, PERL5LIB is set and the script will run perfectly
from the command line
When running through sql agent the following:
cmd /c "perl s:\production\getdbspaceused.pl"
It seems it won't pick up the PERL5LIB environment variable. Any ideas,
(without explicitly putting the path to the modules directory) or am I going
to have to use windows scheduler'
I've got task scheduler to run this (but i want the success/failure
notification available from sql agent)
TIA.
KevinI use ActivePerl from www.activestate.com, and have never
had any problem scheduling a PERL script via SQLServer
Agent, though I use Windows Task Scheduler more often to
run PERL scripts. If you have ActivePerl installed on the
server, you can just run a PERL script as (plus whatever
command line switches you may have) using an Operating
System Command job step type.
perl yourScript.pl
perl.exe should already be on the system path.
Linchi

quote:

>--Original Message--
>SQL2K SP3a Win2k Pro SP4
>Probably one for people using PERL for scripting.
>I'm having a problem scheduling PERL scripts to run with

sql agent.

quote:

>All permissions are OK, PERL5LIB is set and the script

will run perfectly

quote:

>from the command line
>When running through sql agent the following:
> cmd /c "perl s:\production\getdbspaceused.pl"
>It seems it won't pick up the PERL5LIB environment

variable. Any ideas,

quote:

>(without explicitly putting the path to the modules

directory) or am I going

quote:

>to have to use windows scheduler'
>I've got task scheduler to run this (but i want the

success/failure

quote:

>notification available from sql agent)
>
>TIA.
>Kevin
>
>.
>

|||Hello Kevin,
Could you please try to run cmd /c "perl s:\production\getdbspaceused.pl"
using xp_cmdshell in the Query Analyser ?
Does that work without any problem ? If yes, you might want to create a Job
to run the same cmd and that way you can use the
SQL Agent successs/failure notifications.
Honestly, I have no experience with running the PERL scripts, but just
wanted to see if above works. Please let me know.
Thanks for using MSDN Managed Newsgroups.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
| From: <KJGNews@.hotmail.com>
| Subject: Scheduling PERL scripts with sqlagent
| Date: Wed, 21 Jan 2004 09:21:02 -0000
| Lines: 24
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OTbE$9$3DHA.1428@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 62.190.182.219
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08
.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:325288
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| SQL2K SP3a Win2k Pro SP4
|
| Probably one for people using PERL for scripting.
|
| I'm having a problem scheduling PERL scripts to run with sql agent.
|
| All permissions are OK, PERL5LIB is set and the script will run perfectly
| from the command line
|
| When running through sql agent the following:
| cmd /c "perl s:\production\getdbspaceused.pl"
|
| It seems it won't pick up the PERL5LIB environment variable. Any ideas,
| (without explicitly putting the path to the modules directory) or am I
going
| to have to use windows scheduler'
|
| I've got task scheduler to run this (but i want the success/failure
| notification available from sql agent)
|
|
| TIA.
| Kevin
|
|
||||Thanks everybody for the help.
I came in this morning and the job has run, successfully and I don't know
why, although
I'm happy about this. Can only assume it took a period of time before the
environment
variable was recognised by the OS?
Anyway, thanks once again.
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:AZhoRpJ4DHA.3648@.cpmsftngxa07.phx.gbl...

quote:

>
> Hello Kevin,
> Could you please try to run cmd /c "perl s:\production\getdbspaceused.pl"
> using xp_cmdshell in the Query Analyser ?
> Does that work without any problem ? If yes, you might want to create a

Job

quote:

> to run the same cmd and that way you can use the
> SQL Agent successs/failure notifications.
> Honestly, I have no experience with running the PERL scripts, but just
> wanted to see if above works. Please let me know.
> Thanks for using MSDN Managed Newsgroups.
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no

rights.

quote:

> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <KJGNews@.hotmail.com>
> | Subject: Scheduling PERL scripts with sqlagent
> | Date: Wed, 21 Jan 2004 09:21:02 -0000
> | Lines: 24
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> | Message-ID: <OTbE$9$3DHA.1428@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: 62.190.182.219
> | Path:
>

cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08

quote:

> phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:325288
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | SQL2K SP3a Win2k Pro SP4
> |
> | Probably one for people using PERL for scripting.
> |
> | I'm having a problem scheduling PERL scripts to run with sql agent.
> |
> | All permissions are OK, PERL5LIB is set and the script will run

perfectly

quote:

> | from the command line
> |
> | When running through sql agent the following:
> | cmd /c "perl s:\production\getdbspaceused.pl"
> |
> | It seems it won't pick up the PERL5LIB environment variable. Any ideas,
> | (without explicitly putting the path to the modules directory) or am I
> going
> | to have to use windows scheduler'
> |
> | I've got task scheduler to run this (but i want the success/failure
> | notification available from sql agent)
> |
> |
> | TIA.
> | Kevin
> |
> |
> |
>

Scheduling Perfmon Recurring

Is there a way to schedule perfmon to run on a recurring schedule. I need it
to run during the business everyday for a week and currently will have to go
into the each server and set the schedule everyday. Is there a way to start
it remotely?
Thom
You can use the Logman utility that is available with XP or Win2K resource
kit. But why start and stop it each day? It does not take up that much
disk space. Are you running the gui or making a trace log? Open up perfmon
and expand the node in the treeview on the left where it says "Performance
Logs and Alerts". Then right click on the node that says "Counter Logs" and
choose "New Log settigs". Then give it a name and fill int he settings.
This will create a trace file that you can load back into perfmon later when
you want to read it or use the Logman utility to load the results into a
table. In either case there is a section for scheduling and you can tell it
to stop at the end of the week.
Andrew J. Kelly SQL MVP
"Thom" <Thom@.discussions.microsoft.com> wrote in message
news:37F072A2-BB39-4FA1-9F22-0B9E77CBC1EA@.microsoft.com...
> Is there a way to schedule perfmon to run on a recurring schedule. I need
> it
> to run during the business everyday for a week and currently will have to
> go
> into the each server and set the schedule everyday. Is there a way to
> start
> it remotely?
> Thom

Scheduling outside SRS

Hello All!
I have a requirement to set a schedule to run a report on the Tuesday
following the last Saturday of the month. It appears that this is not
possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
and to make it more complex, if the Tuesday in in the new month, quarter, or
year then the parameter should select the previous month, quarter, or year
not the default of current month, current quarter, or current year. I think
the last part can easily be handled by getting the parameter values from the
date of the last saturday of the month, which would always give you the
correct month for month end reporting no matter if the next Tues is in the
next month or not.
what I need is the syntax for defining the parameters and rendering the
report. I would also like to specify a network drive where it can be
archived, email a link to an audience, and save a snapshot in history.
Can someone please help me with this? If not all then parts would be
appreciated!!!
AnthonyI am in the same situation. Were you able to resolve this?
--
---
Yes, I searched first :)
"anthonysjo" wrote:
> Hello All!
> I have a requirement to set a schedule to run a report on the Tuesday
> following the last Saturday of the month. It appears that this is not
> possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
> and to make it more complex, if the Tuesday in in the new month, quarter, or
> year then the parameter should select the previous month, quarter, or year
> not the default of current month, current quarter, or current year. I think
> the last part can easily be handled by getting the parameter values from the
> date of the last saturday of the month, which would always give you the
> correct month for month end reporting no matter if the next Tues is in the
> next month or not.
> what I need is the syntax for defining the parameters and rendering the
> report. I would also like to specify a network drive where it can be
> archived, email a link to an audience, and save a snapshot in history.
> Can someone please help me with this? If not all then parts would be
> appreciated!!!
> Anthony
>|||Yes. Here is what you do. Create a subscription or a shared schedule but
set it to run only once and make it in the past. This will create a Schedule
ID in the database that you can reference later. Then open the Report Server
Database and return all rows for either the Schedules or Subcriptions table
depending on what you created. Copy the Schdule ID out of the table and make
note of the Event Type.
Now that you have this you can use the following code to fire the report
manually:
exec ReportServer.dbo.AddEvent
@.EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@.EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database
I put this code inside the following stored procedure that is executed every
tuesday by a SQL job. If the @.myoutput date = Getdate then it executes the
code above otherwise it does nothing.
CREATE PROC dbo.MONTH_END_SCHEDULE as
--Declare variables
declare @.myoutputdate datetime
declare @.mydate datetime
declare @.minus int
declare @.TuesdayFound char(1)
declare @.subject varchar (255)
--set variables
set @.TuesdayFound='N'
--seed the date with the last day of the month
set
@.mydate=dateadd(dd,-1,convert(datetime,convert(varchar(2),datepart(mm,dateadd(mm,1,getdate())))+'/1/'+
convert(varchar(4),datepart(yy,dateadd(mm,1,getdate())))))
--a variable to backwards through the days of the month
set @.minus=0
WHILE @.TuesdayFound = 'N'
BEGIN
if datepart(dw,dateadd(dd,(@.minus*-1),@.mydate))=7 -- Find Last Saturday
BEGIN
set @.myoutputdate=dateadd(dd, 3,(dateadd(dd,(@.minus*-1),@.mydate))) --This
will add 3 days to the last Saturday
set @.TuesdayFound = 'Y'
END
set @.minus=@.minus+1
END
print @.myoutputdate
if datepart(dy,(getdate()))=datepart(dy,(@.myoutputdate))
BEGIN
exec ReportServer.dbo.AddEvent
@.EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@.EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database
PRINT datename(mm, @.mydate)+ ' ' + datename(yyyy, @.mydate)+ ' Reports Fired '
SET @.subject = datename(mm, @.mydate)+ ' ' + datename(yyyy, @.mydate)+ ' Month
End reports are ready for viewing '
Exec master..xp_sendmail
@.recipients = 'someone@.somewhere.com',
@.copy_recipients = 'someone@.somewhere.com',
@.subject = @.subject,
@.message = 'Month End reports are now available via reporting services.
You can click on the link below and you will be taken directly to the
Month-End reports folder where you may choose to view the most recient
reports or view the history for archived reports.
http://localhost/Reports
If you have any questions please send an email to
someone@.somewhere.com'
END
GO
"Kmistic" wrote:
> I am in the same situation. Were you able to resolve this?
> --
> ---
> Yes, I searched first :)
>
> "anthonysjo" wrote:
> > Hello All!
> >
> > I have a requirement to set a schedule to run a report on the Tuesday
> > following the last Saturday of the month. It appears that this is not
> > possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
> > and to make it more complex, if the Tuesday in in the new month, quarter, or
> > year then the parameter should select the previous month, quarter, or year
> > not the default of current month, current quarter, or current year. I think
> > the last part can easily be handled by getting the parameter values from the
> > date of the last saturday of the month, which would always give you the
> > correct month for month end reporting no matter if the next Tues is in the
> > next month or not.
> >
> > what I need is the syntax for defining the parameters and rendering the
> > report. I would also like to specify a network drive where it can be
> > archived, email a link to an audience, and save a snapshot in history.
> >
> > Can someone please help me with this? If not all then parts would be
> > appreciated!!!
> >
> > Anthony
> >

Scheduling once per hour for part of a day

Hi!
I wonder if it is possible in any way to schedule reports to run every hour
each day between 0800 and 1700?
/MattiasSure, select the report from http://yourserver/reports, go to properties and
select "Render this report from an execution snapshot".
THen you may set the execution schedule as you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message
news:D39C0FF0-5482-4FA4-9B9D-8AF54C44E60E@.microsoft.com...
> Hi!
> I wonder if it is possible in any way to schedule reports to run every
hour
> each day between 0800 and 1700?
> /Mattias|||I still can't see a way of scheduling the report to run once an hour during
just a part of the day as my question was.
I can schedule it to run once per hour 24 hours a day, but not just between
e.g. 0800 and 1700, or am I just dumb?
/Mattias
"Wayne Snyder" wrote:
> Sure, select the report from http://yourserver/reports, go to properties and
> select "Render this report from an execution snapshot".
> THen you may set the execution schedule as you wish..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> news:D39C0FF0-5482-4FA4-9B9D-8AF54C44E60E@.microsoft.com...
> > Hi!
> >
> > I wonder if it is possible in any way to schedule reports to run every
> hour
> > each day between 0800 and 1700?
> >
> > /Mattias
>
>|||I answered your other post on this question.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"MattiasT" <MattiasT@.discussions.microsoft.com> wrote in message
news:846AF338-47C2-44B0-A97D-0BA6C01F0464@.microsoft.com...
> I still can't see a way of scheduling the report to run once an hour
during
> just a part of the day as my question was.
> I can schedule it to run once per hour 24 hours a day, but not just
between
> e.g. 0800 and 1700, or am I just dumb?
> /Mattias
> "Wayne Snyder" wrote:
> > Sure, select the report from http://yourserver/reports, go to properties
and
> > select "Render this report from an execution snapshot".
> > THen you may set the execution schedule as you wish..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> > news:D39C0FF0-5482-4FA4-9B9D-8AF54C44E60E@.microsoft.com...
> > > Hi!
> > >
> > > I wonder if it is possible in any way to schedule reports to run every
> > hour
> > > each day between 0800 and 1700?
> > >
> > > /Mattias
> >
> >
> >

Scheduling of snapshot agent

I have replicated a database as a snapshot replication. I have
scheduled distribution agent to run at the interval of one hour daily
from 9:00 AM to 5:30 PM. Do i also need to schedule snapshot agent
accordingly? What will be optimal schedule depending on the scheduling
of distribution agent?
Quick reply will be appreciated
Ali Raza Rajput
Database Administratoryes you have to configure the snapshot agent to run
before synchoronization runs otherwise no data will be updated
--
thanks,
joey
"perhiyar" wrote:

> I have replicated a database as a snapshot replication. I have
> scheduled distribution agent to run at the interval of one hour daily
> from 9:00 AM to 5:30 PM. Do i also need to schedule snapshot agent
> accordingly? What will be optimal schedule depending on the scheduling
> of distribution agent?
> Quick reply will be appreciated
> Ali Raza Rajput
> Database Administrator
>|||Thanks for reply joeydj
I am monitoring the snapshot agent and distribution agent.I have
noticed the latency for distribution agent upto 352373 (msec) on LAN.
I think it is abnormal what do you say?
Ali Raza Perhiyar
joeydj wrote:
> yes you have to configure the snapshot agent to run
> before synchoronization runs otherwise no data will be updated
> --
> thanks,
> --
> joey
>
> "perhiyar" wrote:
>sql

Scheduling not working

Hello.
Does RS have to be installed on the same machine as SQL Server in order to
get scheduling to work?
I created a schedule to run every minute (for testing). The time has come
and gone, and the Next Run time on the Shared Schedules screen isn't
changing, and the Last Run column shows Never. So scheduling isn't working.
Any ideas?
Thanks in advance,
MikeNo, I don't think so. However, scheduling is dependent on SQL Agent running
so make sure it is running on the database server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mike" <milop@.slomins.com> wrote in message
news:OCyTWKvuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't
> working.
> Any ideas?
> Thanks in advance,
> Mike
>|||Hi, Mike.
1) RS CAN be on different machine.
2)Make sure that the SQL Agent is running.
Regards, ProJester,
MSN Programmer.
"mike" wrote:
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't working.
> Any ideas?
> Thanks in advance,
> Mike
>
>

Scheduling multiple reports at one time

Hi,

I need my reports to run at the 4rth day of every month. I have packs of reports, and typically upto 18 reports in a pack and these packs have to run on the 4rth of every month.

How can i schedule the 18 reports to run at the same time. I don't want to schedule a time for every report ,this is very laborious.Is there any option in SSRS for simulataneously scheduling of these reports?

Thanks

Hi,

You can achieve this functionality by using a Shared Schedule.

In the case the question is if you can assign the schedule to all reports at once, the answer is not by using the web interface, but you can create an script to do so.

In the case you're asking to chain the report processing (i.e.:start report 2 when report 1 is finished), you will need to revert to a script again.

HTH,

Jordi Rambla
MVP SQL Server (Reporting Services)
Certia - rambla informàtica (http://www.certia.net)
Solid Quality Learning (http://www.solidqualitylearning.com)

sql

Scheduling Montly Report

Hi all,

I currently have a report which needs to be scheduled to run on every 1st of the month to get records of last month. For example, on Feb. 01 the report will get all records by the date range of Jan. 01 to Jan. 31. My current report does not have any defined report parameter. It gets all the date range it finds from the table.

Please advise how can I start with this.

Any help is much appreciated! Thanks!

Hi,

Couldnt get it completely.

you want to run a report on monthly basis, then you need to have two datetime parameters i.e. from_time and to_time, then you want default value to be last months dates, use expression for the defualt value....

your expr. for "from time" should be like

=switch(month(now())=1,"01/01/2007",

month(now())=2,"02/01/2007, and in same way you can have it for to time frame....

HTH

Priyank

|||

As i understand the requirement over here is to have a report which shows the data for previous month

say the report is executed on mar 1st then the report should display data till feb28th correct?

For this a report has to be created with 2 parameters Startdate and EndDate.

However subscription can be created in Report Manager.

Regards

Smitha

|||

You don't need a param for this, although to give you an perfectly appropriate example it would be good to know if you are calling a stored procedure or writing the query directly in the report, or what.

Let's say you are writing the query directly in the report, not a stored procedure.

You already know that you can schedule it to run on the first of the month, right? Here is a query that will do what you want, given that you know you are running it every month on the first:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,GETDATE()-1,<YOUR DATE COLUMN>) = 0

' the default behavior of the date subtraction is by days, see?

' and you're running the subscription on the first of the month,

' so this works.

Let's make it better. Here is a query that will do what you want, no matter what date of the month it is run on, which is : provide the results for the month *before* the month in which it is run:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,GETDATE()),

<YOUR DATE COLUMN>) = 0

Now, if you prefer, you can add a parameter and give your parameter the default value of Today (you may have to do some CASTing in here, not checking this):

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,@.YourParam),

<YOUR DATE COLUMN>) = 0

... hope this helps,

>L<

|||

Thank you all for the helpful solutions.

Yes basically it is just to get a report of previous month when it is scheduled to run.

|||

Lisa that's a cool little function!

Only question I have it whether it would work if you had data for more than one year in the table? i.e If you had 2 rows, one with July 2006 and one with July 2007 wouldn't the DateDiff(month, etc) return 0 for that even though the year is different?

As my own $0.02 I usually use something like

Beginning of last month

DATEADD(month,DATEDIFF(month,0,getdate)-1,0)

Beginning of this month

DATEADD(month,DATEDIFF(month,0,getdate),0)

Beginning of next month

DATEADD(month,DATEDIFF(month,0,getdate)+1,0)

which is sort of based off similar logic. Found it on a forum, and loved it. Can also do days/weeks by just switching the period name.

|||

>> whether it would work if you had data for more than one year in the table

It will actually work fine, D, check it out:

select datediff(month, '1/1/2007','1/1/2004')

will return -36 even though both dates are (obviously) in January. 3 * 12.

It's a *difference of months*. Not a difference of *month numbers* <s>.

>L<

|||Right you are - I should have run it through Query Analyzer first

Scheduling Montly Report

Hi all,

Please advise how can I start with this.

Any help is much appreciated! Thanks!

Hi,

Couldnt get it completely.

your expr. for "from time" should be like

=switch(month(now())=1,"01/01/2007",

month(now())=2,"02/01/2007, and in same way you can have it for to time frame....

HTH

Priyank

|||

As i understand the requirement over here is to have a report which shows the data for previous month

say the report is executed on mar 1st then the report should display data till feb28th correct?

For this a report has to be created with 2 parameters Startdate and EndDate.

However subscription can be created in Report Manager.

Regards

Smitha

|||

Let's say you are writing the query directly in the report, not a stored procedure.

You already know that you can schedule it to run on the first of the month, right? Here is a query that will do what you want, given that you know you are running it every month on the first:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,GETDATE()-1,<YOUR DATE COLUMN>) = 0

' the default behavior of the date subtraction is by days, see?

' and you're running the subscription on the first of the month,

' so this works.

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,GETDATE()),

<YOUR DATE COLUMN>) = 0

Now, if you prefer, you can add a parameter and give your parameter the default value of Today (you may have to do some CASTing in here, not checking this):

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,@.YourParam),

<YOUR DATE COLUMN>) = 0

... hope this helps,

>L<

|||

Thank you all for the helpful solutions.

Yes basically it is just to get a report of previous month when it is scheduled to run.

|||

Lisa that's a cool little function!

As my own $0.02 I usually use something like

Beginning of last month

DATEADD(month,DATEDIFF(month,0,getdate)-1,0)

Beginning of this month

DATEADD(month,DATEDIFF(month,0,getdate),0)

Beginning of next month

DATEADD(month,DATEDIFF(month,0,getdate)+1,0)

which is sort of based off similar logic. Found it on a forum, and loved it. Can also do days/weeks by just switching the period name.

|||

>> whether it would work if you had data for more than one year in the table

It will actually work fine, D, check it out:

select datediff(month, '1/1/2007','1/1/2004')

will return -36 even though both dates are (obviously) in January. 3 * 12.

It's a *difference of months*. Not a difference of *month numbers* <s>.

>L<

|||Right you are - I should have run it through Query Analyzer first

Friday, March 30, 2012

scheduling the job to process a dts package

Scheduling Subscription On Day 30 of a Month

scheduling stored procedure

Scheduling SQL 2005 SSIS packages

Scheduling SQL 2005 SSIS packages

Scheduling Running Of Proc's

Scheduling reports to run

Scheduling Reports

scheduling question for SQL Server agent job for 1/1,4/1,7/1,10,1

scheduling question for SQL Server agent job for 1/1,4/1,7/1,10,1

Wednesday, March 28, 2012

Scheduling perl scripts with sqlagent

Scheduling perl scripts with sqlagent

Scheduling Perfmon Recurring

Scheduling outside SRS

Scheduling once per hour for part of a day

Scheduling of snapshot agent

Scheduling not working

Scheduling multiple reports at one time

Scheduling Montly Report

Scheduling Montly Report

T-SQL Script

Blog Archive

About Me