Any way to connect remote server table with out using Linked server in SQL server

OPENROWSET is one of the options to access remote data

 SELECT a.*
 FROM OPENROWSET('SQLNCLI', 'Server=RemoteServer;Trusted_Connection=yes;',
 'SELECT *
  FROM DB.Scehma.Table
 ') AS a;

As @robbin has said, you can use OPENROWSET [ MSDN ], but you can also use OPENDATASOURCE [ MSDN ]

 SELECT *
 FROM OPENDATASOURCE ('SQLNCLI', 
                      'Server=RemoteServer;Trusted_Connection=yes;')
                      .DB.Schema.Table

This question has already been answered, but I would like to point out that in order to use the OPENROWSET, OPENQUERY and OPENDATASOURCE, the 'Ad Hoc Distributed Queries' option has to be enabled. In order to even see the value of this specific option let alone to modify it the 'show advanced options' option has to be enabled. If you need to make sure that you can always use the distributed queries you can simply do this:

exec sp_configure 'Show Advanced Options', 1;
reconfigure with override;
go

exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure with override;
go

However, if any of these options are disabled per DBA requirements then you cannot really arbitrarily change the server settings. In this case, when querying the remote server is just a one time ordeal which must never happen again then you can use this script to modify the options as needed and then restore them to the original state if needed. Of course, it goes without saying that if I know that I must modify the settings for some one time job, I still let the DBA know about it in advance. Here is the script:

-- check the settings for Ad Hoc Distributed Queries and if the configured value
-- is 0 then change it to 1. This is one of the advanced options, so it can only
--  be viewed and modified if the Show Advanced Options is enabled. This means
-- that if the latter is not enabled yet then it has to be enabled now. Both of
-- the original values need to be stored in order to be able to restore them to
-- their original states if the original values were equal to zero. Because 
-- running reconfigure takes effect only after the batch is terminated, the 
-- GO is needed which means that local variables cannot be used to store the 
-- original values.

-- create temp table (2 columns 2 rows) to store the original config values
if exists
(
    select 1 
        from tempdb.sys.objects 
        where [object_id] = object_id('tempdb..#config_values')
)
    drop table #config_values;
go
create table #config_values (name nvarchar(35) not null, value int not null);
go

-- store the original advanced options and ad hoc queries settings
insert into #config_values (name, value)
select 
    name, convert(int, value)
    from sys.configurations
    where name in (N'Show Advanced Options', N'Ad Hoc Distributed Queries');

-- modify the advanced options setting if it needs to be modified
if exists (select 1 from #config_values 
    where name = N'Show Advanced Options' and value = 0)
begin
    exec sp_configure 'Show Advanced Options', 1;
    reconfigure with override;
end;

-- modify the ad hoc queries options setting if it needs to be modified
if exists (select 1 from #config_values 
    where name = N'Ad Hoc Distributed Queries' and value = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1;
    reconfigure with override;
end;
go

-- THE DISTRIBUTED QUERIES GO HERE

-- if the original setting was equal to zero then
-- it needs to be restored back to this value   
if exists (select 1 from #config_values 
    where name = N'Ad Hoc Distributed Queries' and value = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0;
    reconfigure with override;
end;

-- if the original setting was equal to zero then
-- it needs to be restored back to this value   
if exists (select 1 from #config_values 
    where name = N'Show Advanced Options' and value = 0)
begin
    exec sp_configure 'Show Advanced Options', 0;
    reconfigure with override;
end;
go

-- clean up, drop the #config_values temp table
if exists
(
    select 1 
        from tempdb.sys.objects 
        where [object_id] = object_id('tempdb..#config_values')
)
    drop table #config_values;
go

Oleg

You can use the 4 part query...here is an example

ServerName.DatabaseName.SchemaName.TableName..

Hope this helps...