job to run a remote process

I have a question regard the scheduling of a database job. I want to run a job that executes the following steps:

1) backup an entire database from DB server A

2) restore the database on DB Sever B

3) run a non database process on Server C (with xp_cmdshell)

Step 1 and 2 are easy enough but how do I start a process on an remote machine (Step 3)?

In my case the process in step 3 is will execute a Cognos DecisionStream job (rundsjob.exe)

My environment is

SQL Server 2000.

Windows Server 2003

Paul

If you are trying to do something like run a batch file remotely, you may want to look at WMIC.

Prior to Windows 2003, you'd use RCMD. This link might help you get started:

http://www.microsoft.com/technet/prodtechnol/windows2000serv/maintain/featusability/wmic.mspx

-Sue

Job that runs past the next scheduled start time

Hello group
I have a problem that I'm considering if the SQL Server Agent and Job
scheduling can help me solve.
I need to check some status very often (approximately every 30. sec.). I
have learned that you can get the job sceduling to start this often, so that
should not be a problem.
Normaly the job will just check this status and then stop.
Once in a while the status will signal that further processing should take
place. This processing may last more than 30 seconds, and therefore last
beyond the next start time of the job.
Is this a problem?
Will I have the same job running multiple times? That is not good.
When will the next job then start? Will it start 30 sec. after the long
running job completed, or will jobs queue op while the long running job is
processing so that I will get multiple starts when the first job completes?
I hope that you can understand my issue.
Regards
Anders> Will I have the same job running multiple times?
No. Agent is simple in this regard. If the job is already running when it is supposed to start, then
Agent will *not* start another instance of that job.
> When will the next job then start? Will it start 30 sec. after the long running job completed, or
> will jobs queue op while the long running job is processing so that I will get multiple starts
> when the first job completes?
Neither. It will start when it is supposed to. Say for simplicity of this example that you want to
start it every minute. Now, you have one occasion where it starts at 13:00:00 and it runs for 110
seconds. The job will not start at 13:01:00 since it is already running. As I said, this occasion
takes 110 seconds so it finishes at 13:01:50. Next time it is supposed to start is 13:02:00, and now
it isn't running so Agent will start it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Anders K. Olsen" <akol_dk@.hotmail.com> wrote in message
news:OM4PQuc%23HHA.700@.TK2MSFTNGP05.phx.gbl...
> Hello group
> I have a problem that I'm considering if the SQL Server Agent and Job scheduling can help me
> solve.
> I need to check some status very often (approximately every 30. sec.). I have learned that you can
> get the job sceduling to start this often, so that should not be a problem.
> Normaly the job will just check this status and then stop.
> Once in a while the status will signal that further processing should take place. This processing
> may last more than 30 seconds, and therefore last beyond the next start time of the job.
> Is this a problem?
> Will I have the same job running multiple times? That is not good.
> When will the next job then start? Will it start 30 sec. after the long running job completed, or
> will jobs queue op while the long running job is processing so that I will get multiple starts
> when the first job completes?
> I hope that you can understand my issue.
> Regards
> Anders
>|||Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23GdwCgd%23HHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Will I have the same job running multiple times?
> No. Agent is simple in this regard. If the job is already running when it
> is supposed to start, then Agent will *not* start another instance of that
> job.
Sometimes simple is good :-)
>> When will the next job then start? Will it start 30 sec. after the long
>> running job completed, or will jobs queue op while the long running job
>> is processing so that I will get multiple starts when the first job
>> completes?
> Neither. It will start when it is supposed to. Say for simplicity of this
> example that you want to start it every minute. Now, you have one occasion
> where it starts at 13:00:00 and it runs for 110 seconds. The job will not
> start at 13:01:00 since it is already running. As I said, this occasion
> takes 110 seconds so it finishes at 13:01:50. Next time it is supposed to
> start is 13:02:00, and now it isn't running so Agent will start it.
This is fine for our purposes, so we will continue along this line.
Thank you for your help.
Regards
Anderssql

Job Scheduling question

I think I know the answer to my question, but I thought I would ask to be
sure that I'm correct. I have a need to run a job every 30 seconds, but the
Job Scheduler only gives the option of a frequency of 1 minute. Is there
anyway to set this up that way?Try this link:
http://www.sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
"Big Ern" wrote:
> I think I know the answer to my question, but I thought I would ask to be
> sure that I'm correct. I have a need to run a job every 30 seconds, but the
> Job Scheduler only gives the option of a frequency of 1 minute. Is there
> anyway to set this up that way?
>

Job Scheduling question

Try this link:
http://www.sqldev.net/sqlagent/SQLA...gJobsInSecs.htm
"Big Ern" wrote:

> I think I know the answer to my question, but I thought I would ask to be
> sure that I'm correct. I have a need to run a job every 30 seconds, but th
e
> Job Scheduler only gives the option of a frequency of 1 minute. Is there
> anyway to set this up that way?
>I think I know the answer to my question, but I thought I would ask to be
sure that I'm correct. I have a need to run a job every 30 seconds, but the
Job Scheduler only gives the option of a frequency of 1 minute. Is there
anyway to set this up that way?|||Try this link:
http://www.sqldev.net/sqlagent/SQLA...gJobsInSecs.htm
"Big Ern" wrote:

> I think I know the answer to my question, but I thought I would ask to be
> sure that I'm correct. I have a need to run a job every 30 seconds, but th
e
> Job Scheduler only gives the option of a frequency of 1 minute. Is there
> anyway to set this up that way?
>

Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"


I've also done the same on the Step properties under 'Logging'


In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio


The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."


By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.

Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"
I've also done the same on the Step properties under 'Logging'
In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio
The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."
By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.sql

Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"


I've also done the same on the Step properties under 'Logging'


In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio


The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."


By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.

Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"


I've also done the same on the Step properties under 'Logging'


In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio


The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."


By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.

job scheduling

Hello,
Is it possible to schedule the sql agent to run a job every so many
seconds? I may have a need to schedule a job at a certain interval of
seconds and the sql agent gui scheduler seems to only allow scheduling
by the minute or more.
Thanks,
Greg
http://sqldev.net/sqlagent/SQLAgentR...JobsInSecs.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg" <gjackson@.thq.com> wrote in message news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg
|||If you are going to have something run every so many seconds you might
consider having a never ending stored procedure that uses the WAITFOR DELAY
command to control your running of some commands every few seconds.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg" <gjackson@.thq.com> wrote in message
news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg

Job scheduling

Hi,

I have two jobs scheduled on the Agent. Each job has ten steps. The next step is executed only if the previous suceeds.

I want Job 2 to execute only if Job 1 suceeds. How do I schedule that?

I was thinking that I can have the ten steps of Job 2 as steps (11 to 20) of Job 1 with each step executing after the previous one succeeds. If I do that then I can get rid of Job 2.

Let me know what approach you would advise.

Thanksthats what i would do|||I think that is the only option. I am not aware of any other way of controlling the job sequence.|||You don't have to merge two jobs into one. At the end of job1, on step 10 choose "go to next step if succeeds" and then add one more step with command:

exec sp_start_job @.job_name = job2

Job Scheduling

How do I schedule the jobs to run in sequence? I have created a package to
call the sql server agent jobs in sequence but looks like all the jobs are
running at the same time, not waiting for the other jobs to complete. I did
put the constriants not to run the second job until the first job is
complete. But still it is not behaving as instructed.
Any thoughts,
Thanks,
MariJobs run asynchronously and independently of each other.
You need to make ONE job and call each "job" as an individual step. That
way, "job"/step 2 will not start until "job"/step 1 is complete (and you can
terminate the whole sequence on an individual step failure, or you can have
individual step failures ignored and just move to the next step).
"Maria" <Maria@.discussions.microsoft.com> wrote in message
news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
> How do I schedule the jobs to run in sequence? I have created a package to
> call the sql server agent jobs in sequence but looks like all the jobs are
> running at the same time, not waiting for the other jobs to complete. I
> did
> put the constriants not to run the second job until the first job is
> complete. But still it is not behaving as instructed.
> Any thoughts,
> Thanks,
> Mari|||Thank you Aaron. But there is no option to define the "type" for sql server
Agent jobs to call the another job. I guess you know what I am talking.
When I am creating the 'job', for the step 1, there is no Agent Jobs
options. We have only IS package or AS or activeX etc., but not the agent
jobs : (
Tks
"Aaron Bertrand [SQL Server MVP]" wrote:

> Jobs run asynchronously and independently of each other.
> You need to make ONE job and call each "job" as an individual step. That
> way, "job"/step 2 will not start until "job"/step 1 is complete (and you c
an
> terminate the whole sequence on an individual step failure, or you can hav
e
> individual step failures ignored and just move to the next step).
>
> "Maria" <Maria@.discussions.microsoft.com> wrote in message
> news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
>
>|||If you are using SQL Server 2005, Service Broker can be used to guarantee
in-order execution:
http://www.microsoft.com/technet/te...lt.a
spx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Maria" <Maria@.discussions.microsoft.com> wrote in message
news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
> How do I schedule the jobs to run in sequence? I have created a package to
> call the sql server agent jobs in sequence but looks like all the jobs are
> running at the same time, not waiting for the other jobs to complete. I
> did
> put the constriants not to run the second job until the first job is
> complete. But still it is not behaving as instructed.
> Any thoughts,
> Thanks,
> Mari|||Maria wrote:
> How do I schedule the jobs to run in sequence? I have created a package to
> call the sql server agent jobs in sequence but looks like all the jobs are
> running at the same time, not waiting for the other jobs to complete. I di
d
> put the constriants not to run the second job until the first job is
> complete. But still it is not behaving as instructed.
> Any thoughts,
> Thanks,
> Mari
Are you referring to SQL Agent jobs, or tasks within a DTS package?|||It is "Execute SQL Server Agent Job Task" under Maintenance Plan Tasks for a
DTS package.
"Tracy McKibben" wrote:

> Maria wrote:
> Are you referring to SQL Agent jobs, or tasks within a DTS package?
>|||Maria wrote:
> It is "Execute SQL Server Agent Job Task" under Maintenance Plan Tasks for
a
> DTS package.
>
I'm not familiar with that one. So, you're using a DTS package to
execute Maintenance Plan tasks. Is the DTS package then going to be
scheduled to run via SQL Agent? Seems sorta bass-ackwards... Am I
completely not understanding what you're trying to accomplish?|||I think your understanding is right. But looks like that doesn't work. When
I
created the package to run all these jobs in a sequence, all thejobs are
running at the same time though I put constraints on it.
Hope this is clear.
thanks
"Tracy McKibben" wrote:

> Maria wrote:
> I'm not familiar with that one. So, you're using a DTS package to
> execute Maintenance Plan tasks. Is the DTS package then going to be
> scheduled to run via SQL Agent? Seems sorta bass-ackwards... Am I
> completely not understanding what you're trying to accomplish?
>|||Hi Roger,
I never used the Broker Service. Could you give some more details how to use
this service to schedule batch jobs.
Thanks
"Roger Wolter[MSFT]" wrote:

> If you are using SQL Server 2005, Service Broker can be used to guarantee
> in-order execution:
> http://www.microsoft.com/technet/te...lt
.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Maria" <Maria@.discussions.microsoft.com> wrote in message
> news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
>
>|||Maria wrote:
> I think your understanding is right. But looks like that doesn't work. Whe
n I
> created the package to run all these jobs in a sequence, all thejobs are
> running at the same time though I put constraints on it.
>
Ok... Well, that leads me to ask, why not just let the maintenance plan
schedule the jobs in SQL Agent like it's supposed to, instead of
wrapping them in a DTS package like this?
I'll go one step further, and suggest that you dump the maintenance plan
altogether, and write your own processes to do what the maintenance plan
offers you. Write your own BACKUP job, write your own index maintenance
routine, don't shrink your databases. You'll learn alot more about your
environment by doing so...sql

job scheduling

Hello,
Is it possible to schedule the sql agent to run a job every so many
seconds? I may have a need to schedule a job at a certain interval of
seconds and the sql agent gui scheduler seems to only allow scheduling
by the minute or more.
Thanks,
Greghttp://sqldev.net/sqlagent/SQLAgent...gJobsInSecs.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg" <gjackson@.thq.com> wrote in message news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.
4ax.com..
.
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg|||If you are going to have something run every so many seconds you might
consider having a never ending stored procedure that uses the WAITFOR DELAY
command to control your running of some commands every few seconds.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg" <gjackson@.thq.com> wrote in message
news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.
4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg

JOB Scheduling

I create my first job schedule to remove some unused data at mid-night.
As I want to do some modification in that job schedule, I don't know how to
retrieve that Job ,
Please help. and thanksIn Enterprise Manager, go into Management->SQL Server Agent->Jobs.
Double-click on the job.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:uZvwhXzaGHA.4424@.TK2MSFTNGP02.phx.gbl...
I create my first job schedule to remove some unused data at mid-night.
As I want to do some modification in that job schedule, I don't know how to
retrieve that Job ,
Please help. and thanks

Job scheduling

As opposed to running a job every X minutes, is there a way to schedule a
job to re-start X minutes after it finishes successfully ?
Is this the correct newsgroup for this question ?Untested, but it should work:
schedule the job to run every 1 minute
Add an additional step to the end of the job that will only execute if =
the steps ahead of the job complete successfully that does a WAITFOR =
DELAY.
--=20
Keith
"rob" <rwc1960@.bellsouth.net> wrote in message =
news:XBg6c.47748$xL3.43022@.bignews1.bellsouth.net...
> As opposed to running a job every X minutes, is there a way to =
schedule a
> job to re-start X minutes after it finishes successfully ?
>=20
> Is this the correct newsgroup for this question ?
>=20
>=20
>|||Another option could be to end each job with a jobstep which executes
sp_add_jobschedule to schedule the job again, based on the desired interval.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eobvl4ODEHA.3584@.TK2MSFTNGP11.phx.gbl...
Untested, but it should work:
schedule the job to run every 1 minute
Add an additional step to the end of the job that will only execute if the
steps ahead of the job complete successfully that does a WAITFOR DELAY.
Keith
"rob" <rwc1960@.bellsouth.net> wrote in message
news:XBg6c.47748$xL3.43022@.bignews1.bellsouth.net...
> As opposed to running a job every X minutes, is there a way to schedule a
> job to re-start X minutes after it finishes successfully ?
> Is this the correct newsgroup for this question ?
>
>

job scheduling

Hello,
Is it possible to schedule the sql agent to run a job every so many
seconds? I may have a need to schedule a job at a certain interval of
seconds and the sql agent gui scheduler seems to only allow scheduling
by the minute or more.
Thanks,
Greghttp://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg" <gjackson@.thq.com> wrote in message news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg|||If you are going to have something run every so many seconds you might
consider having a never ending stored procedure that uses the WAITFOR DELAY
command to control your running of some commands every few seconds.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg" <gjackson@.thq.com> wrote in message
news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg

JOB Scheduling

I create my first job schedule to remove some unused data at mid-night.
As I want to do some modification in that job schedule, I don't know how to
retrieve that Job ,
Please help. and thanksIn Enterprise Manager, go into Management->SQL Server Agent->Jobs.
Double-click on the job.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:uZvwhXzaGHA.4424@.TK2MSFTNGP02.phx.gbl...
I create my first job schedule to remove some unused data at mid-night.
As I want to do some modification in that job schedule, I don't know how to
retrieve that Job ,
Please help. and thankssql

Job Schedules Not Schedu;ling

I can execute the following file to create jobs/ job
steps/ and related job schedules in SQL Analyzer ...
however the job is never scheduled. I can not identify
anything that is incorrect.
The commands process properly... and I can browse the
resulting msdb table entries in sys_jobs, sys_jobsteps,
and sys_jobschedules... however nothing ever gets
scheduled.
Please provide assistance where appropriate.
USE MSDB
EXEC sp_add_job
@.job_name='MailPointDBEmailFTCTPopulation',
@.enabled=1,
@.notify_level_eventlog=3
EXEC sp_add_jobstep
@.job_name='MailPointDBEmailFTCTPopulation',
@.step_name='EmailFTCTPopulation',
@.subsystem='TSQL',
@.command='EXEC sp_fulltext_table Email, ''update_index'''
EXEC sp_add_jobschedule
@.job_name='MailPointDBEmailFTCTPopulation',
@.name='MailPointDBEmailFTCTPopulation',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.active_start_time=183000
EXEC sp_add_job
@.job_name='MailPointDBDocumentFTCTPopulation',
@.enabled=1,
@.notify_level_eventlog=3
EXEC sp_add_jobstep
@.job_name='MailPointDBDocumentFTCTPopulation',
@.step_name='DocumentFTCTPopulation',
@.subsystem='TSQL',
@.command='EXEC sp_fulltext_table
Document, ''update_index'''
EXEC sp_add_jobschedule
@.job_name='MailPointDBDocumentFTCTPopulation',
@.name='MailPointDBDocumentFTCTPopulation',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.active_start_time=183000
GOis agent running on your server ?
jobi
"Philip" <plippard@.nc.rr.com> wrote in message
news:154f01c35247$39bb07a0$a001280a@.phx.gbl...
> I can execute the following file to create jobs/ job
> steps/ and related job schedules in SQL Analyzer ...
> however the job is never scheduled. I can not identify
> anything that is incorrect.
> The commands process properly... and I can browse the
> resulting msdb table entries in sys_jobs, sys_jobsteps,
> and sys_jobschedules... however nothing ever gets
> scheduled.
> Please provide assistance where appropriate.
> USE MSDB
> EXEC sp_add_job
> @.job_name='MailPointDBEmailFTCTPopulation',
> @.enabled=1,
> @.notify_level_eventlog=3
> EXEC sp_add_jobstep
> @.job_name='MailPointDBEmailFTCTPopulation',
> @.step_name='EmailFTCTPopulation',
> @.subsystem='TSQL',
> @.command='EXEC sp_fulltext_table Email, ''update_index'''
> EXEC sp_add_jobschedule
> @.job_name='MailPointDBEmailFTCTPopulation',
> @.name='MailPointDBEmailFTCTPopulation',
> @.enabled=1,
> @.freq_type=4,
> @.freq_interval=1,
> @.active_start_time=183000
> EXEC sp_add_job
> @.job_name='MailPointDBDocumentFTCTPopulation',
> @.enabled=1,
> @.notify_level_eventlog=3
> EXEC sp_add_jobstep
> @.job_name='MailPointDBDocumentFTCTPopulation',
> @.step_name='DocumentFTCTPopulation',
> @.subsystem='TSQL',
> @.command='EXEC sp_fulltext_table
> Document, ''update_index'''
> EXEC sp_add_jobschedule
> @.job_name='MailPointDBDocumentFTCTPopulation',
> @.name='MailPointDBDocumentFTCTPopulation',
> @.enabled=1,
> @.freq_type=4,
> @.freq_interval=1,
> @.active_start_time=183000
> GO
>

Job schedule interval limits using Management Studio

Has anybody else seen the limit Management Studio's interface puts on the scheduling of jobs? Specifically, if the units is "minutes", the frequency can't be more than 60 (and similarly, the limit for "hours" is 24).

Thanks!

-dave

Yes, we've seen that. Is there a question?|||

Heh.. sorry. I guess the question/assumption is... that's a known bug and will be fixed at some point (?).

Thanks!

|||A bug would mean that it isn't working as designed. This isn't a bug. You can only configure 60 when you select minutes for a value, because there are only 60 minutes in an hour. You can only configure 24 for hours, because there are only 24 hours in a day. I'm not sure what you're trying to do.|||

Michael,

I can easily see times to have a JOB execute every 90 minutes, for example. The Current scheduling GUI doens't allow that without using 'creative' options.

More flexibility with the scheduling GUI would be an excellent move!

|||

Arnie beat me to it, but... yes, every 90 minutes is a good example.

FWIW, I ran into this while trying to implement Quest LiteSpeed's log shipping solution. Their l/s wizard adds a job with twice the l/s interval, which I'd set to 60 minutes. (Oddly enough, their own Enterprise Console front end won't let you put in more than 100 minutes... very strange). Trying to edit that job's schedule with Management Studio gives a popup error box and does not allow entry to the schedule (in effect: "the schedule has an error, but we're not letting you in to fix it").

Finally, this is yet ANOTHER instance of regression from 2000 to 2005, in that Enterprise Manager has no such limit.

|||MSDN Product Feedback Center. Post a suggestion. Then get a bunch of people to vote on including it. Yes, you do have the ability to directly influence feature requests.