Skip to main content
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',...
Marco Slot's user avatar
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 ...
Erik Darling's user avatar
  • 41.9k
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 ...
Tibor Karaszi's user avatar
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 ...
Sean Gallardy's user avatar
9 votes
Accepted

What is considered for output from an sql server job step?

For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT and RAISERROR. Result sets are also included as "output", but only if there are no PRINT / RAISERROR messages, else it ...
Solomon Rutzky's user avatar
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 ...
Evan Carroll's user avatar
  • 63.7k
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.
Scott Hodgin - Retired's user avatar
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(...
sharhp's user avatar
  • 181
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 ...
Mitch Wheat's user avatar
  • 1,375
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 ...
EzLo's user avatar
  • 3,306
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 ...
Chris Harland's user avatar
7 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) ...
Md Haidar Ali Khan's user avatar
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 ...
Tony Hinkle's user avatar
  • 7,964
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: ...
Ronaldo's user avatar
  • 5,091
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 ...
J.D.'s user avatar
  • 38.1k
6 votes
Accepted

Can an SQL Job Deletes itself after its end date?

Can an SQL Job Deletes itself after its end date? I have created a job which will sets up its start date and end date automatically with a dynamic value. Is there a way to code it while ...
IT Thug Ninja's user avatar
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 ...
John Eisbrener's user avatar
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 ...
Solomon Rutzky's user avatar
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 ...
Hannah Vernon's user avatar
  • 70.2k
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....
Martin Smith's user avatar
  • 84.9k
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 ...
Scott Hodgin - Retired's user avatar
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 ...
Solomon Rutzky's user avatar
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 ...
Randi Vertongen's user avatar
5 votes

Script to see running jobs in SQL Server with Job Start Time

I ran into issues on one of my servers querying MSDB tables (aka code listed above) as one of my jobs would come up running, but it was not. There is a system stored procedure that returns the ...
Tequila's user avatar
  • 189
5 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 ...
Lie Ryan's user avatar
  • 287
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 ...
drinky's user avatar
  • 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 ...
Hannah Vernon's user avatar
  • 70.2k
5 votes

Suggested SQL Server Backup Schedule using Ola Hallengren scripts

[T]he suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would ...
Randolph West's user avatar
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 ...
MillhouseD's user avatar
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 ...
IT Thug Ninja's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible