Error handling using TRY Catch in SQL Server
Error handling using TRY Catch was introduced in SQL Server 2005. If you have .net background then you must be familiar of using TRY – CATCH to handle errors. It is … Read More »
ANSI NULL SQL Server
ANSI_NULLS and QUOTED_IDENTIFIER are among the most common settings that a DBA/developer may come across in Ansi Null SQL Server. Let’s have a look at these settings in detail. ANSI_NULLS This … Read More »
T-SQL find tables without primary key
Here’s a T-SQL find tables without primary key. SELECT OBJECT_SCHEMA_NAME(tables.object_id,db_id()) AS SchemaName, tables.name As TableName FROM sys.tables tables join sys.indexes indexes ON tables.object_id=indexes.object_id WHERE indexes.is_primary_key=0 GO The sys.indexes.is_primary_key column indicates whether … Read More »
SQL query analyzer keyboard shortcuts
Here are few SQL query analyzer keyboard shortcuts to save your time and to make you smart among your peers. These are valid from SQL Server 2005. The most common ones … Read More »
SQL Function to remove characters from string
One of the most common questions over forums is a SQL function to remove characters from string. The function for the same is given below. -- Method 1: Loop IF(Object_id('dbo.fn_removecharactersfromstring')) is …Read More »
SET DATEFIRST in SQL Server
The SET DATEFIRST function is used to set the first day of the week from number 1-7. In order to know the current first day of the week, @@DATEFIRST is used. … Read More »
How to change database compatibility level in SQL Server
Many a times one may need to change compatibility level of a database. In this blog we’ll see how to change database compatibility level or mode of a SQL Server database. … Read More »
Get SQL Server Agent job details
A DBA must be aware of what all jobs are scheduled and there details. To get SQL Server Agent job details, msdb.dbo.sp_help_job system stored procedure comes in handy. 1. To get … Read More »
SQL Server error 18452 login failed user null
One of most oldest and common question in forums is SQL server error 18452 login failed for user ‘null’. The error can be resolved by changing the SQL Server authentication mode … Read More »
T-SQL find all identity columns
Here’s a T-SQL find all identity columns in a database. SELECT OBJECT_SCHEMA_NAME(tables.object_id, db_id()) AS SchemaName, tables.name As TableName, columns.name as ColumnName FROM sys.tables tables JOIN sys.columns columns ON tables.object_id=columns.object_id WHERE columns.is_identity=1 … Read More »
Error 40 could not open a connection to SQL Server
One of the most common errors one may encounter when connecting to SQL Server is Error 40 could not open a connection to SQL server. The error is very clear and … Read More »
Function to get date from datetime in SQL Server
Hi, Many a times need arises to get only date part from Datetime datatype/column/value. This blogs lists functions to get date from datetime in SQL Server. -- SQL Server 2008 onwards … Read More »
Find tables with identity columns in SQL Server
In this blog we’ll see T-SQL query to find tables with identity columns In SQL Server. It can be done in two ways either by using Information_schema catalog or the system … Read More »
Frequently used DBCC commands in SQL Server
Database console commands or DBCC are T-SQL Commands grouped in to four categories, Maintenance, Miscellaneous, informational and validation. This blog lists down some frequently used DBCC commands. A brief description of …Read More »
Script to find all SQL Servers on network
Many a times we are required to find SQL Server installations across local network. This blog provides a script to find all SQL Servers on network using SQLCMD. It’s a very … Read More »
SQL Server error 18456 login failed for user SA
The error – SQL Server error 18456 login failed for user sa may occur because of different reasons. The first step to get it resolve is to check the SQL Server … Read More »
Function to Extract Month from Date in SQL Server
A function to extract month from date is a most common question in forums. Month part from a date can be extracted as shown below SELECT MONTH(GETDATE()) AS [Month] GO SELECT … Read More »
SQL Function to Extract Year from Date
One of the most asked questions in forums and discussion board is how a SQL function to extract year from date. This can be done in multiple ways as shown below. … Read More »
Msg 7391 level 16 state 1 line 1 in SQL Server
The error msg 7391 level 16 state 1 line 1 says “Msg 7391, Level 16, State 2, Line 1 The operation could not be performed because OLE DB provider “SQLNCLI” for … Read More »
Improve stored procedure performance in SQL Server
This blog covers simple yet useful tips and optimization to improve stored procedure performance. 1. Use SET NOCOUNT ON SQL Server returns informational messages when running select or DML operations. In … Read More »
One Comment on “SQL Server Accidental DBA”