Sql Server Open Rowset

Another  future of Sql Server a part from linked server to access data from another instance of sql server  is to use the OpenRowset function  ,this future allows for accessing data from another instance of sql server  , but requires  you to specify the connection details every time when you want to use to .

For accessing the data using OpenRowset function sql server needs to configure advanced options and than enable adhoc distributed queries as shown below

it is important to note that, there should be only  single space between each word of  Ad Hoc Distributed Queries  or else the query will fail

Configuring Linked Server in SQl Server 2012

Accessing remote data is  one of the common  tasks  when working on a data related job in sqlserver,linked server is a future in sql server which enables to add the remote sql servers to a local server and makes easy  easy to use a user defined server with  the four part naming convention to query data .

To set set linked server you need to go to server objects  and linked server as shown below

The next step is to right click the linked server and create a new linked server
and fill the fields as necessary  as shown below

As shown below you need to select

1. Other Data Source
2. Provider=SqlNative Client(Sql Server)
3. ProductName= SqlServer
4. DataSource=Name of the remote instance

other fields are optional

In the next step you need to provide the security context for this server,my servers are on the same domain so i have selected  the login under current user context but u can also give remote login details on the final optionas be made under a different security context