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

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

server, Excel - Permissions Issue

Hello,
Linked Server to Excel spreadsheet
I have no problem executing a Linked Server query through Query Analyzer
(Administror) or through an ASP page connecting through a Login setup throug
h
Enterprise Manager with: Server Role - Systems Administrator. However any
ohter lesser Login / Role yields:
ERROR -
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'
NOTES:
* Link Server Properties / Security is - DEFAULT
* Permissions on Excel spreadsheet & directory are - Everyone
Thus, I believe what I'm looking for are the What and How on setting
permissons for executing this query on the linked server?
I have been struggling with this for several days and do not find much on
the web per this issue... any help would be appreciated.
Thanks, j"JLatiolait" <JLatiolait@.discussions.microsoft.com> wrote in message
news:1F47F9C9-18EF-4B60-9D36-42BBD1C56052@.microsoft.com...

> Linked Server to Excel spreadsheet
> I have no problem executing a Linked Server query through Query Analyzer
> (Administror) or through an ASP page connecting through a Login setup
through
> Enterprise Manager with: Server Role - Systems Administrator. However any
> ohter lesser Login / Role yields:
> ERROR -
> Microsoft OLE DB Provider for SQL Server (0x80040E14)
> Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'
> NOTES:
> * Link Server Properties / Security is - DEFAULT
> * Permissions on Excel spreadsheet & directory are - Everyone
Everyone = what permissions at the NTFS layer?
Steve