x

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

Hi all,

Is there any other way that we can connect tables in other sql server with out using linked server?

If yes please advice , i don't want to use inked server .
more ▼

asked Mar 07, 2012 at 05:26 AM in Default

mahi gravatar image

mahi
116 9 12 13

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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;
more ▼

answered Mar 07, 2012 at 05:54 AM

robbin gravatar image

robbin
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left

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

 SELECT *
 FROM OPENDATASOURCE ('SQLNCLI', 
                      'Server=RemoteServer;Trusted_Connection=yes;')
                      .DB.Schema.Table
[2]: http://msdn.microsoft.com/en-us/library/ms179856.aspx
more ▼

answered Mar 07, 2012 at 10:02 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 07, 2012 at 04:01 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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

ServerName.DatabaseName.SchemaName.TableName..

Hope this helps...
more ▼

answered Mar 08, 2012 at 08:45 AM

Kirman1 gravatar image

Kirman1
181 14 15 18

For that you would need the linked server which is not the OP's requirement.
Mar 08, 2012 at 08:59 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x595

asked: Mar 07, 2012 at 05:26 AM

Seen: 949 times

Last Updated: Mar 08, 2012 at 08:59 AM