Running SQL Database Maintenance Tasks Using SQLCMD

Running SQL Database Maintenance Tasks Using SQLCMD
3.5 (70%) 4 vote[s]

This article is about developing an advanced understanding of the Sqlcmd utility which lets you run T-SQL commands directly from the command prompt without needing SSMS (SQL Server Management Studio).

The article also highlights the importance of using Sqlcmd to perform some advanced-level database tasks that would otherwise require additional steps, e.g. getting connected to the database via a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The Sqlcmd utility can be a great time saver for Database developers and DBAs since they can run the required SQL scripts right from the command line. Read More

Transferring Jobs and Schedules between Instances using T-SQL

Transferring Jobs and Schedules between Instances using T-SQL
4.3 (86.67%) 3 vote[s]

Introduction

Quite often, there is a need to transfer Agent jobs to a different instance of MS SQL Server. Restoring a msdb database will not always be the best decision – there are cases in which you will need to transfer only the Agent jobs specifically, and these cases are not uncommon. Also, this wouldn’t work if the recipient MS SQL Server instance is of a newer version than the one you’re transferring the jobs from. So, how can you transfer the Agent jobs without restoring the msdb database?

We will look at an example of a T-SQL script which copies the Agent jobs from one instance of MS SQL Server to another. This solution was tested by transferring jobs from MS SQL Server 2012-2016 to MS SQL Server 2017.

Read More

SQL Server Bulk Insert – Part 2

SQL Server Bulk Insert – Part 2
4 (80%) 2 vote[s]

In the previous part of this article, we discussed how to import CSV files to SQL Server with the help of BULK INSERT statement. We discussed the main methodology of bulk insert process and also the details of BATCHSIZE and MAXERRORS options in scenarios.  In this part, we will go through some other options (FIRE_TRIGGERS, CHECK_CONSTRAINTS and TABLOCK) of bulk insert process in various scenarios.

Read More

Implementing Automated Database Backup and Restore with Default Means

Implementing Automated Database Backup and Restore with Default Means
3.6 (72%) 5 vote[s]

Introduction

You can find a lot of guides on how to backup and restore databases. In this one, we’ll show how this can be done using the default MS SQL Server means.

This example will cover a number of approaches – from checking the database’s integrity before backing it up to restoring the database from a previously created backup copy.

Read More

Automatic Deletion of Forgotten Transactions in MS SQL Server

Automatic Deletion of Forgotten Transactions in MS SQL Server
4 (80%) 4 vote[s]

Introduction

It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error occurs; however, ‘commit’ or ‘rollback’ do not go through and the execution initiator has left this query for a long time. As a result, more and more fluctuation appears when it comes to blocking the queries which request access to closed-off resources (tables and server resources such as RAM, CPU and the input-output system).

In this article, we’ll look at one of the ways you can automate the forgotten transaction deletion process.

Read More

Advanced SQL: Insert output of the parameterized table-valued function in SQL table

Advanced SQL: Insert output of the parameterized table-valued function in SQL table
3 (60%) 3 vote[s]

In this article, I am going to demonstrate the following:

  1. How to insert the output of a table-valued function in a SQL table.
  2. How to insert the output of a table-valued function which is created on the remote database server.

What is “Insert into” statement

In RDBMS, “Insert into” is one of the basic SQL statements. It is used to insert new records in a SQL table. Using the statement, we can perform the following tasks:

  • Insert new records in a table (Basic Insert).
  • Insert values of a specific column in a table.
  • Insert the output generated by a stored procedure in a SQL table. Read More

SQL Server Bulk Insert – Part 1

SQL Server Bulk Insert – Part 1
4.4 (88%) 10 vote[s]

According to Wikipedia, ”A Bulk insert is a process or method provided by a database management system to load multiple rows of data into a database table.”  If we adjust this explanation in accordance with the BULK INSERT statement, bulk insert allows importing external data files into SQL Server. Read More

SQL Server CRUD Operations – Part 1

SQL Server CRUD Operations – Part 1
4.7 (93.33%) 3 vote[s]

In the database programming, there are four basic and essential operations: create, read, update, and delete. These operations can be defined as the first step in database programming and are called CRUD operations. CRUD is an acronym of the CREATE, READ, UPDATE and DELETE words.  The CRUD term was once mentioned by James Martin in his book ‘Managing the Database Environment’ and since then this term has become popular. In this article, we will explore the CRUD operation in terms of SQL Server because the operation syntax can differ from other relational and NoSQL databases. Read More

Dealing with NULLs in SQL Server

Dealing with NULLs in SQL Server
3.3 (66.67%) 3 vote[s]

What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN. Read More