Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mayub
Starting Member
1 Post |
 Posted - 2009-05-12 : 02:28:34
|
| Hello All,I have 3 SQL server 2005 instances. That is there are 3 logins for the same server. I can login as Sysadmin, or as A or as B. The passwords are the same for all. A and B are also databases that are configured so that they cannot access each other. My problem is that I need to run select queries that access data from tables in both A and B in a single select statement. I tried creating a linked server by logging in as SA, but using the linked server name in the select query resulted in errors such as "default settings do not allow remote connection" or "the server name does not contain the table".I am not sure whether linked servers can be created for databases that are on the same server. Is there a way that I can run select statements accessing data from 2 different databases on the same server? Thanks for any help! |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-05-18 : 14:37:50
|
| If there are on the same server and your login has rights to, at a minimum, read the data use the 3 part naming convention: select A.fielda, B.fieldb from databaseA.schemaowner.tableA A join databaseB.schemaowner.tableB B on A.fielda = B.fieldbIf you supply the DDL and sample data, a somewhat more accurate query can be provided.Terry-- Procrastinate now! |
 |
|
|
|
|
|
sp_addlinkedserver