Problem joining tables from different databases

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem joining tables from different databases

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-13 : 05:07:21

SELECT dbMembership..tblMatrixUserAdditional.Username, dbMembership..tblGenusFranchiseDetails.Company
FROM dbMembership..tblMatrixUserAdditional JOIN dbMembership..tblGenusFranchiseDetails ON
dbMembership..tblMatrixUserAdditional.companyid = dbMembership..tblGenusFranchiseDetails.id
WHERE dbMembership..tblMatrixUserAdditional.Username NOT IN
(SELECT username FROM dbPubLog..tblAppActions.Username WHERE ipaddress <> '127.0.0.1')

The query works fine before the WHERE clause. Then I get this eror

Could not find server 'dbPubLog' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

But dbPubLog isn't a server its a database on the same server as the others in the query.

Whats wrong?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 05:12:44

Do you have the proper permissions to access the dbPubLog database?

SELECT		mua.Username,
		gfd.Company
FROM		dbMembership..tblMatrixUserAdditional AS mua
INNER JOIN	dbMembership..tblGenusFranchiseDetails AS gfd ON gfd.id = mua.companyid
WHERE		NOT EXISTS (SELECT * FROM dbPubLog..tblAppActions AS aa WHERE aa.username = mua.Username AND aa.ipaddress <> '127.0.0.1')

E 12°55'05.25"
N 56°04'39.16"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-13 : 05:23:45

Thanks Peso,

Confused now as your query executes perfectly.

This is a test environment using the sa account, so permissions should be fine ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources