SQL Agent Archives - Tomas Lind

windows

From SQL Server to MSMQ using PowerShell

Post author:Tomas Lind
Post published:2017/07/14
Post category:MSMQ PowerShell SQL Agent
Post comments:3 Comments

How do you get records from a SQL Server table into a MSMQ queue as separate XML messages?

A commonly suggested way to communicate with MSMQ from within SQL Server is to create a CLR assembly. See for instance this article that has a great coverage of how to use CLR in this scenario. Note however that you have to import the System.Messaging.dll as unsafe for this to work. And you have to enable CLR obviously.

If this is not an option, another way to do it is to use PowerShell. Which is what I will cover in this demonstration.

(more…)

Microsoft SQL Server

SQL Agent job logging with tokens

Post author:Tomas Lind
Post published:2016/11/04
Post category:SQL Agent
Post comments:5 Comments

A useful feature in the SQL Agent is the possibility to use tokens. Tokens are a number of system variables (e.g. $(ESCAPE_SQUOTE(DATE) that contains the current date) that gets expanded/replaced with a value when the job is run. An obvious use of tokens is to create a new log file with today’s date and time in the file name each time an SQL Agent job is run.

(more…)

Microsoft SQL Server

SQL Agent Workflows

Post author:Tomas Lind
Post published:2015/02/26
Post category:SQL Agent
Post comments:0 Comments

How to control workflows using SQL Server Agent.

(more…)

Microsoft SQL Server

Automatic retries in a SQL Agent job

Post author:Tomas Lind
Post published:2015/01/20
Post category:SQL Agent
Post comments:0 Comments

Many SQL Agent jobs can be run automatically using the scheduler to run the job at specified times. But sometimes it would be better to run the job when specific criteria have been met, instead of at a specified time. For instance, when the data warehouse has finished loading.

This blog post will show how to create a job that starts and then waits / retries until given criteria are met.

(more…)

Microsoft SQL Server

SQL Agent Jobs Summary

Post author:Tomas Lind
Post published:2014/11/19
Post category:Monitoring SQL Agent
Post comments:0 Comments

A script that summarizes information about SQL Agent job execution history. Much in the same way as the Job Activity Monitor, but with some additional information. Also, this script allows you to run it from T-SQL solutions.

(more…)

Microsoft SQL Server

SQL Agent job history gets deleted

Post author:Tomas Lind
Post published:2014/10/23
Post category:Monitoring SQL Agent System Stored Procedures
Post comments:0 Comments

Ever wondered why the SQL Agent job execution history gets deleted at regular intervals?

(more…)

Automate BPA Server

Introduction to Automate BPA Server

Post author:Tomas Lind
Post published:2014/03/13
Post category:Architecture Automate BPA Server SQL Agent
Post comments:0 Comments

If you are using SQL Agent together with SSIS and if you have a batch job solution with hundreds of interdependent SQL Agent steps spread across dozens of servers, you may have run in to some maintenance problems.

For complex batch solutions, a Business Process Automation software can be of great help. In this blog post I’ll give a brief introduction to Automate BPA Server from Network Automation.

(more…)

SQL Server Alerts

Script to configure SQL Server Alerts

Post author:Tomas Lind
Post published:2014/01/21
Post category:Alerts Notifications SQL Agent
Post comments:0 Comments

Are you managing a lot of SQL Server instances? I was setting up a standardized set of SQL Server Alerts on a number of servers and soon realized this was something that could be automated by using a script.

(more…)