query for two database servers

Question

query for two database servers

James Tech

Right there with Babe

Points: 762
More actions
August 1, 2010 at 8:49 am

#238303

Hello,
I just want to ask for advice on what is the most efficient way to query two databases that are on separate servers?
Thanks for your time.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 1

You can setup a linked server between them, or you can use the OPENQUERY command from one server to get data from the other.

James Tech Right there with Babe Points: 762 More actions · Answer 2

Steve Jones - Editor (8/1/2010)
You can setup a linked server between them, or you can use the OPENQUERY command from one server to get data from the other.

Thanks for the suggestion Steve. So OPENQUERY is the efficient solution though, right? or the link server?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

James Tech (8/1/2010)
Steve Jones - Editor (8/1/2010)
You can setup a linked server between them, or you can use the OPENQUERY command from one server to get data from the other.
Thanks for the suggestion Steve. So OPENQUERY is the efficient solution though, right? or the link server?

OPENQUERY connects to the destination server and runs the query on that server and returns the resultset. Whereas, I believe the Linked Server query is executed on the local server and runs across the connection.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 4

Depending on what you do, OPENQUERY can be easier to write. If you do a straight join, I believe that the entire table from a linked server comes across and then is joined. However, if you can send a query across that pulls less data, that can be used.

James Tech Right there with Babe Points: 762 More actions · Answer 5

Steve Jones - Editor (8/2/2010)
Depending on what you do, OPENQUERY can be easier to write. If you do a straight join, I believe that the entire table from a linked server comes across and then is joined. However, if you can send a query across that pulls less data, that can be used.

I am actually trying to query a user reports.

My stored procedures are stored in database A that has the user information and I have to query the user report with a given date range to the other database server report table (database B) that contains lots of data (an average of 5 Million per year quarter).

Which do you think is the most appropriate to use given the screnario?

Thanks for your help guys.

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 6

I think I'd use OPENQUERY and send the data range across

select ...

from OPENQUERY( .., 'select dataIneed from other table where date > ' + @begin + ' and date < ' + @enddate + ' ')

James Tech Right there with Babe Points: 762 More actions · Answer 7

Thanks Steve. I just notice something, OPENQUERY as I tested it actually works but in the reports table, there is an address column of type varchar(50) that I need to retrieve. When I query locally on that server (database B) it can retrieve the address however, when I tried to query it from the other database server (database A), I can't retrieve the address instead it is giving NULL always.

Any thoughts?

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 8

How are you querying it? Same query? same permissions/user account?

varchar(50) shouldn't be an issue in terms of size/type.

James Tech Right there with Babe Points: 762 More actions · Answer 9

Steve Jones - Editor (8/2/2010)
How are you querying it? Same query? same permissions/user account?
varchar(50) shouldn't be an issue in terms of size/type.

Yes it is the same...here is the sample query.

SELECT

*

FROM OPENQUERY([192.168.1.12],'

SELECT

UserID,

FirstName,

LastName,

Address,

Status

FROM MyDB.dbo.Reports

WHERE UserID=1

')

But when I do this query inside database B, it can retrieve all columns.

SELECT

UserID,

FirstName,

LastName,

Address,

Status

FROM MyDB.dbo.Reports

WHERE UserID=1

seth delconte SSCertifiable Points: 6388 More actions · Answer 10

James Tech (8/2/2010)
Steve Jones - Editor (8/2/2010)
How are you querying it? Same query? same permissions/user account?
varchar(50) shouldn't be an issue in terms of size/type.
Yes it is the same...here is the sample query.
SELECT
*
FROM OPENQUERY([192.168.1.12],'
SELECT
UserID,
FirstName,
LastName,
Address,
Status
FROM MyDB.dbo.Reports
WHERE UserID=1
')
But when I do this query inside database B, it can retrieve all columns.
SELECT
UserID,
FirstName,
LastName,
Address,
Status
FROM MyDB.dbo.Reports
WHERE UserID=1

What is the datatype for the Address field on Server A? Varchar(50) also?

_________________________________
seth delconte
http://sqlkeys.com

James Tech Right there with Babe Points: 762 More actions · Answer 11

Hi guys, my bad..I was connecting to a different link server with no address data is present. This is okay now.

Thanks for your help guys. Really appreciate it.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

James Tech (8/2/2010)
Steve Jones - Editor (8/2/2010)
How are you querying it? Same query? same permissions/user account?
varchar(50) shouldn't be an issue in terms of size/type.
Yes it is the same...here is the sample query.
SELECT
*
FROM OPENQUERY([192.168.1.12],'
SELECT
UserID,
FirstName,
LastName,
Address,
Status
FROM MyDB.dbo.Reports
WHERE UserID=1
')
But when I do this query inside database B, it can retrieve all columns.
SELECT
UserID,
FirstName,
LastName,
Address,
Status
FROM MyDB.dbo.Reports
WHERE UserID=1

have you tried to run the query at destination server ? see address column returning values ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)