The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported

Recently I have faced this problem while I am accessing Sqlserver from my application.

I got this problem because of Service Broker has been disabled for my database. By following below steps I have resolved my problem.

-- Enabling Service Broker --

Depending on how your model database is set up, Service Broker may be disabled by default when you create a new database. Or, you may want to enable it in a database that already exists but in which it is not enabled.

Service Broker is turned on or off on a per-database basis, and you can find out its current state for your database by querying the sys.databases catalog view.

Turning on Service Broker is easy enough, but there is a slight twist: there must either be zero users connected to the database, or you must be the only user connected, when you actually flip the switch. ALTER DATABASE can help us in that regard, but it doesn't change the fact that somehow, those users have to go.

--Let's find out if Service Broker is turned on. SELECT is_broker_enabled  FROM sys.databases  WHERE name = 'DataBase Name'

If result is 0 means your service broker not enabled, by running following query we can enable Service Broker.

ALTER DATABASE DatabaseName SET ENABLE_BROKER  WITH ROLLBACK IMMEDIATE

"Service Broker is a feature in Microsoft SQL Server, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server."

MSDTC on server 'servername' is unavailable

This error occurs when the “Distributed Transaction Coordinator” service is not running. To start a distributed transactions with TransactionScope object, the windows service “Distributed Transaction Coordinator” should be running.

To fix the problem just start the service “Distributed Transaction Coordinator” using Windows Service manager.

Here are the detailed steps for starting the service

1. Click on Ctrl+R then type services.msc in run command then click OK,Service Manager will display.
2. Scroll through the list and identify the service “Distributed Transaction Coordinator”
3. Right on the service and choose “Start”

How to Reset Identity column in SqlServer

In this post i explained how to reset the identity column in a table.

we have two methods to reset the identity column 

1) Using Truncate Method.

2) Using DBCC (database consistency checker)

But TRUNCATE TABLE cannot be used when a foreign key references the table to be truncated where as DBCC can.

here is the Query using DBCC

DBCC CHECKIDENT('Table Name', RESEED, 0)

In Above query '0' is the reset idetity seed number,you can give any number whatever you want like 20,30 etc..

Here is the Query Using Truncate 

truncate table TableName

Insert value from one Database Table to another Database Table

In this post i have explained how to insert value from one database to another database table.

i have created two tables one in master database another one in sample database

in master table EmpId is identity column,

Here i am getting identity(EmpId) value from Employee table of master database.

Here is the query.

use master

declare @EmpID int

insert into Employee(EmpName,DeptId) values('AAA',1)

set @EmpID=@@Identity

use sample

insert into EmpDetails(EmpId,Location,Email) Values(@EmpID,'Hyderabad','aaa@gmail.com')

Access Remote sql server’s database table

For accessing Remote SqlServer we need to add remote sqlserver to sys.servers

Syntax for accessing Remote SqlServer: 

select * from Servername.Databasename.dbo.Tablename

it will give following error

“Msg 7202, Level 11, State 2, Line 1
Could not find server 'ServerName' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
”
The syntax to add a remote sql server to sys.servers is:

execute sp_addlinkedserver sqlserverName

If you add your remote server to sys.servers then you can access remote server using following simple query.

select * from servername.databasename.dbo.tablename

If you try to access remote SqlServer without adding remote server to sys.servers