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.

Job Schedualing

Hello,

I m using Sql server 2005 express and sql management studio express.

Just wonder if we have sql agent for them so I can do job scheduling.

Otherwise.. do we have sql agent for full edition of sql 2005.

Due to I m really new to asp.net and sql server, how do we usually do schedule work?

Thanx in advance,

Regards,

Elton

The most common way is to use the built in windows Scheduler.... but I found this solution on Code Project.

Job running status

In SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?

Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.

Thanks a lot!

Penn

Performing query directly to system tables is not recommended, as the schema of system tables/views may change even between different builds. Instead, you can use some system procedures to get detailed information about agent jobs, for example:

exec msdb..sp_help_job @.job_name='Expired subscription clean up'

So you can call such system procedures from your code using SqlDataAdapter and then fill the result sets into DataSet for future using. For more information about system procedures, please refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

Job generates login error

I have a package which will run in Management Studio. The package is saved in MSDB. When I try to set up a job, the step errors out with what appears to be a login error. I am specifying windows authentication in the job and also used that when creating the package.

Paul,

the reason is security, probably you created the package with a different account than the sql server agent is running.

Solution:

you can create the package with the agent account,

or in the package -> properties -> security -> protectionlevel -> choose encryptsensitivewithpassword en fill in a password.

greetz

http://www.ns-consulting.be

|||

the package was originally created in management studio using the administrator account. do I need to set up the SQL Agent as a user? Is there a book on SQL Server 2005 which walks you through creating a sample solution from start to finish? I have the Administrator's Consultant and 'Changing the Paradigm' books. SQL Server 2005 is entirely new to me; I was a dba for Sql Server 2000 before this.

Thanks for your help!

Paul

|||

the package was recreated - I changed the encription option to 'Rely on server . . .' and changed the source path from X:\Directory\FileName to \\HostServerName\Directory\FileName.

problem is solved.

Job generates login error

I have a package which will run in Management Studio. The package is saved in MSDB. When I try to set up a job, the step errors out with what appears to be a login error. I am specifying windows authentication in the job and also used that when creating the package.

Paul,

the reason is security, probably you created the package with a different account than the sql server agent is running.

Solution:

you can create the package with the agent account,

or in the package -> properties -> security -> protectionlevel -> choose encryptsensitivewithpassword en fill in a password.

greetz

http://www.ns-consulting.be

|||

the package was originally created in management studio using the administrator account. do I need to set up the SQL Agent as a user? Is there a book on SQL Server 2005 which walks you through creating a sample solution from start to finish? I have the Administrator's Consultant and 'Changing the Paradigm' books. SQL Server 2005 is entirely new to me; I was a dba for Sql Server 2000 before this.

Thanks for your help!

Paul

|||

the package was recreated - I changed the encription option to 'Rely on server . . .' and changed the source path from X:\Directory\FileName to \\HostServerName\Directory\FileName.

problem is solved.

Job generates login error

I have a package which will run in Management Studio. The package is saved in MSDB. When I try to set up a job, the step errors out with what appears to be a login error. I am specifying windows authentication in the job and also used that when creating the package.

Paul,

the reason is security, probably you created the package with a different account than the sql server agent is running.

Solution:

you can create the package with the agent account,

or in the package -> properties -> security -> protectionlevel -> choose encryptsensitivewithpassword en fill in a password.

greetz

http://www.ns-consulting.be

|||

the package was originally created in management studio using the administrator account. do I need to set up the SQL Agent as a user? Is there a book on SQL Server 2005 which walks you through creating a sample solution from start to finish? I have the Administrator's Consultant and 'Changing the Paradigm' books. SQL Server 2005 is entirely new to me; I was a dba for Sql Server 2000 before this.

Thanks for your help!

Paul

|||

the package was recreated - I changed the encription option to 'Rely on server . . .' and changed the source path from X:\Directory\FileName to \\HostServerName\Directory\FileName.

problem is solved.

Job fails but package will run from MSDB

I created a package for importing data from a flat file to an existing table in SQL Server 2005 using Management Studio and the administrator account working directly on the server through a remote login. I saved the package to MSDB.

I can see the package listed when I connect to Integration Services through Management Studio and expand the MSDB folder. In that location I have the option to run the package and it will run successfully. However, I cannot edit the package.

When I try to create a scheduled job using Sql Server Agent, where the first step is to run the package using the administrator login, the execution fails, and the job history will only tell me that the step failed. The Sql Server Agent log shows that the job started and completed. No error is shown. The Administrative Tools Event Viewer Application log shows only that the job started and failed. Security log shows no errors. System log shows no errors.

What am I doing wrong? How can I edit and schedule this package?

You cannot edit packages through SSMS, that is by design. BIDS is the design environment for SSIS, in fact for all the BI stuff.

When you schedule the package you are potentially changing the location and context compared to a right-click execute. It runs under the SQL Server agent service account, although may be using a SQL Agent Proxy, check the job. It also runs on the SQL Server machine itself.

The best thing to do its supply a step log file, or log to table. This needs to be set at a step level, and normally gives more than the job history. I would also change to CmdExec and DTEXEC. See link for an example of why-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||I don't have a proxy set up - do I need to do that? Also, what are the steps to set up the log file you mentioned?|||

If your job is owned by a sysadmin, and you don't have any special security requirements, then you do not need a proxy. It will run under the context of the SQL Server Agent service account.

To set the job step logging options, open the job, and then open the step. Select the Advanced tab, and set some options. I personally find output log file is the simplest to use and manage.

|||If part of the job is to create a destination table in the database, that part of the job will work correctly, but the data will not be imported from the source text file. I think the problem is that the SQL Server Agent doesn't have permission or security set somewhere so that the agent can read the source file directory. How can I fix this issue?|||

the issue was solved by changing encription option and changing the file reference from a drive-specific source (X:\ImportData\FileName to \\HostServerName\Directory\FileName.

Job fails but package will run from MSDB

I created a package for importing data from a flat file to an existing table in SQL Server 2005 using Management Studio and the administrator account working directly on the server through a remote login. I saved the package to MSDB.

I can see the package listed when I connect to Integration Services through Management Studio and expand the MSDB folder. In that location I have the option to run the package and it will run successfully. However, I cannot edit the package.

When I try to create a scheduled job using Sql Server Agent, where the first step is to run the package using the administrator login, the execution fails, and the job history will only tell me that the step failed. The Sql Server Agent log shows that the job started and completed. No error is shown. The Administrative Tools Event Viewer Application log shows only that the job started and failed. Security log shows no errors. System log shows no errors.

What am I doing wrong? How can I edit and schedule this package?

You cannot edit packages through SSMS, that is by design. BIDS is the design environment for SSIS, in fact for all the BI stuff.

When you schedule the package you are potentially changing the location and context compared to a right-click execute. It runs under the SQL Server agent service account, although may be using a SQL Agent Proxy, check the job. It also runs on the SQL Server machine itself.

The best thing to do its supply a step log file, or log to table. This needs to be set at a step level, and normally gives more than the job history. I would also change to CmdExec and DTEXEC. See link for an example of why-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||I don't have a proxy set up - do I need to do that? Also, what are the steps to set up the log file you mentioned?|||

If your job is owned by a sysadmin, and you don't have any special security requirements, then you do not need a proxy. It will run under the context of the SQL Server Agent service account.

To set the job step logging options, open the job, and then open the step. Select the Advanced tab, and set some options. I personally find output log file is the simplest to use and manage.

|||If part of the job is to create a destination table in the database, that part of the job will work correctly, but the data will not be imported from the source text file. I think the problem is that the SQL Server Agent doesn't have permission or security set somewhere so that the agent can read the source file directory. How can I fix this issue?|||

the issue was solved by changing encription option and changing the file reference from a drive-specific source (X:\ImportData\FileName to \\HostServerName\Directory\FileName.

Job Does Not Execute Correctly

Hi everyone,
I have a management job which runs every minute automatically. However recently I discovered that the result was not executed correctly even the job history tells me it runs successfully. On the other hand, the result was executed correctly (i.e. what I expect) when I try to start the job manually.
Any idea what issue does it related to? I doubt if it is related to any security context but cannot prove it yet. Thanks for any idea.Hi,
As you say u execute job every 1 minute, What does the job
do and what is the expected time period of the job
completion.
Regards
Thirumal
www.thirumal.com
>--Original Message--
>Hi everyone,
>I have a management job which runs every minute
automatically. However recently I discovered that the
result was not executed correctly even the job history
tells me it runs successfully. On the other hand, the
result was executed correctly (i.e. what I expect) when I
try to start the job manually.
>Any idea what issue does it related to? I doubt if it is
related to any security context but cannot prove it yet.
Thanks for any idea.
>.
>|||Thirumal,
Thanks for your reply. The job executes within just few seconds, wont' be too long.
The job retreives several records from tables and then insert records into another table based on a if-else condition.
The code is like this:
DECLARE @.thisMin varchar(25)
DECLARE @.table_no varchar(2), @.table_tmp varchar(2)
SET @.thisMin = GETDATE()
SET @.table_no = DATEDIFF(dd, ''01/01/01'', @.thisMin) % 36
SET @.table_tmp = DATEDIFF(dd, ''01/01/01'', DATEADD(dd, -1, @.thisMin)) % 36
EXEC(''
DECLARE temp_cur CURSOR FOR
SELECT DISTINCT alertId FROM z_EventHistoryD''+@.table_no+'' WHERE
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0 OR
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
GROUP BY alertId, severitySortOrder
HAVING COUNT(alertId) = 1
DECLARE @.alertId int, @.thisSSO int, @.lastSSO int, @.auditTime datetime
OPEN temp_cur
FETCH NEXT FROM temp_cur INTO @.alertId
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.thisSSO = NULL
SET @.lastSSO = NULL
SET @.auditTime = NULL
IF DATEPART(hh, '''+@.thisMin+''')=0 AND DATEPART(mi, '''+@.thisMin+''')=0
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_tmp+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
ELSE
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
SELECT @.thisSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0
IF (@.lastSSO IS NULL)
--PRINT ''''-- NEW EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, NULL, @.thisSSO, 1, @.auditTime)
ELSE
IF (@.thisSSO IS NULL)
--PRINT ''''-- ENDING EVENT''''
INSERT INTO alertAudit
SELECT alertId, @.lastSSO, NULL, -1, clearTime FROM alertHistory WHERE alertId = @.alertId
ELSE
IF (@.thisSSO <> @.lastSSO)
--PRINT ''''-- CHANGE SEVERITY EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, @.lastSSO, @.thisSSO, 2, @.auditTime)
ELSE
IF (@.thisSSO = @.lastSSO)
PRINT ''''-- UNCHANGED SEVERITY''''
ELSE
PRINT ''''-- UNKNOWN ERROR''''
/*--DEBUG
PRINT @.alertId
PRINT @.auditTime
PRINT @.lastSSO
PRINT @.thisSSO
--DEBUG*/
FETCH NEXT FROM temp_cur INTO @.alertId
END
CLOSE temp_cur
DEALLOCATE temp_cur
'')
Thanks again
"Thirumal" wrote:
> Hi,
> As you say u execute job every 1 minute, What does the job
> do and what is the expected time period of the job
> completion.
> Regards
> Thirumal
> www.thirumal.com
> >--Original Message--
> >Hi everyone,
> >
> >I have a management job which runs every minute
> automatically. However recently I discovered that the
> result was not executed correctly even the job history
> tells me it runs successfully. On the other hand, the
> result was executed correctly (i.e. what I expect) when I
> try to start the job manually.
> >
> >Any idea what issue does it related to? I doubt if it is
> related to any security context but cannot prove it yet.
> Thanks for any idea.
> >.
> >
>