SQL Server – How to read the SQL Server Error log files using TSQL

There is undocumented system stored procedure sp_readerrorlog which allows us to read the SQL Server error log files directly using T-SQL. This procedure has total 7 parameters as given below: Parameter Values First Parameter Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc. Second Parameter Log file type : 1 – Reads SQL Server error logs, 2 – Reads SQL Server Agent error logs Third Parameter Search string

» Read more

SQL Server – Like operator does not work with datetime

Today, my one of the friend shared a very interesting fact about like operator. With the help of this post I am sharing with you all. In SQL Server, like operator does not work with datetime. We need to convert it to character data while comparing with the like operator. Let‘s check with example Like operator with datetime Like Operator with datetime using convert function If anybody have any suggestion please share as comment.

» Read more

SQL Server – How to add column dynamically in where clause

In this post, we will learn with example how to add the column dynamically in where clause. Example: For demo we have table as shown in the snapshot Basically we want to execute following script (i.e. Script: 1) but column name (i.e. FIRST_NAME) added dynamically in where clause. Script: 1 SELECT * FROM EMPLOYEE WHERE FIRST_NAME = 'VARINDER' Created a table and insert the column name value (i.e. FIRST_NAME) as shown in the snapshot Now we will execute the Script:

» Read more

SQL Server – The multi-part identifier could not be bound State – Msg 4104 – Fix

Error Msg Server: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier could not be bound. Cause of this Error Msg: Case: 1 This error usually occurs when an alias is used when referencing a column in a SELECT statement and the alias used is not defined anywhere in the FROM clause. Case: 2 This error occurs if we are using the tables from the two different databases in the join statement and not using the table alias

» Read more

SQL Server – Generate Row Number in Select and Select into

How to Generate the Row Number in Select and Select into script? Using IDENTITY for Select Into SELECT RowNumber=IDENTITY(int,1,1),FIRST_NAME,LAST_NAME into EMPLOYEE_backup FROM EMPLOYEE Using Row_Number() for Select SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) RowNumber, FIRST_NAME,LAST_NAME FROM EMPLOYEE Using Row_Number() for Select into SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) RowNumber , FIRST_NAME,LAST_NAME into EMPLOYEE_backup1 FROM EMPLOYEE

» Read more

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

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 – User Defined Group

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

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

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

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

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

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
1 2 3