Category Archives: SQL Server

Difference between Primary, Unique, Candidate and Composite Keys

Primary key: Primary key is a key that is used for uniquely identify the each rows in a table. A primary key cannot be null and cannot be more than one primary key in a table. Where we create a primary key a cluster index is created. But we can change it with non- cluster… Read More »

Calculate age in years, months and days in mysql

When we are dealing with dates in mysql sometimes we need to calculate the age in years months and days directly from the mysql query We can easily achieve it from the below sql query SELECT name,dob, CONCAT(TIMESTAMPDIFF( YEAR, dob, now() ),’ Years,’, TIMESTAMPDIFF( MONTH, dob, now() ) % 12,’ Months,’, FLOOR( TIMESTAMPDIFF( DAY, dob,… Read More »

Error: Unable to connect to the Database. A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: Unable to connect to the Database. Description: While trying or establishing a database connection and the connectivity to the database is not configured properly then this error occurs. More exception details as followed. Exception Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or… Read More »

Difference between Local & Global Temporary Tables

In this post, we will learn what is temporary tables, types of temporary tables and difference between local & global temporary tables. Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The… Read More »

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

Drop all non-system stored procs DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = ‘DROP PROCEDURE [dbo].[‘ + RTRIM(@name) +’]’ EXEC (@SQL) PRINT ‘Dropped Procedure: ‘ + @name SELECT… Read More »

Insert results of a Stored Procedure into a Temporary Table

Here, I will show you how to insert results of a Stored Procedure into a Temporary Table with an example as below: CREATE PROC sp_order AS BEGIN SELECT * FROM sys.t_order END GO sp_configure ‘Show Advanced Options’, 1 GO RECONFIGURE GO sp_configure ‘Ad Hoc Distributed Queries’, 1 GO RECONFIGURE GO SELECT * INTO #temp_orderdetails FROM… Read More »