SQL Server General DBA

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

Monitor SQL Server Replication Jobs

The Replication infrastructure in SQL Server is implemented using SQL Server Agent to execute the various components involved in the form of a job (e.g. LogReader agent job, Distribution agent job, Merge agent job) SQL Server jobs execute a binary executable file which is basically C++ code. You can download all the scripts for this […]

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

Introduction to SQL Server Check Constraints

Check Constraints play a very important role in SQL Server as it allows data validation rules to be imposed at the SQL Server instance level itself instead of writing rules for each application. Check Constraint enforces Domain Integrity by restricting the values that can be inserted into columns. Domain Integrity ensures that only a valid […]

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’ — […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |