Category: SQL-Scripts

SQL Server – Error 3154: The backup set holds a backup of a database other than the existing database

SQL, SQL-Backup & Restore, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Sometime we faced the following error message while restoring the database Error 3154: The backup set holds a backup of a database other than the existing database. Solution: Use WITH REPLACE while using the restore script as Script 1 RESTORE DATABASE YourDB FROM DISK = 'C:\YourDB.bak' WITH REPLACE  Script 2 — Get the Logical file name of the database from backup. RESTORE FILELISTONLY FROM DISK = 'C:\YourDB.bak' GO RESTORE DATABASE YourDB FROM DISK = 'C\:YourDB.bak' WITH MOVE 'YourDB_Data' TO 'C:\Database\YourDB.mdf',

» Read more
, ,

SQL Server – Stored Procedure – WITH RECOMPILE

SQL, SQL-Advanced, SQL-Scripts, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

Stored Procedure has ability to execute faster than the same T-SQL statements executed individually because SQL Server recorded the query execution plan for Stored Procedure. In case you need to execute the stored procedure with different execution plan then use WITH RECOMPILE options Different ways to use the WITH RECOMPILE options You can create the stored procedure using WITH RECOMPILE option. This way every time SQL Server uses the recompile the execution plan. CREATE PROCEDURE sp_orders ( @StartDate Datetime, @EndDate

» Read more
, ,

SQL Server – User Defined Group

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to make the User Defined Group in Reports (i.e. Crystal Reports) Example: CREATE TABLE test_custom_group ( id INT, dept VARCHAR(20), detail VARCHAR(30), salary NUMERIC(5,2) ) Created a Function CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20), grp varchar(10) ) AS BEGIN INSERT @temp_table(sno,dept,grp ) SELECT 1,'C','A' UNION all SELECT 2,'A','A' UNION all SELECT 3,'D','B' UNION all SELECT 4,'E','B' UNION all SELECT 5,'B','B' RETURN END Created a Store Procedure USE test_db IF EXISTS

» Read more
, ,

SQL Server – User Defined Sort Order

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to get the values with Use Defined Sort order. Here is simple way to get User Defined Sort order Example: Created a table as : CREATE TABLE sort_order ( id INT, dept VARCHAR(20), detail VARCHAR(30) ) Inserted few rows in table as Requirement: Now we want to change the sort order and new sorted output should be: REQUIRED OUTPUT: For this created a function: CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20)

» Read more
, , ,

SQL Server – sp_MSforeachdb – Undocumented Stored Procedure

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

The undocumented stored procedure “sp_MSforeachdb” is found in the “master” database. This is similar to sp_MSforeachtable. This stored procedure Sp_MSforeachdb gives a DBA the ability to cycle through every database in your catalog. This stored procedure will loop every database in your catalog for performing a command. This stored procedure accepts the following input parameters.   Example:  You can run a DBCC CHECKDB on all databases as below EXEC sp_Msforeachdb "DBCC checkdb ('?')" Similarly you can run other command also.

» Read more
,

SQL Server – sp_MSforeachtable – Undocumented Stored Procedure

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data. No doubt we can use the dynamic statement or cursor for this purpose. But we have better alternative way; this is an undocumented stored procedure called as “sp_MSforeachtable” in the master database. This stored procedure will loop through all the tables

» Read more
,

SQL Server – Order By Clause Based on a Variable

Guest Post, SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks

This is a guest post from one of my good friend and Developer Ranjoyt Singh. He is a dedicated and committed person professionally and personally. We developers come across many times in a situation where we need to change the sort order in which results of a query are displayed. Sorting based on a variable can be achieved in the application as well as in T-SQL. The SQL Server gives performance benefit over application. So, let’s explore what are the

» Read more
, ,

SQL Server – List of all the Error codes or messages

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Contains one row for each system error or warning that can be returned by Microsoft SQL Server. SQL Server displays the error description on the user’s screen. You can get the List of all the Error codes or messages as USE master GO SELECT * FROM sysmessages You will get the five columns as Error – Unique error number Severity – Severity level of the error Dlevel – For internal use only Description – Explanation of the error with placeholders

» Read more
, ,

SQL SERVER – The Self Join

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

A self-join is joining a table to itself. This is done by using table name aliases to give each instance of the table a separate name. Self Join is very useful when you want to compare values in a column to other values in the same column. Self join and its example is very common question in the interview. Let us take a look to example

» Read more
, ,

SQL Server – SQL Scripts to find and Kill all the Blocked Process in a database

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

SQL Server database administrators frequently need in especially development and test environments to find and kill all the blocked process. Following scripts are useful in that scenario. Script – 1 — Find All the Blocked Processes SELECT spid, status, loginame=SUBSTRING(loginame,1,12), hostname=SUBSTRING(hostname,1, 12), blk = CONVERT(char(3), blocked), dbname=SUBSTRING(DB_NAME(dbid),1, 10), cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)   Script 2 — Kill all the Blocked Processes of a Database DECLARE @DatabaseName nvarchar(50) — Set the Database Name SET

» Read more
, , ,

SQL Server – Reset auto number (IDENTITY) column in SQL Server

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, Varinder Sandhu

How to reset auto number (IDENTITY) column in SQL Server? Sometimes we need to reset the auto number – identity column in a table. Using the DELETE statement is not enough. The statement only deletes the data but not reset the identity column. We can reset the auto number (IDENTITY) column in SQL Server using DBCC CHECKIDENT command. Following example shows how to reset auto number (IDENTITY) column the IDENTITY column Hope this post helps you.

» Read more
, ,

SQL Server – Error Handling Using TRY…CATCH

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Prior to SQL server 2005, the only way of performing the error handling was to test vale of @@ERROR global variable. Once the SQL Server completes the execution of a Transact-SQL statement, it records the status of result in @@ERROR. If the error occurred, @@ERROR contains the error number. If the statement executed successfully it contain the 0. You need to check the variable if the statement succeeded or failed. Using @@ERROR to perform the error handling is not easy

» Read more
, , , ,
1 2 3 4