Remote tables

Hi everybody:

I am working on a query that referrences a table in a remote database. It seems in Management Studio 2005 anytime you open a new query window you should connect to a specific database instance that's why when I refer to the remote database table using Fully Qualified Name it tells me the database name is unknown.

Do you have any solutions for this?

Thanks a lot

Which qualified name did you use ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

[server].[database].[owner].[table]

|||Hi,

if you connect to a database / server you can reach any (linked) server and therefore remote database that are setup on the server machine. You don′t need to specify the remoteserver at connection time, that why you can specify it the four part name:

[server].[database].[owner].[table]

But the [server] has to be a linked server. If you don′t know how to setup, look in the BOl there are some good straight forward examples for it.

HTH, jens Suessmeyer.

http//www.sqlserver2005.de

|||

You can try with OPENROWSET or OPENQUERY function. To do this you need to enable this features from "SQL Server Surface Area Configuration" - Ad Hoc Remote Queries (you need to check "Enable OPENROWSET and OPENDATASOURCE). After you enable this option you can use this function to retrieve data from another server or to get data from another format (including MS Excel - for example).

SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;', 'SELECT GroupName, Name, DepartmentID FROM AdventureWorks.HumanResources.Department ORDER BY GroupName, Name') AS a;