schedule end of month

i'd like to schedule some reports to run just before midnight on the last day
of each month. it doesn't appear that the RS interface allows me to create
just one schedule that would do that (i end up creating 3 schedules: 1 for
months ending 30th, 1 for months ending 31st, 1 for February).
on the sql agent side of things, its interface allows scheduling on the last
day of the month, but you're not supposed to mess with that...
am i missing something? is there an easy workaround? thanksOn Jul 27, 9:12 am, burkecrosby
<burkecro...@.discussions.microsoft.com> wrote:
> i'd like to schedule some reports to run just before midnight on the last day
> of each month. it doesn't appear that the RS interface allows me to create
> just one schedule that would do that (i end up creating 3 schedules: 1 for
> months ending 30th, 1 for months ending 31st, 1 for February).
> on the sql agent side of things, its interface allows scheduling on the last
> day of the month, but you're not supposed to mess with that...
> am i missing something? is there an easy workaround? thanks
Took me a while to figure it out, but it works great. It might not be
exactly what you want, but who knows it may work out for you. I got a
request from a user that they get the previous month's report on the
first of the month at 7am (time doesn't matter here). So for example,
on August 1st at 7am, she will receive a report for July1-31st. You
can change the time to be 12:00am or whatever.
First in your report make two datasets with the following:
Dataset 1 (I called mine StartofMonth):
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
Dataset 2 (I called mine EndofMonth):
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
In my main dataset, the code looks for data between two parameters
@.startdate and @.enddate. Default these parameters (under
Report>Report Parameters...) to the corresponding datasets. The
"field" here should just be ID. Run the report, it should pull data
from the first to the last of last month.
Now go to your subscription and make your schedule. Under Schedule
Details (when you select to make your own schedule) click the Month
radio button. Pick all the month (using the check boxes) and then
select the radio labeled "On Calendar day(s):" and place a 1 in that
box. Now just adjust your start time as you see necessary. August
1st is coming soon, so you can see the result of your effort soon!!!
Only issue with this work around is that whenever your users open the
report (if they ever will) the dates will default to the first and
last day of the previous month. Oh well... they can change it
manually I guess or you can make a specific report (duplicate) that is
just for subscriptions. Also, for sanity, you might want to display
your parameters on the report so you see that it worked correctly.
Let me know if that works out for you!|||thanks Ayman. i can see and understand your method and may use that for
future reports. i was hoping not to have to redo the report query because of
the oddities of my source data.
i don't understand why MSFT has the difference between the scheduling
interfaces of RS vs SQL Server Agent. seems like RS could have just
inherited from SQL Agent...
burke
"Ayman" wrote:
> On Jul 27, 9:12 am, burkecrosby
> <burkecro...@.discussions.microsoft.com> wrote:
> > i'd like to schedule some reports to run just before midnight on the last day
> > of each month. it doesn't appear that the RS interface allows me to create
> > just one schedule that would do that (i end up creating 3 schedules: 1 for
> > months ending 30th, 1 for months ending 31st, 1 for February).
> >
> > on the sql agent side of things, its interface allows scheduling on the last
> > day of the month, but you're not supposed to mess with that...
> >
> > am i missing something? is there an easy workaround? thanks
> Took me a while to figure it out, but it works great. It might not be
> exactly what you want, but who knows it may work out for you. I got a
> request from a user that they get the previous month's report on the
> first of the month at 7am (time doesn't matter here). So for example,
> on August 1st at 7am, she will receive a report for July1-31st. You
> can change the time to be 12:00am or whatever.
> First in your report make two datasets with the following:
> Dataset 1 (I called mine StartofMonth):
> select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
> Dataset 2 (I called mine EndofMonth):
> select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
> In my main dataset, the code looks for data between two parameters
> @.startdate and @.enddate. Default these parameters (under
> Report>Report Parameters...) to the corresponding datasets. The
> "field" here should just be ID. Run the report, it should pull data
> from the first to the last of last month.
> Now go to your subscription and make your schedule. Under Schedule
> Details (when you select to make your own schedule) click the Month
> radio button. Pick all the month (using the check boxes) and then
> select the radio labeled "On Calendar day(s):" and place a 1 in that
> box. Now just adjust your start time as you see necessary. August
> 1st is coming soon, so you can see the result of your effort soon!!!
> Only issue with this work around is that whenever your users open the
> report (if they ever will) the dates will default to the first and
> last day of the previous month. Oh well... they can change it
> manually I guess or you can make a specific report (duplicate) that is
> just for subscriptions. Also, for sanity, you might want to display
> your parameters on the report so you see that it worked correctly.
> Let me know if that works out for you!
>|||On Jul 27, 10:44 am, burkecrosby
<burkecro...@.discussions.microsoft.com> wrote:
> thanks Ayman. i can see and understand your method and may use that for
> future reports. i was hoping not to have to redo the report query because of
> the oddities of my source data.
> i don't understand why MSFT has the difference between the scheduling
> interfaces of RS vs SQL Server Agent. seems like RS could have just
> inherited from SQL Agent...
> burke
> "Ayman" wrote:
> > On Jul 27, 9:12 am, burkecrosby
> > <burkecro...@.discussions.microsoft.com> wrote:
> > > i'd like to schedule some reports to run just before midnight on the last day
> > > of each month. it doesn't appear that the RS interface allows me to create
> > > just one schedule that would do that (i end up creating 3 schedules: 1 for
> > > months ending 30th, 1 for months ending 31st, 1 for February).
> > > on the sql agent side of things, its interface allows scheduling on the last
> > > day of the month, but you're not supposed to mess with that...
> > > am i missing something? is there an easy workaround? thanks
> > Took me a while to figure it out, but it works great. It might not be
> > exactly what you want, but who knows it may work out for you. I got a
> > request from a user that they get the previous month's report on the
> > first of the month at 7am (time doesn't matter here). So for example,
> > on August 1st at 7am, she will receive a report for July1-31st. You
> > can change the time to be 12:00am or whatever.
> > First in your report make two datasets with the following:
> > Dataset 1 (I called mine StartofMonth):
> > select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
> > Dataset 2 (I called mine EndofMonth):
> > select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
> > In my main dataset, the code looks for data between two parameters
> > @.startdate and @.enddate. Default these parameters (under
> > Report>Report Parameters...) to the corresponding datasets. The
> > "field" here should just be ID. Run the report, it should pull data
> > from the first to the last of last month.
> > Now go to your subscription and make your schedule. Under Schedule
> > Details (when you select to make your own schedule) click the Month
> > radio button. Pick all the month (using the check boxes) and then
> > select the radio labeled "On Calendar day(s):" and place a 1 in that
> > box. Now just adjust your start time as you see necessary. August
> > 1st is coming soon, so you can see the result of your effort soon!!!
> > Only issue with this work around is that whenever your users open the
> > report (if they ever will) the dates will default to the first and
> > last day of the previous month. Oh well... they can change it
> > manually I guess or you can make a specific report (duplicate) that is
> > just for subscriptions. Also, for sanity, you might want to display
> > your parameters on the report so you see that it worked correctly.
> > Let me know if that works out for you!
I'm not that knowledgeable but I can tell you, it doesn't matter what
datasource you use the code is universal across all SQL databases.
It's an easy fix to a tedious and annoying problem. I wish there was
an easier way too, but hey it was a good coding experience for me!! I
will be using the same method across any other reports that need
subscriptions.
You don't need to change any scripts at all, well... except the main
script where you need to add the data time filter. Hmmm... not sure
what to tell you now...

schedule dts at a remote server

Hi
Can somenone help me build a DTS in SQL Server 2000 that allows to schedule
the execution of a executable file in a remote server without sql server?...
[]
RickyRicky,
try to read up on xp_cmdshell in BOL.
--
Venkat
sql server admirer
"Ricky" wrote:

> Hi
> Can somenone help me build a DTS in SQL Server 2000 that allows to schedul
e
> the execution of a executable file in a remote server without sql server?.
.
> []
> Ricky
>
>
>|||Ricky,
try to read up on xp_cmdshell in BOL.
--
Venkat
sql server admirer
"Ricky" wrote:

> Hi
> Can somenone help me build a DTS in SQL Server 2000 that allows to schedul
e
> the execution of a executable file in a remote server without sql server?.
.
> []
> Ricky
>
>
>|||I didn't understand what "xp_cmdshell in BOL" is. Can you explain better.
Sorry but i'm a newbie on this matter.
Thanks
Ricky
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:39EFAD0A-689B-441E-A444-9CC7FCA660D8@.microsoft.com...[vbcol=seagreen]
> Ricky,
> try to read up on xp_cmdshell in BOL.
> --
> Venkat
> sql server admirer
>
> "Ricky" wrote:
>|||I didn't understand what "xp_cmdshell in BOL" is. Can you explain better.
Sorry but i'm a newbie on this matter.
Thanks
Ricky
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:39EFAD0A-689B-441E-A444-9CC7FCA660D8@.microsoft.com...[vbcol=seagreen]
> Ricky,
> try to read up on xp_cmdshell in BOL.
> --
> Venkat
> sql server admirer
>
> "Ricky" wrote:
>

schedule dts at a remote server

Hi
Can somenone help me build a DTS in SQL Server 2000 that allows to schedule
the execution of a executable file in a remote server without sql server?...
[]
RickyCan you access shared folders on the remote server? If so you could use
xp_cmdshell to run the command - mapping a Network Drive if necessary. You
will need to set appropriate permissions on the shared folder.
Alternatively if you are using the DTS package to unconditionally run the
executable then instead you could just schedule the executable on the remote
server using Windows Scheduled Tasks.
Chris
"Ricky" wrote:

> Hi
> Can somenone help me build a DTS in SQL Server 2000 that allows to schedul
e
> the execution of a executable file in a remote server without sql server?.
.
> []
> Ricky
>
>
>|||Thanks for the tip. But i must go with the first option once i've tried the
second option you advice but the excutable i want to run in the remote
server doesn't do an audit to the software and hardware of the remote
server.
The executable file is audit32.exe from www.itsolutions.intuit.com
Can you design for me the dts and send it to my email?.. Or give me the
right/direct way of doing that in DTS of SQL Server 2000.
Thanks
[]
Ricky
"Chris Howarth" <ChrisHowarth@.discussions.microsoft.com> wrote in message
news:193887B6-D869-45D2-A6DC-362A62009FBA@.microsoft.com...
> Can you access shared folders on the remote server? If so you could use
> xp_cmdshell to run the command - mapping a Network Drive if necessary. You
> will need to set appropriate permissions on the shared folder.
> Alternatively if you are using the DTS package to unconditionally run the
> executable then instead you could just schedule the executable on the
> remote
> server using Windows Scheduled Tasks.
> Chris
>
> "Ricky" wrote:
>|||Ricky wrote:
> Thanks for the tip. But i must go with the first option once i've tried th
e
> second option you advice but the excutable i want to run in the remote
> server doesn't do an audit to the software and hardware of the remote
> server.
> The executable file is audit32.exe from www.itsolutions.intuit.com
> Can you design for me the dts and send it to my email?.. Or give me the
> right/direct way of doing that in DTS of SQL Server 2000.
>
So you're trying to perform a Trackit audit on a remote machine from
within SQL Server? This isn't going to work. That executable file must
be run ON THE MACHINE that you want to audit. Running it from within
SQL, via xp_cmdshell, DTS, SQL Agent, whatever, is going to execute it
on the SQL Server machine, not the remote machine. You're either going
to have to schedule it on the remote machine itself, or find some way to
open a remote command shell to the remote machine.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23eePm2flGHA.3528@.TK2MSFTNGP02.phx.gbl...
> Ricky wrote:
> So you're trying to perform a Trackit audit on a remote machine from
> within SQL Server? This isn't going to work. That executable file must
> be run ON THE MACHINE that you want to audit. Running it from within SQL,
> via xp_cmdshell, DTS, SQL Agent, whatever, is going to execute it on the
> SQL Server machine, not the remote machine. You're either going to have
> to schedule it on the remote machine itself, or find some way to open a
> remote command shell to the remote machine.
You're right but to run the Trackit audit in the remote server i only have
to
let the file in that specific remote server and share the directory where is
it.
The final purpose is to make a DTS with schedule and force the Trackit audit
run at x time. So i need help for this design. Can you help me?...
Thanks
Ricky|||Ricky wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:%23eePm2flGHA.3528@.TK2MSFTNGP02.phx.gbl...
>
> You're right but to run the Trackit audit in the remote server i only have
> to
> let the file in that specific remote server and share the directory where
is
> it.
> The final purpose is to make a DTS with schedule and force the Trackit aud
it
> run at x time. So i need help for this design. Can you help me?...
> Thanks
> Ricky
>
Running the executable from a job ON YOUR SQL SERVER is NOT going to
execute it on the remote server. To illustrate, go to your SQL Server
machine, open Explorer, navigate to the executable via the UNC path to
your remote share. If you double-click on the executable, it's going to
run within the context of your SQL Server machine.
You need to run the audit within the context of the remote machine,
meaning it needs to be scheduled ON THE REMOTE SERVER.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:elzOtfmlGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Ricky wrote:
> Running the executable from a job ON YOUR SQL SERVER is NOT going to
> execute it on the remote server. To illustrate, go to your SQL Server
> machine, open Explorer, navigate to the executable via the UNC path to
> your remote share. If you double-click on the executable, it's going to
> run within the context of your SQL Server machine.
> You need to run the audit within the context of the remote machine,
> meaning it needs to be scheduled ON THE REMOTE SERVER.
>
Yes i know that. The audit must be schedule on the Remote Server.
But couldn't i do that by creating a DTS Schedule that will run in a shared
directory where is the audit32.exe?
Thanks
Ricky|||Ricky wrote:
> Yes i know that. The audit must be schedule on the Remote Server.
> But couldn't i do that by creating a DTS Schedule that will run in a share
d
> directory where is the audit32.exe?
> Thanks
> Ricky
>
Open Windows Explorer on your SQL Server machine. Navigate to the
remote share that contains the audit32.exe file, double-click the exe
file. Where does the program run? It runs on your SQL Server machine.
If you schedule the job ON THE SQL SERVER MACHINE, SQL is going to do
exactly what you just did - it's going to pull the exe from the remote
share and run it, ON THE SQL SERVER MACHINE. I really truly don't know
a better way to explain execution context to you, perhaps you should
consult with one of your local IT guys?|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uP9vpFMmGHA.856@.TK2MSFTNGP03.phx.gbl...
> Ricky wrote:
> Open Windows Explorer on your SQL Server machine. Navigate to the remote
> share that contains the audit32.exe file, double-click the exe file.
> Where does the program run? It runs on your SQL Server machine. If you
> schedule the job ON THE SQL SERVER MACHINE, SQL is going to do exactly
> what you just did - it's going to pull the exe from the remote share and
> run it, ON THE SQL SERVER MACHINE. I really truly don't know a better way
> to explain execution context to you, perhaps you should consult with one
> of your local IT guys?
Ok. I've already understand what you mean. So what do you advice then? Once
i've already try windows schedule and didn't work well.
Thanks
Ricky|||Ricky wrote:
> Ok. I've already understand what you mean. So what do you advice then? Onc
e
> i've already try windows schedule and didn't work well.
> Thanks
> Ricky
>
Recent versions of TrackIT offer the ability to do scheduled audits.
Consult your Administrator's Guide for details on how to set one up.

schedule dts at a remote server

Hi
Can somenone help me build a DTS in SQL Server 2000 that allows to schedule
the execution of a executable file in a remote server without sql server?...
[]
RickyRicky,
try to read up on xp_cmdshell in BOL.
--
Venkat
sql server admirer
"Ricky" wrote:
> Hi
> Can somenone help me build a DTS in SQL Server 2000 that allows to schedule
> the execution of a executable file in a remote server without sql server?...
> []
> Ricky
>
>
>|||I didn't understand what "xp_cmdshell in BOL" is. Can you explain better.
Sorry but i'm a newbie on this matter.
Thanks
Ricky
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:39EFAD0A-689B-441E-A444-9CC7FCA660D8@.microsoft.com...
> Ricky,
> try to read up on xp_cmdshell in BOL.
> --
> Venkat
> sql server admirer
>
> "Ricky" wrote:
>> Hi
>> Can somenone help me build a DTS in SQL Server 2000 that allows to
>> schedule
>> the execution of a executable file in a remote server without sql
>> server?...
>> []
>> Ricky
>>
>>
>>

Schedule a scripted restore on 2005 Express from .bak file.

Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.

Hi TheGrox,

Generally speaking the sql restoration script is like this:

RESTORE DATABASE [Your_databasename] FROM DISK = N'File_Path\BCKUP.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO

However, I don't think it can be used from a .cmd script file. To run .sql script file, you need to run it within sql server query analyzer (if you run it from a .cmd script, first it will jump up a window asking your to login to sql server, even though you enter the server name and user/password and login successfully, that sql script file will be opened there while won't be executed until you click the execute button from the GUI).

I think there are two solutions for you requirment. One solution is the easist, upgrade your sql express to a full vertion and use Server Agent-> Jobs. Run that script in a job and schedule the job to run at a sepcific time frequency. I believe it won't cost you more than 5 minutes;

The second solution is to use ado.net.Open your visual studio and create a new project, build a connection to your database first and then send the restore sqlcommand to it. Compile and build you project and after that, you create a windows schedule task and run that application regularly.

Hope my suggestion helps

|||

Hi, thanks for the suggestions. The running the .sql from a .cmd file isnt a problem, i already do that to automatically back up all DB's in my SQL Express instance by having a file called BackupDB.cmd with the following code:

CLS
ECHO OFF
ECHO Testing to make sure directories exist.
IF NOT EXIST C:\SQLBackups MD C:\SQLBackups

ECHO Complete with directory creation
ECHO **********************************************************
ECHO Backing up databases......
sqlcmd -S .\SQLEXPRESS -i c:\Windows\BackupExpress.sql -o C:\SQLbackups\backup.log
ECHO **********************************************************
ECHO Backup complete. Look in the C:\SQLBackups\Backup.log file for information.

ECHO ON

The BackupExpress.sql file is simply a script that loops through all databases on the server and creates a .bak file for each of them in the specified directory. Works nicely andmeans i dont need to reconfigure anything if a new DB is created, it is just automatically included in the backups.

So i guess i just need to create a Restore.sql script using the syntax you quoted and call it as above. The command runs under the context of a valid SQL user account specified when the scheduled task is created so login is not an issue.

The script for the BackupExpress.sql file if anyone is interested in using it is:

/**File Name: BackupExpress.sqlDescription: Backs up all databases. This script is mainly meant for SQL Express instancesThe script requires a C:\SQLBackups directory by default to backup to but can be changed with the @.OutputPath variable.Accompanying file is BackupExpress.cmd, which is used to schedule the script.**/SET QUOTED_IDENTIFIEROFF USE masterGOSET NOCOUNT ON DECLARE @.dayofweekvarchar(20)SELECT @.dayofweek =CASE datepart(dw,getdate())WHEN 1THEN'Sunday'WHEN 2THEN'Monday'WHEN 3THEN'Tuesday'WHEN 4THEN'Wednesday'WHEN 5THEN'Thursday'WHEN 6THEN'Friday'WHEN 7THEN'Saturday'ENDDECLARE @.OutputPathvarchar(500)DECLARE @.DatabaseBackupFilevarchar(500)DECLARE @.FolderNamevarchar(25)DECLARE @.DatabaseNamevarchar(25)DECLARE @.strSQLvarchar(2000)DECLARE @.hostnamevarchar(255)SET @.hostname = (select replace(convert(varchar(255),serverproperty('SERVERNAME')),'\','_'))SET @.OutputPath ='C:\SQLBackups'DECLARE cur_BackupCURSOR FOR select name fromsysdatabaseswhere name !='tempdb'OPEN cur_Backup-- Fetch the db names from CursorFETCH NEXT FROM cur_BackupINTO @.DatabaseNameWHILE@.@.FETCH_STATUS = 0BEGINSET @.DatabaseBackupFile = @.OutputPath +'\' + @.hostname +'-' + @.DatabaseName +'-' + @.dayofweek +'.bak'print @.DatabaseBackupFileSET @.strSQL ='BACKUP DATABASE '+@.DatabaseName+' TO DISK = "'+ @.DatabaseBackupFile+'" WITH RETAINDAYS = 1, NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'PRINT @.strSQLEXEC (@.strSQL)FETCH NEXT FROM cur_BackupINTO @.DatabaseNameEND-- Distroy CursorCLOSE cur_BackupDEALLOCATE cur_BackupSET NOCOUNT OFF