Configure SQL Server Agent Mail to Use SQL Mail

We can configure SQL Server to send email. First you had to enable SQL Mail and set up a mail profile. SQL Mail can be enabled by opening the Properties dialog box from the SQL Mail node, by using the Surface Area Configuration facet of Policy-Based Management, or by the using the sp_configure stored procedure.

• Expand a server in  Object Explorer
• Right-click on SQL Server Agent, and click on Properties.
• On the Alert System page, click Enable mail profile. If this option is not available, enable SQL Mail and set up a mail profile.
• In the Mail system list, choose SQL Mail.
• In the Mail profile list, select the Extended MAPI mail profile you created for SQL Mail. For more information about configuring a mail profile, see How to: Configure Mail Profiles for Use by SQL Mail (Windows).
• Restart the SQL Server Agent service for your changes to take effect. For more information, see How to: Restart the SQL Server Agent Service (SQL Server Management Studio).
• To test SQL Mail, send a test message using xp_sendmail.

Scheduling database backup in SQL Server Management Studio

Follow the below steps to schedule database backup in SSMS.

1) Start SQL Server Management Studio.

2) In the Connect to Server dialog box, click the appropriate values in the Server type list, in the Server name list, and in the Authentication list.

3) Click Connect.

4) In Object Explorer, expand Databases.

5) Right-click the database that you want to back up, click Tasks, and then click Back Up.

6) In the Back Up Database - DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.

7) In the Select Backup Destination dialog box, type a path and a file name in the Destinations on disk box, and then click OK.

8) In the Script list, click Script Action to Job.

9) In the New Job dialog box, click Steps under Select a page, and then click Edit if you want to change the job parameters.

Note In the Job Step Properties - 1 dialog box, you can see the backup command.

10) Under Select a page, click Schedules, and then click New.

11) In the New Job Schedule dialog box, type the job name in the Name box, specify the job schedule, and then click OK.

Note If you want to configure alerts or notifications, you can click Alerts or Notifications under Select a page.

Click OK two times.

You receive the following message:

The backup of database 'DatabaseName' completed successfully.

Note To verify the backup job, expand SQL Server Agent, and then expand Jobs. When you do this, the SQL Server Agent service must be running.

Error The trasaction log for database is full.

Sometime we may face an error in SQL Server while performing some transaction

ERROR - (Error 9002)

The following SQL Server Error or errors occured when accessing the "Table Name" table:
9002,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server] The trasaction log for 
database 'Database name' is full. To find out why space in the log cannot be reused, see the
log_reuse_Wait_desc column in sys.database.

SQL:
Update "database name"."dbo"."Table Name" with (RepeatTableRead) set something where 
query.

Reason For Error-
The error is due to space avilability in the disk where Log file reside.

Resoultion -

There are multiple options for the resoultion of the same, choose one that best suite you-
1) Backing up the Log.
2) Freeing Disk Space.
3) Moving the Log File to a Different Disk
4) Increasing the size of a log file.
5) Adding a log file on a different disk.
6) Completing or killing a long-running transaction.

Index statistics in a database

Below query use and DMV and shows you the general state of the indexes in a database. It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the last restart or the index was (re)created, and how many indexes SQL Server thought it would like to have on the table. In a well tuned database, the unused and missing counts would both be 0. If they are, you have exactly the right number of indexes for the workloads hitting your tables.



SET Transaction Isolation Level Read Uncommitted


SELECT CONVERT(VARCHAR(30),so.name) AS TableName,
       COALESCE(Unused.IndexCount, 0) AS IndexCount,
       COALESCE(Unused.UnusedIndexCount, 0) AS UnusedIndexCount,
       COALESCE(Missing.MissingCount, 0) AS MissingIndexCount,
       COALESCE(CONVERT(DECIMAL(6,1), (CONVERT(DECIMAL(10,2),Unused.UnusedIndexCount)/CONVERT(DECIMAL(10,2),Unused.IndexCount)) * 100), 0) AS UnusedPercent


FROM sys.objects so
LEFT JOIN
    (SELECT s.OBJECT_ID, COUNT(*) AS IndexCount, SUM(CASE WHEN s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 THEN 1 ELSE 0 END) AS UnusedIndexCount
            FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
            ON s.OBJECT_ID     = i.OBJECT_ID AND s.index_id = i.index_id
            WHERE s.database_id   = DB_ID() AND OBJECTPROPERTY(s.OBJECT_ID, 'IsMsShipped') = 0
            GROUP BY s.OBJECT_ID
        ) AS Unused
ON Unused.OBJECT_ID = so.OBJECT_ID
LEFT JOIN (SELECT  d.OBJECT_ID, COUNT(*) AS MissingCount
            FROM sys.dm_db_missing_index_groups  g JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
            JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
            WHERE d.database_id = DB_ID()
            GROUP BY d.OBJECT_ID
            ) AS Missing
ON Missing.OBJECT_ID = so.OBJECT_ID
WHERE so.type_desc = 'USER_TABLE' AND (Missing.MissingCount > 0 OR Unused.UnusedIndexCount > 0)
ORDER BY UnusedPercent DESC 


SET Transaction Isolation Level Read Committed

SQL SERVER EXPRESS 2008 R2 – INSTALLING ON WINDOWS 7 WITH VISUAL STUDIO 2010

Today I was trying to install the SQL Server 2008 R2 Developer Edition on my system. Operating system is Windows 7 with Visual Studio 2010. When I run the setup up file I got the follow error message:

System.Configuration.ConfigurationErrorsException: An error occurred creating the configuration section handler for userSettings/Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified. (C:\Users\Arun\AppData\Local\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0\user.config line 5) ---> System.IO.FileNotFoundException: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.
File name: 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

I pressed continue and SQL Server installed without any error.

Get all dates between date range

There are various ways through which we can get all date between date range.


Method 1



WITH myCounter AS
(
  SELECT CAST('2012-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    myCounter   
  WHERE   DateValue + 1 < '2012-12-31'
)
SELECT  DateValue
FROM    myCounter
OPTION (MAXRECURSION 0)

Method 2

DECLARE @FromDate datetime

DECLARE @ToDate datetime

DECLARE @NoOfDays  INT

SELECT @FromDate = getDate() - 15

SELECT @ToDate = getDate()

DECLARE @index INT

SELECT @index = 0

SELECT @NoOfDays = datediff(day, @FromDate, @ToDate)

CREATE TABLE #tempTable

(

   ID INT NOT NULL IDENTITY(1,1)

   ,CommonDate DATETIME NULL

)

WHILE @index < @NoOfDays

   BEGIN

      INSERT INTO #tempTable (CommonDate) VALUES  (DATEADD(Day, @index, @FromDate))   

      SELECT @index = @index + 1

   END

SELECT CONVERT(VARCHAR(10), CommonDate, 102) as [Date Between] FROM #tempTable

DROP TABLE #tempTable

Method3

CREATE FUNCTION dbo.ShowDates

(

     @FromDate    char(10)  

    ,@ToDate      char(10)

)

RETURNS

@DateTable table

(

    Date datetime

)

AS

BEGIN

IF ISDATE(@FromDate)!=1 OR ISDATE(@ToDate)!=1

BEGIN

    RETURN

END

INSERT INTO @DateTable

        (Date)

    SELECT

        CONVERT(datetime,@FromDate)+n.Number-1

        FROM Numbers  n

        WHERE Number<=DATEDIFF(day,@FromDate,CONVERT(datetime,@ToDate)+1)

RETURN

END 

Create a Comma-Delimited List Without Using a Cursor

Cursors in SQL Server are used to process a complete set of rows one by one. Cursors are much slower and we should avoid using Cursors.

Use below query to create comma-delimited list without th use of a Cursor:

DECLARE @CardIDIDs  VARCHAR(MAX)


SELECT @CardIDIDs = ISNULL(@CardIDIDs + ',', '') + [CardID] FROM CustomerMaster


SELECT @CardIDIDs

How to determine 32 or 64 bit version of SQL Server

Run below query to know which edition SQL server is installed:


SELECT ServerProperty('edition')


It will show the result like this: 

32-bit: Enterprise Edition

OR

64-bit: Developer Edition (64-bit)

You can also check this with below code:

select @@version


It will show the result like this:


Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600: ) 

Drawing a circle in SQL Server 2008 R2

Use below script to draw a circle:  



DECLARE @Points AS FLOAT ;
DECLARE @count AS INT ;
DECLARE @size AS INT ;
SET @Points = 360;
SET @count = 0;
SET @size = 1;
DECLARE @tmpTable TABLE (
geo geometry);


WHILE (@count < @Points)
BEGIN
INSERT INTO @tmpTable
SELECT geometry::STGeomFromText('POINT(' + CAST (SIN(RADIANS((CAST (@count AS FLOAT) / CAST (@Points AS FLOAT)) * @Points)) * @size AS VARCHAR) + ' ' + CAST (COS(RADIANS((CAST (@count AS FLOAT) / CAST (@Points AS FLOAT)) * @Points)) * @size AS VARCHAR) + ')', 4326) AS Posn;
SET @count = @count + 1;
END
SELECT geo
FROM   @tmpTable;  


Now choose the 'Spatial Results' tab:

HashBytes Function to implement a one way hash

In below script I have chosen to use SHA1 for this demo which produces an 160-bit (20 byte) hash.

SELECT CAST(REPLICATE('Z',1024) AS NVARCHAR(1024))  
-- returns me a string of 1024 'Z' characters  
  
SELECT LEN(CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))  
-- clarifies the length is 1024   
  
SELECT DATALENGTH(CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))  
-- shows the real storage length is 2048 (unicode, i.e. Nvarchar requires double byte storage)  
  
SELECT HASHBYTES('SHA1', CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))  
-- shows the MD5 hash of that Nvarchar string  
  
SELECT LEN(HASHBYTES('SHA1', CAST(REPLICATE('Z',1024) AS NVARCHAR(1024))))  
-- confirms the length of that MD5 hash is 20  
  
DECLARE @myhash VARBINARY(20)  
SELECT @myhash = HASHBYTES('SHA1', CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))  
-- assigns varbinary hash to variable  

Get the view definition from a SQL Server using ADO

Use the below query to get the view definition from a SQL server using ADO. This is applicable for SQL Server 2005 and later.


SELECT definition FROM sys.objects AS O JOIN sys.sql_modules AS M ON M.object_id = O.OBJECT_ID where O.type = 'V'


If you want to get the definition of a particular view then use below query:


SELECT definition FROM sys.objects AS O JOIN sys.sql_modules AS M ON M.object_id = O.OBJECT_ID where O.type = 'V' AND o.object_id = object_id( 'dbo.TotalView')


Note:


If the view was last modified with ALTER VIEW, then the script will be an ALTER VIEW statement instead of CREATE VIEW statement.

Get a List of Stored Procedures Within a Database

You can get the list of Stored Procedure within a database through different ways. 


The first first method is with the INFORMATION_SCHEMA.ROUTINES system view.  The INFORMATION_SCHEMA.ROUTINES system view contains one row for each stored procedure and function accessible to the current user in the current database.  


SELECT Routine_Name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'


Here we need to get only Stored Procedure, so  ROUTINE_TYPE filtered out for a value of ‘PROCEDURE’.


The second way of getting a list of stored procedures within a database is with the sp_stored_procedures system stored procedure.  The sp_stored_procedures system stored procedure returns a list of stored procedures and user-defined functions in the current environment.  


EXECUTE [dbo].[sp_stored_procedures] @sp_owner ='dbo'


Here we had passed a parameter to limit the User defined stored procedure. 


The third way of getting a list of stored procedures is by querying the different system views and system tables, namely the sys.procedures, sys.objects, sys.all_objects, and dbo.sysobjects:



SELECT [Name] FROM [sys].[procedures]
SELECT [Name] FROM [sys].[objects] WHERE [type] = 'P'
SELECT [Name] FROM [sys].[all_objects] WHERE [Type] = 'P' AND [Is_MS_Shipped] = 0
SELECT [Name] FROM [dbo].[sysobjects] WHERE [XType] = 'P'

Remove Auto Shrink from all databases

Use below query to remove Auto Shrink from all database: 


DECLARE @DatabaseName varchar(100)  
DECLARE @AlterQuery varchar(500)  
  
DECLARE @CurDB CURSOR READ_ONLY FOR  
SELECT name FROM sys.databases WHERE database_id >  4 AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1  
  
OPEN @CurDB  
FETCH NEXT FROM @CurDB INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
    BEGIN  
    SET @AlterQuery ='ALTER DATABASE [' + @DatabaseName + '] SET AUTO_SHRINK OFF WITH NO_WAIT' + CHAR(10)  
    print @AlterQuery  
    EXEC(@AlterQuery)  
    FETCH NEXT FROM @CurDB INTO @DatabaseName  
    END  
  
CLOSE @CurDB  
DEALLOCATE @CurDB  

How many times a character (string) is repeated within a string

Use below procedure to find the occurrence of some characters within a string.


DECLARE @strColumn VARCHAR(20)
DECLARE @searchString  VARCHAR(10)
DECLARE @intLen INT


SET @strColumn = '123asd123asd123' 
SET @searchString ='123'
SET @intLen = LEN(@searchString)


SELECT (LEN(@strColumn) - LEN(REPLACE(@strColumn, @searchString, '')))/@intLen 

Export query result to Excel or CSV

If you want to export the result of a query or Stored procedure from SQL Server Management Studio without writing any scripts then use following procedure:

Option1:

You don't have to change any formats for this option. You just execute your SP or SELECT statement and once you got the output on the Results Window. Just "Right click" on the result and choose "Save Result As" to save it as CSV file.

Option2::

One of the easiest option if you are using SQL Server 2005 or above is to make use of the output format specifier in SQL Server Management Studio.

Step1: Open SQL Server Management Studio.

Step2: Click on New Query

Step3: Goto Menu Query >> Query Options >> Within Results Choose Text

Step4: On the right window you can see an option called "Output Format". Select "Comma Delimited".

Step5: Next either Press Control + T (to select Results to Text option). Or goto Menu Query >> Results To >> Results to Text option.

Now you are all set. Just run your SELECT statement or SP with the SELECT statement to see the result as Comma Separated Values on the screen.

You can copy and save it as a file now.

Find when SQL Server was last started

When ever SQL Server is (re)started ‘TempDB’ gets recreated. So the below query should give us the time of when ‘SQL Server’ was last started.

Select create_date from sys.databases where [name] = 'tempdb'

When a SQL Server starts all the tasks which it initiates makes an entry in sysprocesses table with its ‘login time’. That said, all we need to do is find the least login_time which has got recorded in that table!

1. Select min(login_time) from master.dbo.sysprocesses 
OR
2. Select login_time from master.dbo.sysprocesses where spid = 1 
OR
3. Select login_time from sys.dm_exec_sessions where session_id = 1

Limit a VarChar Column to 10,000 Characters

Before SQL Server 2005 then maximum length of a VarChar data type is 8000. In SQL Server 2005 VarChar(Max) data type was introduced. VarChar(Max) can store upto 2,147,483,645 characters. 


Now you want to restrict the length of a VarChar data type to 1000. You had defined the column as VarChar(10000) and upon saving you got an error:

Msg 131, Level 15, State 3, Line 1

The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

Now it means you can set length of a column to either MAX or <= 8000. So follow below procedure to define max length to 10000.

To do this you had to create a check constraint against that column and set data type to Varchar(Max).

Example: added a column Address in CustomerMaster Table with Data type Varchar(Max). Now add a contraint on address column to restrict input length to 10000.

ALTER TABLE CustomerMaster ADD CONSTRAINT [MaxLen10000] CHECK (DATALENGTH([Address]) <= 10000)

Add or Modify IDENTITY property to an existing column

Method 1
Easiest method is to use SSMS to change the property.

• Goto SQL Server Management Studio
• Right click on the table name
• Choose Design/Modify
• Choose the column which needs to be set as Identity
• At the bottom window (Column Properties) for that column you can see "Identity Specification" has been by default set to "NO".
• Just change it to "YES" and save the table (CTRL + S).

Method 2

If there is no data in the table then use this method.

Drop the already exist column 

ALTER TABLE TempTable DROP Column ID;

GO;

Now add the ID column again with Identity specification on.

Alter Table TempTable Add ID INT IDENTITY
GO 

Method 3

If there is data in the table then use this method.

• Create a new table with identity column
• Enable IDENTITY_INSERT for this new table
• Move the data from this old table to this new table.
• Disable IDENTITY_INSERT for this new table
• Delete the old table.
• Rename the newtable with the oldtable name. 

Random Number Generator

Method 1 : Generate Random Float Numbers
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )


Method 2 : Random Numbers Quick Scripts
---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())


Method 3 : Generate Random Numbers (Int) between Range
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT


---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random


Method 4 : Random Numbers (Float, Int) Tables Based with Time
DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum


Method 5 : Random number on a per row basis
---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber

Finding Log Size for all Databases in SQL Server

Monitoring the size of Transaction Log files is one of the important tasks for a SQL Server DBA. I regularly monitor my database log files that it do not grow tremendously in size and potentially run out of space. Below script will give the list of Databases and their Transaction Log files size in MB in the descending order.



SELECT INSTANCE_NAME AS [Database],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC

Finding Databases which had Transaction Log Growth

The following T-SQL script will give the list of the databases and how many times the log file grew by using the auto-grow value. This information is available since the last restart of the SQL Instance and will be reset again when the Instance is restarted. This list will not count if you grow the log file manually.



SELECT INSTANCE_NAME,cntr_value FROM MASTER.dbo.sysperfinfo
WHERE COUNTER_NAME = 'Log Growths'
AND INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource')
AND CNTR_VALUE <> 0

Conversion from Hex String to VarBinary and vice versa

-- Conversion from hex string to varbinary: 


DECLARE @hexstring VarChar(MAX);
SET @hexstring = 'abcedf012439';
SELECT  CAST('' AS XML).Value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
FROM (SELECT CASE SubString(@hexstring, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
GO


-- Conversion from varbinary to hex string: 


DECLARE @hexbin VarBinary(MAX); 
SET @hexbin = 0xabcedf012439; 
SELECT '0x' + CAST('' AS XML).Value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)'); 
GO

Get number of days in a month using SQL

Use below code to get the number of days in a month, given Month and year.

DECLARE @MonthNumber AS VarChar(3), @YearNumber AS VarChar(4)
SET @MonthNumber = 'Feb'
SET @YearNumber = '2009'


SELECT datediff(day,CAST(('01-' + @MonthNumber + '-' + @YearNumber) AS DateTime),dateadd(day,-1,dateadd(month,1,CAST(('01-' + @MonthNumber + '-' + @YearNumber) AS DateTime)))) + 1