Database Simplified Headline Animator

Database Simplified

Sunday 25 December 2011

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Collation conflicts are encountered when u try cross database query or cross server query or joining tables, because joining tables might have different collation settings. You must handle these query with specifying appropriate collation while joining to tables having different collation data. Let Replicate the issue with following example.

Create following tables with script given below.



CREATE TABLE Department_Branch
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](40) NULL,[NoOfBranch] TinyInt NOT NULL
)
GO
Insert Into Department_Branch Values('IT',40),('BIOLOGY',23),
('FINANCE',21),('MEDICAL',12),('TRANSPORT',5)
Go

CREATE TABLE [dbo].[Department_Account](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](60) Collate SQL_Latin1_General_CP1_CI_AI NULL,
[TotalSalary] [decimal](18, 0) NULL,
[TotalEmployee] [int] NULL
)
GO
Insert Into Department_Account Values('IT',5700000,76),('BIOLOGY',8900000,290),
('FINANCE',4500000,900),('MEDICAL',560000,17),('TRANSPORT',700000,15)
Go

Here you have two tables Department_Branch with column DepartmentName (has default collation “SQL_Latin1_General_CP1_CI_AS” and Department_Account with column DepartmentName with  Collation “SQL_Latin1_General_CP1_CI_AI”. Now Let join these to table and check the output.

Select A.DepartmentName,A.NoOfBranch,B.TotalEmployee,B.TotalSalary
From Department_Branch A
Join Department_Account B On A.DepartmentName=B.DepartmentName
Go

Output :

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Now to resolve above error you must specify collation.



Select A.DepartmentName,A.NoOfBranch,B.TotalEmployee,B.TotalSalary
From Department_Branch A Join Department_Account B
On A.DepartmentName=B.DepartmentName Collate SQL_Latin1_General_CP1_CI_AS
Go

Output :

image

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.

Inserting explicit value for identity column in a table can not be done directly as identity column is bound to rule of incrementing them with specified number. If you try to insert value explicitly you encounter following error message.

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So when you need to insert explicit value to identity column in a table you must set IDENTITY_INSERT ON for that table. Lets see following example.

Create table with following script.



Create Table Department_Master
(
Id Int Identity(1,1),
DepartmentName Varchar(60),
IsActive bit default 1,
CreatedOn Datetime Default Getdate()
)
Go

I have Department_Master where “Id” column is an identity column which automatically inserts values into this column. Lets see what happens when you try to insert ID column value explicitly.

Try Following Insert Statement.



Insert Into Department_Master(Id,DepartmentName,IsActive,CreatedOn)
Values(1,'Biology',1,GETDATE())
Go

Output :

image

You can see the output.

Now to overcome this issue you must set Identity_Insert On for table specified. Let do that and check insert.



Set Identity_Insert Department_Master On
Go
Insert Into Department_Master(Id,DepartmentName,IsActive,CreatedOn)
Values(1,'Biology',1,GETDATE())
Go
Set Identity_Insert Department_Master Off
Go

Here I have set Identity_Insert On Before inserting the values and set Identity_Insert off after inserting the value. So you Set it off as next values should come automatically as defined in Identity Rule for that column.

Also you must note that in a database you can only set Identity_Insert On for a single table only. If you try to set Identity_Insert On for more then one table you might encounter following error.

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'TableName’. Cannot perform SET operation for table 'TableName'.

So Make sure you set Identity_Insert Off once you have inserted Id value explicitly by setting Identity_Insert On.

Monday 5 December 2011

Nested Transaction In SQL Server

SQL Server facilitate us to define nested explicit transaction. We may need to define nested transaction in many situation, so before you define or use nested transaction you must be aware of behavior of nested transaction.

Create tables from following script to perform following tests.



Create Table Parent_Table(Id int Identity(1,1),DataValue Varchar(20))
Go
Create Table Child_Table(Id Int Identity(1,1),DataValue Varchar(20))
Go

This example explains that rolling back outer transaction will rollback all the inner transaction irrespective of whether those were committed or rolled back.

Start A Transaction and call it parent transaction



Begin Transaction Parent

Insert Data Into Parent Table



Insert Into Parent_Table Values('Parent')

Start another transaction and call it child transaction



Begin Transaction Child; --Start Child Transaction

Insert Data into Child Table



Insert Into Child_Table Values('Child')

Now Commit Child Transaction



Commit Transaction Child

Now Rollback  Parent Transaction



Rollback Transaction Parent

So in above code steps we have committed the child transaction and rolled back the parent transaction which implies that data should be inserted into parent table but here this is not the case rolling back outer transaction will rollback all the inner transaction (even if it was committed). So this is quite important and must be kept in mind while working or designing nested transaction. You can use following query to see data in parent and child table and you will see that no data exists in both the tables



Select * From Parent_Table
Go

Select * From Child_Table
Go

Friday 2 December 2011

How To Move TEMPDB In SQL Server

In this blog post we will see how can we move location of tempdb database files in sql server. Moving of database may be required for many reasons like for security purpose or space constraints or etc. As tempdb is system database it cannot be detached or attached and cannot be restored like user databases. You need to be more alert when you are planning to move tempdb database files.

Let’s see how.

Step -1 :

Check Current Location Of Tempdb Files.


Use Tempdb
Go
Select * From SysFiles
Go

Output :

image

You Must Note Down These Path as you need to copy MDF and LDF files to new location later on.

Step – 2:

Move Tempdb Database File locations



Alter Database Tempdb
Modify File(Name='Tempdev', FileName='D:\Tempdb.MDF')
Go


Alter Database Tempdb
Modify File(Name='templog', FileName='D:\Templog.LDF')
Go

Above two queries moves location of MDF and LDF files. See Following screen shot for more clarification.

image

Step – 3 :

Now You have changed the location of files but to activate new location you must restart the SQL Server instance Service. Before you start SQL Server Instance you need to do following 2 steps.

Verification of new file locations.

image

So we can check that new filenames or paths are correct if you think the path or filename is incorrect then you can run above queries to move files again with correct filename or path and check.

Step – 4

Once you have verified the new locations you need to move tempdb files ( Data and Log File) to new location. So you can copy tempdb data and log file i.e. (MDF and LDF) file from OLD Location to New Location.

image

Step – 5

Now after moving the files you need to restart SQL Server Services. Until You restart SQL Will use old tempdb files.

Saturday 26 November 2011

Memory Utilization In SQL Server (Database Level)

Its common question for DBAs to find memory consumption by each database. With SQL Server 2000 its was quite difficult to find but with SQL Server 2005 + we have DMV (Dynamic Management Views)  “sys.dm_os_buffer_descriptors” which provides memory utilization by each database or table in database.

So let’s use the DMV to find current memory utilization by each database.

Before we start this memory test you need to create a test table and a database with following script.



Create Database DBMemoryTest
Go
Use DBMemoryTest
Go
Create Table TblMemoryTest(Id Int Default 0,NameValue Char(8000) Default 'Mem Test')
Go
Insert Into TblMemoryTest Default Values
Go 20000

Now you have 20000 records in TblMemoryTest Table. Lets check the data space of the table.



exec sp_spaceused 'TblMemoryTest'
Go
Output :

image

Data : = 160000 KB Means 160000/1024 = 156 MB Around. So it means that when I use select * on this table without any where condition then it must eat up memory upto 156 MB. Lets Test It.

Clear Buffer Cache and Procedure Cache



DBCC FREEPROCCACHE
Go
DBCC DROPCLEANBUFFERS
Go

Now run following query to find memory utilization by each database.



SELECT DB_NAME(database_id) AS DatabaseName, (COUNT(*) * 8)/1024.0 AS [Size(MB)]
FROM sys.dm_os_buffer_descriptors
Where Database_Id<>32767
GROUP BY DB_NAME(database_id)

Output :

image

Now run select statement in TblMemorycheck table.



Use DBMemoryTest
Go
Select * FRom TblMemory Test
Go

And now again run query to find memory utilization.



SELECT DB_NAME(database_id) AS DatabaseName, (COUNT(*) * 8)/1024.0 AS [Size(MB)]
FROM sys.dm_os_buffer_descriptors
Where Database_Id<>32767
GROUP BY DB_NAME(database_id)

Output :

image

You can clearly see that database DBMemoryTest is showing 157 MB of memory utilization which was not there in previous run of query.

Thursday 24 November 2011

Move Database In SQL Server With Minimum DownTime

When working with very large database and there is need to move database from one server to another, then downtime is always an issue in such cases. There are several ways to move database from one location to another. Here I am describing an efficient way to move database from one location to another location. Following option only works if database recovery modal is Full or Bulk Logged. Let’s see how to move database in following scenario.

Suppose database needs to be moved from Server-1 To Server-2, Total size of database is 50 GB or More. Database is online at Server-2.

Step 1 : Take Full Backup Of Database

Step 2 : Move Full Backup Of Database to Server – 2

Step 3 : Restore Database At Server – 2 With No-Recovery Option

---Down Time Starts Here

Step 4 : Disconnect All The User From Server – 1 Database. Make sure no user is accessing the database at Server – 1.

Step 5 : Take Transaction Log Backup of Database

Step 6 : Move Transaction Log Backup To Server – 2

Step 7 : Restore Transaction Log Backup At Server 3 With Recovery Option

Step 8 : Finish and Your Database is Up and Running at Server – 2.

So as you can see that down time start only when there is only a log backup pending to be restored, Since log backups are small in size thus can be moved and restore with small amount of time.

Thursday 10 November 2011

Database Backup At Multiple Location Using Mirror In SQL Server

SQL Server 2005+

At times we come across issue where we need multiple copies of database at different places. Many people end up taking single backup of database and making required copy of backup and moving them to required location using some cmd or script. But now this can be achieved using MIRROR with backup command. Mirror option allows you to create maximum 4 copies of database backup at a time.

Create Database with Following Script.



Create Database MakeMultipleCopies
Go

Take Backup With Mirror Option.



Backup Database MakeMultipleCopies To Disk='D:\Copy1.Bak'
Mirror TO Disk ='E:\Copy2.Bak'
Mirror to disk='C:\Copy3.Bak'
Mirror to disk='D:\Copy4.Bak'
With Format

Output :

image

As I mentioned earlier we have max limit with mirror we cannot use more then 4 backup mirror. Let’s try it and see what happens when we use 5th mirror. See Following Screen.

image

So, You can see that the error message clearly says only 4 are allowed. So in all its really cool to have multiple backup with single backup statement without putting effort of copy and pasting it manually or using scipt or command.

Wednesday 9 November 2011

How To Get Comma Separated Value In SQL Server.

In the earlier post we have seen use of xml path to get comma separated result. This post describes how to get comma separated value from a result set or a table data.

Create Table With Following Script.



Create Table CSData
(
Id Int Identity(1,1),
DepartmentName Varchar(40)
)
Go

Insert Into CSData(DepartmentName) Values('IT'),('FINANCE'),('MEDICAL'),('TRANSPORT'),('TALENT TRANSFORMATION'),('IMG')
Go

You have following data.

image

Now we need comma separated departmentnames.

image

So its quite easy to have comma separated list. I have used stuff to removed first comma from the result.

The media family on device is incorrectly formed. SQL Server cannot process this media family

This blog post deals with the problem of restoring SQL Server Database from higher version to lower version. We generally encounter error following error.

Error while restoring database from SQL server 2008 to SQL server 2005

Msg 3241, Level 16, State 7, Line 1

The media family on device ‘<backup path>’ is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

So, There is option where you can restore database from higher version to lower version.

  • Generating Script With Database : This feature enables you to create version compatible script of database. you can also generate script of database along with structure. See following screen shot step by step.

On Object Explere :  Right Click Database –> Tasks —> Click Generate Script

image

It opens a wizard for you which begins with introduction page which provides quick brief info about script wizard.

image

Click Next to go to next page.

image

In this page you can choose specific object of database for those you want to generate script. since we need complete data. So will choose option “Script entire database and all database objects”

image

This page is scripting option page. In this page you can provide file name and path where script will be saved. You have multiple option as you can save script to a file or copy the script to a clipboard or directly move it to query window. In Order to generate script with data and lower version compatible script you need to set some option in advance option. See following screen.

image

In advance scripting option you can define “Type of data to script” I have chosen “Schema and Data”  and Script for Server Version : you have Option from SQL Server 2000 to SQL Server 2008 R2. So You just need to select required version and click ok and press next on parent screen.

image

On clicking next you get summary screen which tell you about the options you have selected for generating script it just kind of verification and cross checking and now click ok and you are done with scripting.

image

Green symbols are indication that script has been generated without any warning or error. Now you can click finish and run you script in your target server.

 

Note : If you have VLDB ( Large Size Database) then you can use BCP. In order to move data.

Monday 7 November 2011

How to find list of stored procedure with content info in SQL Server

This post describes the how can you find list of stored procedure with specific text content or how to answer following question.

  • List All Stored Procedure, Function, Views, Triggers which uses a Update or Insert or Delete Statement.
  • List All Stored Procedure, Function, Views, Triggers which uses temporary table.
  • List All Stored Procedure, Function, Views, Triggers which uses a particular table.
  • and Many More.

There is a system table which contains information about stored procedure,view and trigger contents “syscomments

List All Stored Procedure, Functions, Views, Triggers which uses “Update, Insert or Delete Statement



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text Like '%update %' or text like '%insert %' or text like '%delete %'

List All Stored Procedure, Functions, Views, Triggers which uses temporary table.



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where Replace(Text,' ','') Like '%Table#%'

List All Stored Procedure, Functions, Views, Triggers which uses a particular table.



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Sys.Objects%'

List All Stored Procedure , Functions, Views, Triggers which contains a specific text



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Data%'

So “syscomments” is really helpful.

NOTE: Searching cannot be done when stored procedure function or View Are Encrypted.

Find Tables With Column Name in SQL Server Databases

This post describes how one can find list of all the tables where a column with a particular name exists in a databases. Many people come across this issue and searches for query. So here is the query with example.

I am using following query to find out all the table having name “Status” in master database.



Use master
Go

Select B.name as TableName, A.name As ColumnName,C.name as DataType
from Sys.columns A
Join Sys.objects B On A.object_id=B.object_id
Join Sys.types C On A.system_type_id=C.system_type_id
Where A.name='Status'--ColumnName
Go

Output :

image

So the above query lists all the tables having column with name “status” and their datatype.

Wednesday 2 November 2011

Is Data Always Sorted On Primary Key (Clustered Index) In SQL Server ?

Whenever we create a primary key on a table and we generally get data sorted on primary key defined. but that’s not always the case. Let’s examine it.

Create Table and Populate Data With Following Script



If Exists(Select * From sys.Objects where object_id=object_id('Records'))
Drop Table Records
go


CREATE TABLE [dbo].[Records](
    [Id] [int] IDENTITY(1,1) Primary Key  NOT NULL,
    [EmpName] [varchar](20) NULL,
    [AccountNo] [varchar](12) NULL,
    [IsActive] [tinyint] NULL,
    [Salary] [decimal](22, 2) NULL,
    [Department] [varchar](50) NULL
)
GO

INSERT [dbo].[Records] ([EmpName], [AccountNo], [IsActive], [Salary], [Department])
VALUES
('Kuldeep Bisht', N'012398760987', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Vishal Verma', N'987026749876', 3, CAST(6700.00 AS Decimal(22, 2)), N'BPO'),
('Rakesh Kumar', N'876894326754', 1, CAST(4500.00 AS Decimal(22, 2)), N'TESTING'),
('Sandeep Verma', N'786798564329', 1, CAST(9800.00 AS Decimal(22, 2)), N'IT'),
('Revinder Singh', N'999867543987', 1, CAST(7850.00 AS Decimal(22, 2)), N'TESTING'),
('Seema Singh', N'876549879898', 2, CAST(4560.00 AS Decimal(22, 2)), N'BPO'),
('Ashutosh', N'785640987567', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Devesh Gupta', N'776623458123', 2, CAST(6600.00 AS Decimal(22, 2)), N'TESTING'),
('Satinder Negi', N'768098723456', 1, CAST(3500.00 AS Decimal(22, 2)), N'BPO'),
('Meenakshi', N'009812346785', 1, CAST(9800.00 AS Decimal(22, 2)), N'BPO'),
('Manoj Pandey', N'767689900145', 3, CAST(7800.00 AS Decimal(22, 2)), N'IT'),
('Sanjay Rana', N'980765430974', 1, CAST(8800.00 AS Decimal(22, 2)), N'SALE')
Go

Since we have primary key on Id so data is sorted on Id columns when we pass select query.You can see in screen shot below.

image

Now Let’s Create a Non Clustered Index on Department Column



Create NonClustered Index NCI_Records_Id On Records(Department)
Go

Now pass select query on Table Again. You Get the Same Result.

image

 

But Now Let’s just select 2 columns Id and Department and see the resule.

image

Interestingly you can see that even after having Primary Key (clustered index) on Id  columns data is being sorted on non-clustered index. You must take care of this with using proper order by clause whenever sorting of data is required. Do not just rely on Primary Key.

Saturday 29 October 2011

Policy In SQL Server

SQL Server 2008 +

Policy based management introduced in sql server 2008 is really a nice feature. Where DBA can manage certain activity by enforcing policy based on specified conditions. You can enforce policy at database level.

How To Create Policy ?

The following step by step shows creation of policy for a stored procedure name standard.

Create a condition for policy

image

Create A Condition Expression

image

Facet : Stored Procedure, Field = @Name and Value=’Usp_%’, It Means that any stored procedure name must start with “Usp_” If it does not starts with Usp_ then user won’t be able to create that stored procedure.

Now Condition has been created, Next step is to create policy on condition create above.

How To Create Policy ?

image

Define Policy

image

Here "Pol_Create_Stored_Procedure”  has been defined with condition we created above which will be targeting all the databases and evaluation mode has been set to On Change prevent. these evaluation mode depends on facet we define in condition. Following is the list of evaluation mode.

On demand - The policy is evaluated only when directly ran by the administrator.
On change: prevent - DDL triggers are used to prevent policy violations.
On change: log only - Event notifications are used to check a policy when a change is made.
On schedule - A SQL Agent job is used to periodically check policies for violations.

Now Lets Try to Create a Stored Procedure

image

You can see the above screen shot where I tried to create stored procedure “Sp_GetData” which doesn’t pass the policy and resulted in batch aborted.

Now Let Try to use Usp_

image 

So you saw that you can enforce policy to following standard naming conventions. There are many facets on which condition and policy can be defined.

Friday 28 October 2011

Create SNAPSHOT In SQL Server

SQL SERVER 2005 + (Only Enterprise Edition) 

SNAPSHOTS are read-only views of actual database of a  particular time and are really useful for sharing load for reporting purpose. You can have multiple snapshot against a single database.

Working of SNAPHOT ?

Suppose you have created SNAPHOT of database at 9:00 AM. You will see that (.ss) snapshot file size is very small initially and as you modify actual database data, the size of this (.ss)  file increases. This is because whenever user modifies actual database record the older records (only First time since snapshot was created) are moved to snapshot database file (.ss). This is done to maintain SNAPSHOT State. In our case it is 9:00 AM

So one must keep this in mind that if you are planning to have multiple snapshot then it may be an overhead and come up as performance bottleneck.

How To Create SNAPHOT ?

Syntax :



Create database <SnapShotName> On
(Name =<ActualDatabase Data FileName >, FILENAME='.ss File Path')
As SNAPSHOT OF <ACtual Data Name>

How To Check Actual Database Data File Name ?

image

Example : To Create SNAPSHOT



Create database CDB_SNP On
(Name =CDB, FILENAME='D:\CDB_NSP_Data.ss')
As SNAPSHOT OF CDB
Go

As You Can Create Multiple SNAPSHOT



Create database CDB_SNP2 On
(Name =CDB, FILENAME='D:\CDB_NSP_Data2.ss')
As SNAPSHOT OF CDB
Go

 

See Your SNAPSHOT In SSMS Object Explorer

image

Password Protected Backup In SQL Server

Password protected database backup : Setting backup media password are important and useful for securing misuse of data. If database backup is password protected then one will not be able to restore and see details from backup file using RESTOREFILELIST or HEADERONLY command.

How to take password protected database backup?

Create A Database



If DB_ID('DBS_Test') Is null
Create Database DBS_Test
Go

Backup Database Backup With Password



Backup Database DBS_Test To Disk ='D:\DBS_Test.BAK' With MediaPassword='password'
Go

Drop The Existing Database



Drop Database DBS_Test
Go

Try To Get FileList Info From Backup



Restore FILELISTONLY  From Disk='D:\DBS_Test.BAK'

Output :

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Throws error since no password has been supplied with query.

Try To Get DB Info From Backup



Restore HEADERONLY  From Disk='D:\DBS_Test.BAK'

Output:

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

Same as above throws error since no password has been supplied with query.

Now Try To Restore Database With Backup File



Restore Database DBS_Test From Disk='D:\DBS_Test.BAK'

Output:

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

So, you have seen that no details or info can be taken from password protected database backup.

How to Get Info and Restore Password Protected Database Backup ?

Get File List Info From Password Protected Database Backup



Restore FILELISTONLY  From Disk='D:\DBS_Test.BAK'
With MEDIAPASSWORD='password'
Output: See Here

Get Database Info From Password Protected Database Backup



Restore HEADERONLY  From Disk='D:\DBS_Test.BAK'
With MEDIAPASSWORD='password'

Output : See Here

Restore Database From Password Protected Database Backup



Restore Database DBS_Test From Disk='D:\DBS_Test.BAK'
With MEDIAPASSWORD='password'

Output :

Processed 168 pages for database 'DBS_Test', file 'DBS_Test' on file 1.
Processed 2 pages for database 'DBS_Test', file 'DBS_Test_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.214 seconds (6.197 MB/sec).

Get Information From Backup File In SQL Server

This post describes how can we get information from backup file without restoring it. information about file groups, data and log files and Database information like server name and user name, internal database version, backup size and etc.

Backup A Database



Backup Database CDB To Disk='D:\CDB_Full.Bak'  With MediaName='CDB_FullBackup'
Go

Now we have a backup file located at D:\CDB_full.Bak, With this file following query retrieves information about database Name,UserName, ServerName and Backup Date and etc.



Restore HEADERONLY  From Disk='D:\CDB_Full.BAK'
Go

image

Now let’s get filegroup and file info from backup



Restore FILELISTONLY From Disk='D:\CDB_Full.BAK'
Go

image

Its really useful when you want to have information about a backup file without restoring it.

Wednesday 26 October 2011

Configuring Database Mail In SQL Server Step By Step

To Configure Database Mail you must create database mail account and profile.Following are the steps to configure Database Mail Account And Profile.

image

image

image

image

image

image

image

image

image

image

Once Account and Profile has been created you must configure database mail using sp_configure stored procedure

exec sp_configure 'show advanced options',1
Go

Reconfigure
go

sp_configure 'Database Mail XPs',1
go

Reconfigure
go

Now the database mail configuration is done. Lets send a test mail.

 

image

image

Or you can use following query to send database mail.



EXEC msdb.dbo.sp_send_dbmail @profile_name=’Demo Profile’,
@recipients='kuldeep@dbsimplified.com',
@subject='Demo Mail Sent Using T-SQL Query',
@body='This is test email sent from database mail using T-SQL Query’
 

image

image

Create User In All The SQL Servers

I have found many people questioning on MSDN forum “How to run a query or create a user in all sql server instances” and the answer is using Central Management Server (Registered Servers). see following example of creating a user in all the sql server instances.

I have following three instances of SQL where we need to create a user using registered servers.

image

 

Goto Menu : View—>Registered Servers

image

You will have registered servers pane on your screen. See following.

image 

You see a “Local Server Groups” folder under Database Engine. You can add all your instances under this group see following how to add ?

Right Click On Local Server Group and Click New Server Registration

image

You will get following connection dialogue. Using the following dialogue you can register the sql server instance under the local server group. You can register multiple instances of SQL Server or The number of instances on which you want to run a query.

image

Now after registering the sql server instance you need to open a query window for these registered servers see following.

Now right click on Local Server Group and choose New Query.

image

 

Now Create Database in all the instances using following query.



Create Database CommonDb

Output :

Command(s) completed successfully. (3 servers)

It means Query is successfully executed on 3 server.

Now Create a User in all the instances



CREATE LOGIN [CommonUser] WITH PASSWORD=N'Admin@123'

Output :

Command(s) completed successfully. (3 servers)

 

So, Central Server management is really useful and nice feature when you are working with multiple and large number of sql instances.