Query a table from other databases, same server.

[bjswift]

I am writing a query of data from a couple of tables on the same database, on a particular server. Now, I am to add in some data from another table, on a different database, but the same server. How can I reference that join? I tried setting it up such as server.databasename.tablename but that didn't help.

Any insight?

Thanks

[Shuja Ali]

I am writing a query of data from a couple of tables on the same database, on a particular server. Now, I am to add in some data from another table, on a different database, but the same server. How can I reference that join? I tried setting it up such as server.databasename.tablename but that didn't help.

Any insight?

Thanks
If you are talking about the SQL Server, then you could use DatabaseName.DBOwner.TableName to access the tables from a different database residing on the same server. And make sure the UserID that you are using has got access to that database.

Just open Query analyzer and select pubs and write this query Select * From Northwind.dbo.Orders

[bjswift]

Thanks. I believe I had my syntax mixed up. I was doing owner, database, then table.

[pc_newbies]

Sorry to interrupt, but I'm doing the same thing here. With DbOwner, do you mean the account name in which the database is created?

[Shuja Ali]

Sorry to interrupt, but I'm doing the same thing here. With DbOwner, do you mean the account name in which the database is created?
Usually all the databases are created by the database owners. It doesn't matter what the UserID is. If you can create a database then that means that your are a DBO. The user can create a database/table only when he has been added to the database owners group on the server.

[fangfoo]

You can also reset the dbOwner (often dbo):

sp_changeobjectowner [@objname =] 'object', [@newowner =] 'owner'

or

USE object
GO
EXEC sp_changedbowner 'newowner'
GO


And you can find out who it is by right clicking and bringing up properties on any table or using the query command.

Here is a decent KB article on SQL 2000 security and roles:

http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c05ppcsq.mspx

And some undocumented stored procedures:

http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm