I created an agent job, and it runs fine, but I want more control over the scheduling. I need to schedule jobs to run Sunday through
Thursday at 8:00pm, skipping Friday.
How can I use the agent job interface and customize the schedule this way? Will I have to resort to a Windows Service and control the
scheduling that way?
Hi Group,
Is it possible to schedule and execute sql jobs from an asp page.
I am developing an application that requires an administration section. In this section the admin would like to change / update the schedule of the sql job and also run the jobs ad hoc. Basicall y they would like the functionality of sql agen in enterprise manager to be available from their web application.
Has anbody done this before ?
All information / solutions shall be psoted back to the group as I reckon this would be pretty useful functionality.
Thanks in advance
Tony
mangesh writes "HI Everybody,
Please help me to solve this problem. I am not getting any solution to run jobs over sql server express. it does not have sql agent service.please suggest alertnative.
Mangesh"
HI,
I am having a strange error coming in SQL server 2005.
I had a DTS package developed in SQl server 2000. Then i had migrated that DTS package to SSIS package using Execute DTS 2000 package. The conversion is successfull.
Then when i manullt execute this package it runs successfully without giving any error.
But then wheh i schedule a SQL server agent job for the same above SSIS package it fails giving me some cryptographic error as follows:
Message
Executed as user: CATOS-CGDBTUW02SYSTEM. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-02-20 23:00:00.66 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-02-20 23:00:01.00 Code: 0x00000000 Source: Execute DTS 2000 Package T... The package execution fa... The step failed.
Not sure why is this happening.
Manually running the SSIS package from Visual studio does not gives error, but the same SSIS package when is been schedule from SQL server 2005's SQL server agent job it fails giving above error.
Please help me out how to rectify whith this.
Thanks.. in advance
Regards,
Ashok
Hi All,
I am not able to see any of the jobs in the enterprise manager that are scheduled on one of our production servers. Have any body seen this type of behaviour of SQL server.. The jobs are there in the SQL as I can query sysjobs table to see there entry.. The jobs are being executed properly when they are supposed to..
Thanks
Ajay
Hello all,
we just moved our databases over from a SQL 2000 server onto a new SQL 2005 server. We had no problem migrating them over, but was wondering if there was a way to migrate the jobs over as well. We have about 20 of them and would prefer not to hav to create them all again on the new server.
Thanks in advance,
Phoenix
Hello,
I need to document all SQL Server Agent Jobs with full description of dependencies - database, table, stored procedure, etc.
Is there are a tool that I can download for that particular use???
Thank you so much
Hi Guys,
I am not sure if this is the right place to put a question on SQL Server Agent.
I have scheduled some SSIS Jobs using the SQL Server Agent, and wanted to know how can I view detailed log information on how the Job ran (particularly Failure Information).
The information in the Log File Viewer to see the job execution log doesnt seem to help much - as it only says weather the Job Failed or Succeeded.
I need to see the reason and/or the step in a job where it failed!
Can someone help me out with some insight into this....
Thanks
Pramod
I have a few jobs in sql server 2005 agent that got corrupted that Iwould like to restore.
Do they get backed up and how can I restore them?
Just wondering if anyone knew if it was possible to create a SQL Server agent job that ran once every 30 seconds. I can't enter a value below 1 minute when editing the job schedule in Enterprise Manager.
Alternatively, I can create duplicate stored procedures and set one to run on the minute and one on the half minute. Thought there might be a better alternative to this though and I'll always have to be remembering to makes changes to both sprocs.
Thanks!
Hi. I was wondering if anyone has ever had a problem where nothing seems to be wrong with the server, and SQL Server Agent is up and running, but jobs fail. We had a job run at midnight, and it was the last successful run. Every job failed after that, with an error stating "Unable to retrieve steps for job..." There doesn't seem to be any reason for the problem. The jobs were kicked off manually and they all ran.
The only other error messages we could find were in the Application Event Viewer on the server. But the first error happened days ago and said this: "The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Unable to open shim database version registry key - v2.0.50727.00000."
Since then, every few seconds this error occurred: "Windows cannot load extensible counter DLL MSSQLServerOLAPService, the first DWORD in data section is the Windows error code."
We turned off the performance monitor counters and that error stopped. But we still have no idea if that was the cause of the problem.
This isn't the first time we've had an issue with jobs failing because the steps couldn't be 'found', while SQL Server Agent was up and running.
Has anyone else had this problem?
Thanks!
Hello everyone. I'm having a problem with the sql server agent occasionally not executing its scheduled jobs, and unless the sql server agent is restarted they will not execute according to their schedule. I've read many posts on people having problems with running a package manually and then not being able to run it in a job, although that is not my problem since these are jobs that have all run successfully historically, and only until the agent is restarted do they start working again. It is the strangest thing.....we have many different jobs on this server and all of them will just mysteriously stop being executed. Two other things of note, the agent and server processes both run on AD accounts, and we receive this error:
SQL Server Scheduled Job 'X' - Status: Failed - Invoked on: 2007-10-27 02:00:00 - Message: The job failed. Unable to determine if the owner (ADadacct) of job 'X' has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).
Any help would be greatly appreciated.
Hi,
I have couple of questions about the scheduler:
I'd like my packages to run only on weekdays and disable a package if one of the first two packages fail. is it possible to do it?
Thanks
Gulden
Hello All,
My database application has certain stored procedures which are scheduled to run at certain predefined interval (say once everyday). Now to implement this logic I have two options with me, first being to use the EXECUTE SQL TASK of the SSIS package and schedule it using the windows scheduler and the other is to use the SQL Server Agent Jobs and schedule it there itself. Now the current implementation is using the SSIS packages and I even know that the second approach of using the SQL Server Agents Job is better.
The only thing I want to know is whether it makes sense to invest time in converting my SSIS packages (20 in all) to SQL Server Agent jobs or not. Can somebody briefly explain or give some pointers regarding the performance benefits of Agent Jobs over the SSIS packages.
Thanks in Advance
Mitesh
I want to schedule a database backup using SQL Server Agent Jobs how can i do this?
View 1 Replies View RelatedHi,
I had a few jobs as schduled DTS tasks in SQL 7. I manually saved them and reset the connection. I am able to execute the tasks successfully. However, on scheduling the same, it is failing. I am able to schedule other tasks successfully. So the scheduler is working OK. All tasks that involve dts are not running properply with the scheduler.
Any help appreciated.
Regards,
Ganesh
Hi
I have a strange scheduling problem.
I have a job, i test it through BIDS - Works
I test it through SSMS - works
I run the job - Works
I schedule for a run time job - Works
I schedule for a recurring job - Works
However (here is the really annoying part) it fails to run at the night run (11.20PM)
there are 6 other packages that run fine after it , 11.30 , 1AM .. 4 AM etc ...
So
last night I changed the package to run at a later time, and it worked, but the other package that
ran 2nd (which now ran 1st) failed !!
its almost as if the 1st package has to fail !!!!!
When i look at the history and detailed log all it says is "The step Failed"
anyway , I can set up a workaround by starting a dummy job that is supposed to fail..
FYI the jobs are run under administrator and they are protected by a password (encryptSensitiveWithpassword) .
When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
Yao,
Could anyone help me with, or point me to information regarding, scheduling jobs for MSSQL 2000? I need to execute a SQL Command, monthly, which sets a budget field to a fixed ammount, something like: UPDATE Programs SET Budget = 500.
--Kyle Johnson
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 Replies View RelatedI'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.
The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.
I am actually the MOM administrator and only know enough SQL 2005 to keep mom working....sometimes I noticed on my dev mom box that SQL server agent has a lot of jobs configured to run against the mom onepoint database. The production box does not have these jobs. What would be the simplest way to export these jobs so that i can import them on the production box. Any help would be appreciated.
View 1 Replies View RelatedHow can I use SQL Server Agent schedule to start my.exe?
I am not sure in command that I have to place in "Edit Job Steps" DialogBox.
It's not working just with path. File is accessible, it's on the same server. I dont know KeyWord to execute this .exe.
Sorry for stupid question.
Stan
I have just moved an old sql 2000 database over to new hardware and sql 2005. I had a few dts jobs that ran on the old server and I have converted them over to SSIS. In VisualStudios the packages run just fine, but when I try to schedule it on Agent, It just fails and says step 1 failed. I have looked at it for hours and still have nothing.
Anyone got anything i can try to get this working?
I developed a package,configuration file on my machine i imported the packages on to the SSIS msdb folder, i am scheduling the package by using SQL Server Agent, Jobs while scheduling i got the error
Executed as user: user. ...cute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:14:00 PM Error: 2007-10-23 15:14:00.19 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-10-23 15:14:00.49 Code: 0xC0202009 Source: SampleSchdule Connection manager "Source Connection" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "HiT Software OLE DB Component for DB2/400" Hresult: 0x80004005 Description: "HiTOLEDB400 datasource object (Initialize) - ODBS error: Error on request data". End Error Error: 2007-10-23 15:14. The step failed.
and i have the sysdba permissions to schedule the packages. when i run the package manually in msdb folder it is executed successfully and one more thing i have developed this package on 32 bit machine, and the server is running on 64 bit and i developed the package on 9.00.3042 version, server is 9.00.3054 I am using SQL Server Integration services package option,package source as SQL Server in Steps while scheduling. I am using protection level as EncryptAllWithPassword.
Can anyone help me out with this ?
Hello Everyone,
I wanted to schedule my SQL Agent Job with SSIS package loading data into Oracle Destination can it be possible, if not. What do i do to schedule my SSIS package?
Thank you
I have created a VB.NET application using Visual Studio. I wish to schedule this application on SQL Server Agent, reason being, I wish to be notified when the job succeeds / fails, which is not possible using Win Scheduler.
Any suggestions as to how this can be done?
One way, I think, this can be done is by making use of xp_cmdshell. But, I do not wish to use xp_cmdshell.
Is there any other way?
Any ideas will be appreciated.
Hi:
I created a fairly simple SSIS package to move data from a remote server which uses SQL Server Authentication to a local server that uses Windows Authentication. Everything works fine when executing the package within BI studio and if I execute the package by connecting to SSIS. If I schedule it as a SQL Server Agent Job, however, the non-integrated security fails unless I manually edit the connection to include the password. Obviously this is insecure because it is in plain text. What can I do?
Thanks.
I have a series of jobs in the SQL server Agent that will begin and appear to run fine, but will never finish. Is there a way to limit the time that a job will run in the agent, for instance stop with failure if not completed within 1 hour?
Thanks for the help
Ken
Hi Guys,
Can you please advice me on if there is a limitation on SQL agent Jobs. If then how many jobs can SQL server accomedate.
If there is a limit, then is there are any ways to increase it?
I appreciate your quick response.
Thanks
I have several users that are a member
of a Windows Nt group. I want to be
able to allow this group to create,
update, and delete SQL Agent Jobs.
But I do not want them to be members
of the SYSADMIN role.
Everything that I have found states
that they must be members of this
server role to perform these actions.
Does anyone have an idea on how to
accomplish this? or point me in some
kind of direction to explore.
Windows 2000 sp2
Sql 2000 sp1
Thanks,
Robert
Hi all,
after creating the replication job (works) other agent jobs hang up. after restart the agent, the jobs work one time. t
can it be a problem with dieffernt agent profiles/accounts
many thanks
oliver
The SQL Server Agent process is running. When I use EM to start a database integrity
check job, it fails with an error message of 22029 and the following:
[LOG] SQLServerAgent security context does not have server-autorestart privileges
I can not find error 22029 in Books on Line. I will go to Microsoft Knowledge base
and research this problem.
In the mean time if someone can help, it is much appreciated.
Thanks to all.