SQL Server General DBA

How To Remove the Identity Column in SQL Server

Setting the identity property on a table when it is created is a simple task. The below snippet will create table called SampleTable with ID column which has an identity property. CREATE TABLE SampleTable ( ID INT IDENTITY(1,1), Name VARCHAR(30) ) To set an indentity column for an alredy created table using SQL Server Management […]

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 […]

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 […]

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, […]

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 […]

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 […]

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 […]

Pareto Charts in SSRS

The purpose of a Pareto chart is to highlight the most important amongst a set of factors. For example, in quality control for a manufacturer, a Pareto chart can highlight the most common sources of defects and the highest occurring type of defect. The Pareto principle is also known as 80-20 rule, so for quality […]

Design A Hybrid Report in SSRS

Hybrid reports are the reports which have two types of graphs using different axes within the same graph . For example. In the above report, Product cost is on the right axis while Sales Amount Percentage is shown on the left axis, so that viewers can easily compare Product Cost vs Sales Amount Percentage. Below […]

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 […]