How do I reference a table on a different server

[bigfoot]

This may seem dumb but I can't seem to do this.<br>
<br>
On 7.0 the syntax is:<br>
<br>
select * from servername.dbname.dbo.tablename<br>
<br>
How do I do this on 6.5??<br>
<br>
Thanks

 

[databarn]

bigfoot,<br>
<br>
I've done this via ODBC from an ASP application, but don't know that there is a direct way. Mind you, I'm not overly conversant with things SQL Server - the ODBC calls I was making were mostly to Oracle instances, but they were tying back to a local instance of SQL Server, and one of the calls was to a 6.5 instance of SQL Server.<br>
<br>
I do recall that I had a problem 'seeing' the other SQL Server until I entered it into my local hosts file. DNS resolution just didn't work, but once I put the server reference in the hosts file I could hit it every time. Maybe the same work for you?<br>
<br>
Make a good day . . .<br>
. . . barn<br>
<br>
<br>

 

[carle]

On 6.5 this must be done via a stored procedure:<br>
In the sp on the calling server you whould use:<br>
<br>
exec NEWSERVER.master.dbo.sp_who<br>
<br>
Which would run sp_who (or any sp) on the NEWSERVER. <p>Carl Erickson<br><a href=mailto:carl.erickson@newsedge.com>carl.erickson@newsedge.com</a><br><a href=

http://www.shore.net/~carle>Computer

Consulting Services</a><br>

 

[jnicho02]

I seem to remember something like sp_addserver that adds the remote server to a list held on your server. This is part of replication.<br>
After doing this you should be able to do:<br>
select * from servername.dbname.dbo.tablename<br>
<br>
If anyone else knows about this please post the correct commands (or maybe I am just plain wrong!) I shall check my 6.5 book at home as I only have 7.0 at work.

 

[phili5678]

there is something called the sp_addlinkedserver.. then u can use openquery to select from another server.. hope it helps..

 

[bigfoot]

Found it. Thanks all.