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

avatar image

mahi
116 12 12 15

(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

avatar image

robbin
1.7k 1 5 10

(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 ]

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



more ▼

answered Mar 07, 2012 at 10:02 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(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

avatar image

Oleg
16.9k 3 7 28

(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

avatar image

Kirman1
181 16 16 23

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.

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:

x738

asked: Mar 07, 2012 at 05:26 AM

Seen: 1125 times

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

Copyright 2016 Redgate Software. Privacy Policy