In a previous article about creating a SQL Server Agent job with T-SQL, I demonstrated how to create a job with a single step.
In this article, I’ll show you how to create a job with multiple steps.
Continue readingCategory: SQL Server
How to Do a LEFT SEMI JOIN in SQL Server
A LEFT SEMI JOIN is kind of a half-join. It returns any distinct values that are returned by both the query on the left and right sides of the query.
However, when using T-SQL in SQL Server, if you try to explicitly use LEFT SEMI JOIN in your query, you’ll probably get the following error:
How to Do a LEFT ANTI SEMI JOIN in SQL Server
A LEFT ANTI SEMI JOIN is a type of join that returns only those distinct rows in the left rowset that have no matching row in the right rowset.
But when using T-SQL in SQL Server, if you try to explicitly use LEFT ANTI SEMI JOIN in your query, you’ll probably get the following error:
Format sysjobhistory datetime & duration Columns in SQL Server
If you’ve ever queried the sysjobhistory table in the msdb database, you’ll probably know that the datetime and duration columns are stored as integers.
In particular, when you query this table, the run_date, run_time, and duration columns are returned as integers, which can make it difficult to read.
Below is a query you can use to return this data in an easier to read format.
Continue readingList of Date Formats Available with CONVERT() in SQL Server
The following table contains a list of the date formats that you can provide to the CONVERT() function when you convert a date/time value to a string.
These formats are provided as an optional third argument when calling the CONVERT() function. They’re provided as an integer expression that specifies how the CONVERT() function will format the date.
How to Fix “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION” in SQL Server
If you’re receiving error Msg 3902, Level 16, which reads “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION”, it’s probably because you’ve got a stray COMMIT statement.
You could be getting this due to implementing error handling, and forgetting that you’ve already committed or rolled back the transaction elsewhere in your code.
Continue reading4 Ways to Get a List of Schedules in SQL Server Agent (T-SQL)
In this article, I present four options for using T-SQL to return the schedules that are attached to a SQL Server Agent job.
Continue readingCreate a SQL Server Agent Job in Azure Data Studio
In this article, I show you how to create a SQL Server Agent job in the Azure Data Studio GUI.
Here, I create a test job that includes one job step and an attached schedule.
Continue readingCreate a SQL Server Agent Schedule with T-SQL
When using T-SQL, you can use the sp_add_schedule stored procedure to add a SQL Server Agent schedule.
This procedure creates the schedule, but it doesn’t attach it to any jobs. To attach it to a job, you must use the sp_attach_schedule procedure.
You can also use sp_add_jobschedule to create the schedule and attach it all with the same procedure.
How to Locate SQL Server Agent Jobs in Azure Data Studio
I wrote this post, because it may not be immediately apparent how to find the SQL Server Agent jobs in Azure Data Studio once you’ve installed the SQL Server Agent Extension.
In SSMS, the SQL Server Agent can be hard to miss, but in Azure Data Studio, it can be hard to find!
Or perhaps you’re not even aware that you need the extension, and you may be searching for jobs that simply can’t be found.
Here’s how to locate the SQL Server Agent jobs in Azure Data Studio.
Continue reading