Table not being recognized on remote query

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 2008 Forums

Transact-SQL (2008)

Table not being recognized on remote query

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2012-12-19 : 21:56:34

Can anyone offer any hints or thoughts as to why a particular DB and table are not recognized using either Opernrowset or opendatasource methods of remote access?

I am accessing other DB remotely on the same server using same methods, with same exact credentails, which by the way are assigned the same way on the DB which is not being recognized.

Here's the query:
SELECT e.Employee_ID, e.FirstName, e.LastName, e.Login, e.EmailAddress, e.IsActive, e.Title, t.Team_ID TeamID, t.Code TeamCode, t.Name TeamName, st.Code MDMTeamCode
FROM OPENDATASOURCE('SQLNCLI10', 'Data Source=Server;User ID=User;Password=Pass').DB1.[dbo].[Employees] e
left outer join (Select * from OPENDATASOURCE('SQLNCLI10', 'Data Source=Server;User ID=User;Password=Pass').DB1.[dbo].Teams) t on e.Team_ID = t.Team_ID
left outer join (SELECT Code FROM OPENDATASOURCE('SQLNCLI10', 'Data Source=Server;User ID=User;Password=Pass').[DB2].[dbo].[tblSalesTeam]) st
on t.Code = st.Code WHERE e.FirstName = 'xxx'

The last join in that query will not work, error says object does not exist. It does exist, and I'm spelling it right and the user and pwd are correct and have select rights on that DB and table.

Any advice appreciated.

Thx!

--PhB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 22:05:49

just try this query and report back result


SELECT * FROM OPENDATASOURCE('SQLNCLI10', 'Data Source=Server;User ID=User;Password=Pass').[DB2].[INFORMATION_SCHEMA].TABLES WHERE TABLE_NAME ='tblSalesTeam'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

phrankbooth
Posting Yak Master

162 Posts

Posted - 2012-12-19 : 22:12:51

Thanks! Right after I wrote this I checked securables at server level and it wasn't set for this DB and the user. Silly me!!

--PhB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 22:34:33

oh ok...that explains it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources