SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table not being recognized on remote query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phrankbooth
Posting Yak Master

USA
141 Posts
Posted - 12/19/2012 :  21:56:34  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 12/19/2012 :  22:05:49  Show Profile  Reply with Quote
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/
Go to Top of Page

phrankbooth
Posting Yak Master

USA
141 Posts
Posted - 12/19/2012 :  22:12:51  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 12/19/2012 :  22:34:33  Show Profile  Reply with Quote
oh ok...that explains it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000