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 DTS packages in SQLServer 2005 via the Run Time utility

All,
I installed SQLServer 2005 on a new box and installed the DTS Runtime
utility as well so that the DTS packages run in the 2005 environment.
I imported all the DTS packages but I dont know how to schedule each of
them. Would anyone know. In SQLServer 2000, "Schedule Package" menu on a DTS
package would create and schedule a job. I'm not sure how to do it here other
than creating the job manually.
Thanks,
rgnCreate an Agent job with a CmdExec jobstep. In this jobstep, you execute DTSRUN.EXE with appropriate
command-line options (see 2000 Books Online for documentation of the command-line options for
DTSRUN.EXE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:5B08D6C7-9A39-49AF-8EA5-532627D5E310@.microsoft.com...
> All,
> I installed SQLServer 2005 on a new box and installed the DTS Runtime
> utility as well so that the DTS packages run in the 2005 environment.
> I imported all the DTS packages but I dont know how to schedule each of
> them. Would anyone know. In SQLServer 2000, "Schedule Package" menu on a DTS
> package would create and schedule a job. I'm not sure how to do it here other
> than creating the job manually.
> Thanks,
> rgn
>|||rgn,
Apparently, since DTS is being deprecated the scheduling function did not
move over with the legacy support.
However, creating your own job is pretty easy, especially if you examine the
job definitions created for you on your SQL Server 2000 server. In fact, you
may be able to simply script out the jobs from SQL Server 2000 and run those
scripts on your 2005 SQL Server.
Some adjustements may be necessary. For example, if the same logins used
for the jobs on 2000 do not exist on your new 2005 server, you will need to
adjust the scripts to point to the logins you wish to use.
RLF
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:5B08D6C7-9A39-49AF-8EA5-532627D5E310@.microsoft.com...
> All,
> I installed SQLServer 2005 on a new box and installed the DTS Runtime
> utility as well so that the DTS packages run in the 2005 environment.
> I imported all the DTS packages but I dont know how to schedule each of
> them. Would anyone know. In SQLServer 2000, "Schedule Package" menu on a
> DTS
> package would create and schedule a job. I'm not sure how to do it here
> other
> than creating the job manually.
> Thanks,
> rgn
>|||Tibor/Russell,
I should have been clearer in my earlier post. I can create a job for each
one of the DTS packages with DTSRUN. However, I dont know how to generate the
encrypted details of the package name, login name, Password ... etc.
It is against our SOX policy to include password in open text.
THis example is for a different DTS on a different server altogether.[DTSRun
/~Z0xBBA31CD08C42214454A70882A4D929948FA0915B8AA465A7BEF16CCA4AC7639EB256D85DB6780227228D2D92A3FF92D1AAC3354C13AF4CCF5704F24B8EA95C5C95E33BE0EAF73A5ED7C9F1D9AA391B364F64B7473F9E27823B3EF142CE09CF1F1BF7E08A031026BC2F7E03E544C824E13C2584B3C7197F19A12371 ]
Thanks,
rgn
"Russell Fields" wrote:
> rgn,
> Apparently, since DTS is being deprecated the scheduling function did not
> move over with the legacy support.
> However, creating your own job is pretty easy, especially if you examine the
> job definitions created for you on your SQL Server 2000 server. In fact, you
> may be able to simply script out the jobs from SQL Server 2000 and run those
> scripts on your 2005 SQL Server.
> Some adjustements may be necessary. For example, if the same logins used
> for the jobs on 2000 do not exist on your new 2005 server, you will need to
> adjust the scripts to point to the logins you wish to use.
> RLF
>
> "rgn" <rgn@.discussions.microsoft.com> wrote in message
> news:5B08D6C7-9A39-49AF-8EA5-532627D5E310@.microsoft.com...
> > All,
> >
> > I installed SQLServer 2005 on a new box and installed the DTS Runtime
> > utility as well so that the DTS packages run in the 2005 environment.
> >
> > I imported all the DTS packages but I dont know how to schedule each of
> > them. Would anyone know. In SQLServer 2000, "Schedule Package" menu on a
> > DTS
> > package would create and schedule a job. I'm not sure how to do it here
> > other
> > than creating the job manually.
> >
> > Thanks,
> > rgn
> >
> >
>
>|||rgn,
I poked around a bit and found that the encrypted details are not as secure
as you might wish. So... user beware. Here is how you can generate one:
dtsrun /S ServerName /E /N PackagePathAndName /!Y
You will see that it gives a different encryption each time, but try it out
and see if the results work for you.
Since you are in SQL Server 2005, I encourage you to study up on SQL Agent
Proxies, which use Credentials for Logins to switch execution context before
running the Operating System step of DTSRUN. If that works for you, you can
run your DTS Packages without any user names or passwords in the command
line. Much more secure.
RLF
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:C777E793-E904-4D38-A361-E642C056AF7A@.microsoft.com...
> Tibor/Russell,
> I should have been clearer in my earlier post. I can create a job for each
> one of the DTS packages with DTSRUN. However, I dont know how to generate
> the
> encrypted details of the package name, login name, Password ... etc.
> It is against our SOX policy to include password in open text.
> THis example is for a different DTS on a different server
> altogether.[DTSRun
> /~Z0xBBA31CD08C42214454A70882A4D929948FA0915B8AA465A7BEF16CCA4AC7639EB256D85DB6780227228D2D92A3FF92D1AAC3354C13AF4CCF5704F24B8EA95C5C95E33BE0EAF73A5ED7C9F1D9AA391B364F64B7473F9E27823B3EF142CE09CF1F1BF7E08A031026BC2F7E03E544C824E13C2584B3C7197F19A12371
> ]
> Thanks,
> rgn
> "Russell Fields" wrote:
>> rgn,
>> Apparently, since DTS is being deprecated the scheduling function did not
>> move over with the legacy support.
>> However, creating your own job is pretty easy, especially if you examine
>> the
>> job definitions created for you on your SQL Server 2000 server. In fact,
>> you
>> may be able to simply script out the jobs from SQL Server 2000 and run
>> those
>> scripts on your 2005 SQL Server.
>> Some adjustements may be necessary. For example, if the same logins used
>> for the jobs on 2000 do not exist on your new 2005 server, you will need
>> to
>> adjust the scripts to point to the logins you wish to use.
>> RLF
>>
>> "rgn" <rgn@.discussions.microsoft.com> wrote in message
>> news:5B08D6C7-9A39-49AF-8EA5-532627D5E310@.microsoft.com...
>> > All,
>> >
>> > I installed SQLServer 2005 on a new box and installed the DTS Runtime
>> > utility as well so that the DTS packages run in the 2005 environment.
>> >
>> > I imported all the DTS packages but I dont know how to schedule each of
>> > them. Would anyone know. In SQLServer 2000, "Schedule Package" menu on
>> > a
>> > DTS
>> > package would create and schedule a job. I'm not sure how to do it here
>> > other
>> > than creating the job manually.
>> >
>> > Thanks,
>> > rgn
>> >
>> >
>>sql

Scheduling an automatic backup

I have only started using SQL Server 2005 Express Edition recently.I also installed Management Studio Express version to manage the database. While testing the database I had came across some question and they are:

Can I upgrade from SQL Server 2005 Express to Workgroup/Standard/Enterprise without loosing the database and its contents. While I backed up the database, I was permitted to backup only on to my local drive even though I have administrative rights on the system. It would be easy if I could backup to a network drive directly.My question is:Is it possible to backup directly to a network drive rather than backing the database to a local drive and then "copy and paste" it to a network drive.
Windows Scheduler: I have scheduled a daily backup of the database by midnight. I have also written the script to run the backup. But every time the scheduler starts the backup, it asks for the database password. This halts the backup until I come back in the morning and manually enter the password. My question is: is it possible to run an automatic database backup using Windows Scheduler? I understand that there are 2 types of backups: full and differential backups. My question is: Is it possible to dynamically allocate different names automatically to consecutive backups so that the previous backups are not over written

Thanks for your patience and time.

regards,
Berly Sam

1. Yes

2. SQL Server only allows backups to local drives or LUNs. There are third party backup tools that will backup to a network drive/share. I usually have a job step that 'moves' the files to a network share after the backup is complete.

3.Yes. I assume that you are using SQLCmd.exe. Use on of the following command line arguements (switches): -U login_id [ -P password ] } OR –E trusted connection. For addtional information, see Books Online, Topic: "SQLCmd Utility'. If necessary, you can download a copy of Books Online here.

4. Since you would need two separate Jobs scheduled, one for FULL backup and one for DIFFERENTIAL, then it seems that you would not have any issue having two different names. And yes, you can dynamically create your file names.

|||Thank you Mr. Rowland for replying.
But in the question of Back ups: i need to create different differential backups which take different names dynamically. Is it possible to schedule differential backups without over writing the previous backup.

Thank you for your time and patience
Regards
Berly Sam
|||

They will not 'take' names.

You will have to dynamically, and in your script/code, create the names and provide them to the backup command.

|||I was working over the backup case but was unable to come up with a solution as to how i can automatically backup the database while dynamically allocating names to consecutive differential backups. Just a reminder, the database I am using is SQL Server 2005 Express edition.

The script that is used to run the backup is
BACKUP DATABASE [DatabaseName] TO DISK = N'C:\BackUp\ST_BKUP_14_06_07'WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'DatabaseName-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

I have also run the SQLCMD utility.
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -S [SERVER] -U backupadmin -P [PASSWORD] -i B:\ST_BACKUP\sqlscript.sql

What else must i do so that i get an automatic backup without being asked to login for every scheduled backup.Since the 'login' makes it compulsory for me to be on the system while the backup takes place. Is there any alteration i must make so that different names are alloted to consecutive backups thus not overwriting an old backup.|||

Before the part of the script that executes the BACKUP command, create a variable with the name you wish to use.

Code Snippet

DECLARE @.MyBackupName nvarchar(250)

SET @.MyBackupName = 'C:\BackUp\ST_BKUP_' + convert( varchar(10), getdate(), 112 ) + '.BAK'

BACKUP DATABASE [DatabaseName] TO DISK = @.MyBackupName
WITH DIFFERENTIAL ,
NOFORMAT,
NOINIT,
NAME = N'DatabaseName-Differential Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10

Every time this runs, the file name will be in the form: ST_BKUP_20070614.BAK, with the date changing every day.

|||

Very cool.

So, for addition information I have a question:

1-Can I create a Stored Procedure with this code and then run it from a command line (cmd) ?

2-Can I to use the osql.exe to run this command or run the Stored Procedure created with this code ?

Tks,

Mura

|||

1. By using OSQL or SQLCmd.

2. Yes, OSQL.exe with SQL 2000, SQLCmd.exe with SQL 2005.

You can use the Windows Scheduler service to automate this.

|||Thank you Mr. Rowland for replying.
Your posts have helped me solve my problem of dynamic name allocation to my backups.
Although I could solve that, the scheduled backup does not execute at the specified time and will be halted till I manually enter the login information. My question is how can i remove the login option that i have to fill-in every time a scheduled backup script is run.

This would complete my question of how an automatic backup is done in SQL Server.
Thank you for your time and patience

Regards
Berly Sam
|||

This command line prompts the user for a Server and password.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -S [SERVER] -U backupadmin -P [PASSWORD] -i B:\ST_BACKUP\sqlscript.sql

I suggest that you put in the ServerName in place of [SERVER], and that you use -E instead of: -U backupadmin -P [PASSWORD]. So my suggested command line would be:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -S MyServerName -E -i B:\ST_BACKUP\sqlscript.sql

|||Tkx guy,|||Thank you Mr. Rowland,
I could solve my problem. I really appreciate the time and energy you invested to help me with my issue.Once again thank you
Regards
Berly Sam
|||When I executed this code, it works fine, but the thing is when I tried to change the style in the convert function its throwing an error. The dynamic file which which is generated for each backup should have the date and also the time for me . Can anyone help me on this.
|||

You have probably selected a time format that contains characters unacceptable in a path/filename.

You may prefer to do something more like this:

Code Snippet

DECLARE @.MyBackupName nvarchar(250)

SET @.MyBackupName = 'C:\BackUp\ST_BKUP_'
SET @.MyBackupName = @.MyBackupName + convert( varchar(10), getdate(), 112 ) + '_'
SET @.MyBackupName = @.MyBackupName + replace( convert( varchar(5), getdate(), 108 ), ':', '' )
SET @.MyBackupName = @.MyBackupName + '.BAK'

SELECT @.MyBackupName

--
C:\BackUp\ST_BKUP_20070618_2124.BAK

Schedule DTS Package

I have MS SQL installed on my workstation at work. I am trying to use DTS to export data from our local network that uses a Pervasive DB to our web server that is hosted with another company.

If I go in and manually execute the DTS package from my workstation, it send the data to the web server.

If I try to schedule the DTS Package to automatically send the data, it fails. SQL Server Agent is running on my workstation and on the web server.

Is what I am trying to do possible? What am I doing wrong?This is a very frequent problem and it is related to permissions.

This is a KB article that describes the problem and the solution:INF: How to Run a DTS Package as a Scheduled Job

Terri

Schedule a Job - Access denied

Greetings,
We have a NT4.0 SP6a server with SQLServer 7.0 SP3 installed. We have a job
that writes data to a text file. This job works fine either manually or
scheduled when writing to a local hard drive. However when writing to the
network the job will work when run manually but not when run scheduled.
I thought the problem has to do with the scheduler not having rights on the
network. The scheduler runs from the system account. When I tried to change
it to a network account I got this message, "Service Schedule could not be
configured to run under a shared process. If computer xxxx in not a Windows
2000 computer, service which run in a shared process must run under the
system account."
Error 1057: the account name is invalid or does not exist, or the password
is invalid for the account name specified.
Any idea how I can get this schedule job to write to the network?
Jim
Jim,
Which service did you change? You need to change the account of the
SQLServerAgent service to use a domain account that has rights to your
network resource.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602X.html
"Jim" wrote:

> Greetings,
> We have a NT4.0 SP6a server with SQLServer 7.0 SP3 installed. We have a job
> that writes data to a text file. This job works fine either manually or
> scheduled when writing to a local hard drive. However when writing to the
> network the job will work when run manually but not when run scheduled.
> I thought the problem has to do with the scheduler not having rights on the
> network. The scheduler runs from the system account. When I tried to change
> it to a network account I got this message, "Service Schedule could not be
> configured to run under a shared process. If computer xxxx in not a Windows
> 2000 computer, service which run in a shared process must run under the
> system account."
> Error 1057: the account name is invalid or does not exist, or the password
> is invalid for the account name specified.
> Any idea how I can get this schedule job to write to the network?
> Jim
>
>

Schedule a Job - Access denied

Greetings,
We have a NT4.0 SP6a server with SQLServer 7.0 SP3 installed. We have a job
that writes data to a text file. This job works fine either manually or
scheduled when writing to a local hard drive. However when writing to the
network the job will work when run manually but not when run scheduled.
I thought the problem has to do with the scheduler not having rights on the
network. The scheduler runs from the system account. When I tried to change
it to a network account I got this message, "Service Schedule could not be
configured to run under a shared process. If computer xxxx in not a Windows
2000 computer, service which run in a shared process must run under the
system account."
Error 1057: the account name is invalid or does not exist, or the password
is invalid for the account name specified.
Any idea how I can get this schedule job to write to the network?
JimJim,
Which service did you change? You need to change the account of the
SQLServerAgent service to use a domain account that has rights to your
network resource.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602_.html
"Jim" wrote:

> Greetings,
> We have a NT4.0 SP6a server with SQLServer 7.0 SP3 installed. We have a jo
b
> that writes data to a text file. This job works fine either manually or
> scheduled when writing to a local hard drive. However when writing to the
> network the job will work when run manually but not when run scheduled.
> I thought the problem has to do with the scheduler not having rights on th
e
> network. The scheduler runs from the system account. When I tried to chang
e
> it to a network account I got this message, "Service Schedule could not be
> configured to run under a shared process. If computer xxxx in not a Window
s
> 2000 computer, service which run in a shared process must run under the
> system account."
> Error 1057: the account name is invalid or does not exist, or the password
> is invalid for the account name specified.
> Any idea how I can get this schedule job to write to the network?
> Jim
>
>

Schedule a Job - Access denied

Greetings,
We have a NT4.0 SP6a server with SQLServer 7.0 SP3 installed. We have a job
that writes data to a text file. This job works fine either manually or
scheduled when writing to a local hard drive. However when writing to the
network the job will work when run manually but not when run scheduled.
I thought the problem has to do with the scheduler not having rights on the
network. The scheduler runs from the system account. When I tried to change
it to a network account I got this message, "Service Schedule could not be
configured to run under a shared process. If computer xxxx in not a Windows
2000 computer, service which run in a shared process must run under the
system account."
Error 1057: the account name is invalid or does not exist, or the password
is invalid for the account name specified.
Any idea how I can get this schedule job to write to the network?
JimJim,
Which service did you change? You need to change the account of the
SQLServerAgent service to use a domain account that has rights to your
network resource.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602­.html
"Jim" wrote:
> Greetings,
> We have a NT4.0 SP6a server with SQLServer 7.0 SP3 installed. We have a job
> that writes data to a text file. This job works fine either manually or
> scheduled when writing to a local hard drive. However when writing to the
> network the job will work when run manually but not when run scheduled.
> I thought the problem has to do with the scheduler not having rights on the
> network. The scheduler runs from the system account. When I tried to change
> it to a network account I got this message, "Service Schedule could not be
> configured to run under a shared process. If computer xxxx in not a Windows
> 2000 computer, service which run in a shared process must run under the
> system account."
> Error 1057: the account name is invalid or does not exist, or the password
> is invalid for the account name specified.
> Any idea how I can get this schedule job to write to the network?
> Jim
>
>