Linked server Problem

Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain 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.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain 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.|||Also make sure that SA is the owner in all of the DB's. I have seen issues
like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.

>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider[vbcol=seagreen]
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.

>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>sql

Linked server Problem

Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain 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.
Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain 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.
|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I
|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.

>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in[vbcol=seagreen]
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider
>
>.
>
|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.

>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>

Linked server Problem

Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain 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.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain 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.|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB'
Jef
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
>> Hi,
>> We linked 2 sql servers and executing stored procedures
>> between the two servers.
>> We are using the 4 part key to reference a table.
>> We can able to execute sql statements with four part
key
>> from sqlserver1 to sqlserver2 for certain databases and
>> cannot able to do on certain 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.
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

-

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

Did you check permissions of remoteUser on the remote server instance? You can do this by just using the remote login to connect to the server using ISQLW and see what tables you can access or check your permissions.|||Yes, remoteUser is able to access RemoteServerName through Query Analizer.|||I didn't mean just accessing the server. That works since you didn't get any errors. What tables can you access? What happens if you try to select from a known table?|||

You have to use the Servername.Database.UserObject.Table on the query..

example,

Select * from [RemoteServer\MSSS2000].NorthWind.dbo.Orders

Linked server from SQL2005 to SQL2000

Linked server from SQL2005 to SQL2000

Hi

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

--

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

What do you mean by "I am not able to see the tables under it"... have you tried:

SELECT * FROM [targetServer].[master].dbo.SysObjects to see if you are actually connected (and can query) ?

|||No, this does not work, I am getting no object found|||Can you post your query to access the linked server, as well as the error message you got? Maybe you got right object name, but incorrect schema name?|||

Hey u can't see the link server objects in 2005 as u used to see in sql2000.

But u can query an object as u wish.

if u know the name of any table.

Try to query the link server like

Select * from LinkServerName...tablename.

You can also use openquery statement.

Good Luck

|||Do you mean you can't find Linked Server objects in Management Studio? Actually?Linked Server objects are moved to the "Server Objects" catalog in Management Studio, no longer under the Security branch as in Enterprise Manager

You can also get information about linker servers usingsp_linkedservers system procedure.