Extracting data from multiple sql servers in different domains

Question

Post reply

Extracting data from multiple sql servers in different domains

sqllearner-339367

Hall of Fame

Points: 3004
More actions
May 8, 2009 at 2:08 am

#99687

Hello everyone,
we have 2 sql servers installed in different domains and will be writing a stored procedure to extract data from one server to another. As a trail execrises I wrote a dummy query with fully qulaified name to extract data but I was unsuccessful. Could some one point me how to do data extraction from multiple sql servers? I have googled but was unsuccessful. Any help is good.
ta

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Did you try to connect to remote server via IP instead of the name?

Greets

Flo

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

What errors do you get when executing the queries?

Did you create a linked server to connect to the remote server?

Here is sample script to create a linked server:

DECLARE @strLinkedServer NVARCHAR(100)

SELECT @strLinkedServer = 'North'

EXECUTE master.dbo.sp_dropserver

@server = @strLinkedServer,

@droplogins = 'droplogins'

EXECUTE master.dbo.sp_addlinkedserver

@strLinkedServer,

'SQL Server'

EXECUTE master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = @strLinkedServer,

@useself = N'True',

@locallogin = NULL,

@rmtuser = NULL,

@rmtpassword = NULL

EXECUTE master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = @strLinkedServer,

@useself = N'False',

@locallogin = N'sa', -- add local login

@rmtuser = N'Admin', -- add the remote login

@rmtpassword = NULL -- add the remote login password

-- Now verify the remote connection by executing the below query

SELECT * FROM North.master.dbo.sysobjects

--Ramesh

sqllearner-339367 Hall of Fame Points: 3004 More actions · Answer 3

I have tried using Ip address but im not sure how to use them in query? Is it possible to give an example to write a basic query using Ip address in fully qualified format?

I have tried using below format

Select * from [192.168.100.5].Database.Table

Ta

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Err... You cannot directly select from your remote server without any registration or using an ad-hoc query method.

* One way (as Ramesh showed) is create a linked server. Search BOL for sp_addlinkedserver

* Another way would be a ad-hoc request via OPENROWSET or OPENDATASOURCE

Greets

Flo

sqllearner-339367 Hall of Fame Points: 3004 More actions · Answer 5

I certainely thought about creating linked server but I wasnt sure. I will try both the options mentioned in the post. Thanks to everyone who has replied to this post.

Ta