48
votes
How to run recurring tasks on Postgresql without an external cron-like tool?
As of PostgreSQL 9.5, you can use the pg_cron extension, which is loaded as a shared library into PostgreSQL.
After setting it up, creating a job is pretty simple:
SELECT cron.schedule('30 3 * * 6',...
- 581
12
votes
Accepted
INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'
It's not the way the table was created, it's the options your query runs with.
Agent does indeed run with the wrong settings.
SELECT
s.*
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
...
12
votes
Why does this query fail when I run it from a SQL Server job?
It fails because you aren't using a language neutral datetime format. You are assuming that SQL Server understands that the day comes first, then the month and finally the year.
When you run this ...
- 17.9k
12
votes
How can we prevent Agent Jobs running twice when the clocks change?
Use UTC when setting up your SQL Server.
If the platform you're serving is large enough that this is an issue, then you're probably doing business in more than one time zone. If you never plan on ...
- 5,001
11
votes
Does @os_run_priority in sp_add_jobstep actually work in SQL Server 2008 R2?
This is part of the job step definition and you may even see it have values used or defined in other areas.
After taking a look in the source code (I work at Microsoft and have access), while the ...
- 35.5k
9
votes
How to create jobs in PostgreSQL?
PostgreSQL doesn't have the concept of scheduled jobs.
You can use
cron or the like on Unix.
Windows task scheduler on Microsoft operating systems.
With these, just use psql to connect the db and ...
- 64.6k
9
votes
Accepted
Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016?
Discontinued Database Engine Functionality in SQL Server 2016 indicates
ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.
8
votes
What is meant by a blind write in a schedule?
If there is no read that happens prior to the first write then it is said to be a blind write.
Example. Consider the following schedule:
W3(X) is a blind write, as there is no read before write [R3(...
- 181
8
votes
How to create jobs in PostgreSQL?
You can configure your recurring tasks by creating database jobs in PostgreSQL. PostgreSQL provides one external tool to configure PostgreSQL jobs, its name is PGAgent.
PGAgent (a part of pgAdminIII) ...
- 6,497
8
votes
How to add a step in all existing jobs in sql server
I have created a stored procedure which will add a new first step to any existing agent job.
It checks if running on the AG primary replica and quits with success otherwise:
use master
go
-- Adds ...
- 1,376
8
votes
Accepted
How do I find a job that sends a specific email?
The problem with your first query is the use of unescaped brackets with the LIKE operator.
When using the LIKE operator, there are a few special characters such as brackets that require escaping if ...
- 3,306
8
votes
Cannot give NT SERVICE\MSSQLSERVER permissions on network drive
The accepted answer is wrong. The 'NT SERVICE\MSSQLSERVER' is not a local service account, it is virtual account.
Virtual Accounts are running tasks in the context of the computer they are installed ...
- 181
7
votes
How to run recurring tasks on Postgresql without an external cron-like tool?
You really, really don't want to do this. Postgres is not an operating system, it's a database server. Even if the database supports running scheduled tasks, it's not really a good idea to abuse the ...
- 307
7
votes
Calling a SQL Server job within another job
From stackoverflow (mafafu )
WAITFOR DELAY '00:00:02';
while exists (select * from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id
...
7
votes
Accepted
Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)
I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the ...
- 7,984
7
votes
Accepted
Why does this query fail when I run it from a SQL Server job?
I'd say the problem is related to the language being used when the query runs.
Please, run this from SSMS and from the job you created to check if the language being used is the same on both methods:
...
- 5,926
7
votes
Can AWS services replace SQL Server Agent Jobs?
Can AWS services replace SQL Server Agent Jobs?
Sure, in the same sense that Walmart can replace McDonald's. Both sell food, but one of them requires you to cook the food yourself and it may not come ...
- 39.5k
7
votes
Accepted
The run_duration column from the msdb.dbo.sysjobhistory returns negative values
One possibility is the duration is negative because the system clock was adjusted during the job's execution.
SQL Server uses a frankly daft integer representation for dates, times, and durations in ...
- 90.2k
7
votes
How can we prevent Agent Jobs running twice when the clocks change?
Don't start jobs between the times when clocks change in your location.
For example In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday ...
6
votes
how to check who has killed the job which was running ?if logs are not generated
The error log should contain some information about what session killed a particular process. Run the following statement to get the hostname and process id of the user the KILL command originated ...
- 9,487
6
votes
Accepted
The metadata could not be determined because statement invokes an extended stored procedure
Nothing (that I can see) changed between SQL Server 2012 and 2016 with respect to this issue. Not working with temp tables, multiple result sets, extended stored procedures, CLR objects, etc have all ...
- 69.5k
6
votes
Accepted
What's the best way to pass a variable/valuable between steps in a SQL Server job
The only way to pass state between SQL Server Agent T-SQL Job Steps is via a real, i.e. non-#temp, table, perhaps one that exists only in the tempdb database. Even a global temp table (##temp) won't ...
- 70.5k
6
votes
Accepted
What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule?
The documentation states
freq_interval is one or more of the following:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday
These values are all powers of 2....
- 86.5k
6
votes
Run SQL Agent Job 3 times in a Row
I think I would solve it this way:
Create a driving stored procedure that identifies if work needs to be done. That could mean checking a table for the next folder month to process. This driving ...
6
votes
Accepted
Start job from another SQL server instance
You probably don't want to grant direct access to sp_start_job or sp_stop_job, or even grant access to a range of SQL Agent functionality via SQLAgentOperatorRole, to either NT AUTHORITY\NETWORK ...
- 69.5k
6
votes
Accepted
Why the job's next_run_time is wrong?
The docs on sysjobschedules reveal that the data inside refreshes every 20 minutes.
NOTE: The sysjobschedules table refreshes every 20 minutes, which may
affect the values returned by the ...
- 16.5k
5
votes
Grant permissions to run a SQL Server job
This will allow a specific user (user1) to run any SQL Agent job.
Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all ...
- 51
5
votes
Ola Hallengren backup failed
You need to ensure the SQL Server Service Account has the required Access Control for the paths used for storing your backups.
Check the SQL Server Accounts and Permissions page for details about ...
- 70.5k
5
votes
Automated SP_WhoIsActive SQL Activity Capturing Issue
It appears from the Primary Key name that you are running Adam Machanic's sp_WhoIsActive and storing the result in a table.
If that is the case, the warnings are normal and caused by the options you ...
- 423
5
votes
Accepted
Automated SP_WhoIsActive SQL Activity Capturing Issue
Automate capturing SQL Server activity with SP_WHOISACTIVE and SP_WHO2 via a SQL Agent job
Any idea what may be causing this?
Obviously your current process has an issue trying to insert duplicate ...
- 2,288
Only top scored, non community-wiki answers of a minimum length are eligible