cross server SQL queries

[columbo2]

Hello All,
Does any one know how to do cross server queries?
I have Enterprise manage setup with several servers and I want to run a query that uses tables in searate databases on separate servers.

e.g.

select * from db1_on_server1
inner join db2_on_server2

I've tried [ipaddress].[databasename].[tablename]
and
[server name].[databasename].[tablename]
etc
but it doesn't want to know.
ANybody got any ideas.
Thanks for reading this
ta
C

 

[arrowhouse]

You need servername.databasename.owner.table you are missing out the owner bit. Have never tried the IP address so I'm not sure how sql will handle that.

If it still doesn't work can you post the error?

I presume the setting up of the linked/remote servers has already been done. If not you will need to set that up as well.

 

[mutley1]

You need to specify the full path including database owner.

Either:

1. select BLAH from foreignserver.database.dbo.table

or

2. select BLAH from foreignserver.database..table

(double period = .dbo)
HTH,

M.

 

[LFCfan]

I'm a bit rusty on SQL Server, but I believe you need to look into linked servers. They are in the Security bit of Enterprise Manager. I seem to remember that in order to do servername.databasename.owner.table... you need to set up linked servers if you're going across servers. My apologies if I'm wrong - but it has been almost a year since I have done any SQL Server work at all

_{~LFCfan
who is gnawing on the knowledge}