servers

Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We can able to execute sql statements with 4 part key
from sqlserver1 to sqlserver2 on some databases and
cannot able to do on other databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.
.Rajah,
just to check if this is a permissions issue, can you open up a connection
to sqlserver1 using QA. This needs to be done in hte context of the linked
server login, so you could log on as that user or use SETUSER to switch
users. Once connected, can you do the select using the 4 part name?
Regards,
Paul Ibison|||Hi Paul,
Thanks for your reply.
I tried as you suggested and getting the same error.
We are getting this error for all ids, but for only some
databases. Users have permissions and previleges.
Same error for even the user with sysadmin role
previleges.
Any help appreciated.
Rajah V.
>--Original Message--
>Rajah,
>just to check if this is a permissions issue, can you
open up a connection
>to sqlserver1 using QA. This needs to be done in hte
context of the linked
>server login, so you could log on as that user or use
SETUSER to switch
>users. Once connected, can you do the select using the 4
part name?
>Regards,
>Paul Ibison
>
>.
>|||Rajah,
using the same connection as in the previous post, does
use db1
go
select * from INFORMATION_SCHEMA.TABLES
return table1 in the recordset, having an owner of dbo?
Regards,
Paul Ibison|||Yes. table1 is in the recordset when I execute
'select * from information_schema.tables'
Was there a chance that any system table is not recognising the
database?
Rajah V.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Rajah,
my suspicion was that the owner wasn't dbo. If it is (please confirm) then I
really don't understand it - you say even with sysadmin privileges you can't
access the table? Does the same apply to opening the table in EM? What if
you run
select * from sqlserver1.db1.dbo.table1
select * from db1.dbo.table1
select * from dbo.table1
select * from table1
I hope that it's an owner issue!
Regards,
Paul Ibison