SQL Server General DBA

Using QUOTENAME() to Protect Against SQLInjection

QUOTENAME is function which has been available since SQL Server 2005. This function returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier, such as in the below example The output for this query is as below:           String Default_QuoteName QuoteName_WithQuote Long_String SQL Server [SQL Server] […]

New Database Backup Options in SQL Server 2014

Backup options are changing with every release of SQL Server. With SQL Server 2014, several important backup features have beenintroduced. First, the options are divided into two options called Media, Backup. General Under General, there is a new media type called URL. This option is to store the backup directly to cloud services. After providing […]

Importing data from text file to specific columns using BULK INSERT

Bulk insert is much faster than using other techniques such as  SSIS. However, when you are using bulk insert you can’t insert to specific columns. If, for example, there are five columns in a table you should have five values for each record in the text file you are importing from. This is an issue […]

Finding Replication Publications For A Table

If you want to drop or rename a table, you will need to make ensure the table is not an article of a publication. If it is article for any publication, you need to find out what are those publications. If you have several publications and if one table is an article for many publications, […]

Automatically Retry A SQL Server Job After A Failed Step

If you are running SQL Server jobs with numerous steps there can be failures due to network glitches or deadlocks. In such a scenario the DBA will typically restart the job from the failed step. However, there is an option in SQL Server Agent to automatically retry the job, which is under utilized by many […]

Data Mining Add-ins Error

When using Microsoft SQL Server 2012 Data Mining Add-ins you may encounter the below error: Error (Data mining): Session mining objects (including special data source views used to process data mining dimensions) cannot be created on this instance The reason this occurs is that the Data Mining tool needs to create session mining models in […]

Overview of Template Explorer Feature in SQL Server Management Studio

This article will give you an Overview of Template Explorer Feature in SQL Server Management Studio which was initially introduced in SQL Server 2005. SSMS has a variety of inbuilt templates which contains SQL Scripts that can help you create objects within SQL Server Database. Let’s work through an example of using the Template Explorer […]

Finding Connections for A Single Database

During trouble-shooting you may want to find all the connections relevant to one database. You can use sp_who2 but the problem with that is it can return a lot of results for all connections and you need to scroll through entire result set. The following script will return connections relevant to one database with other […]

Using Stored A Procedure with Temp Tables for SSIS Packages

Using stored procedure with temp tables for SSIS packages. Some stored procedures are complex and so require temporary tables or table variables. Let us say we have the following procedure to extract data. CREATE PROC ProcWithTableVariable AS DECLARE @Table TABLE (id Int) INSERT INTO @Table VALUES (1),(2) SELECT * FROM @Table And SSIS OLE DB […]

Verify Whether a SQL Server Agent Job is Running

When I posted an faq about Finding Running SQL Server Agent Jobs I had several few emails and queries on how verify whether a job is running by using only a TSQL script. The below script will verify if the job exists and whether or not it is running. DECLARE @jobname sysname =’Running Job’ — […]