March 24, 2008 at 3:58 pm
I can select from a master table however get this error for all user databases. any idea what is going on?
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "Server Name" does not contain the table ""DBname"."dbo"."sysfiles"". The table either does not exist or the current user does not have permissions on that table.
This is the setting from my linked server.
declare @ServerName varchar(50)
Set @ServerName = 'Server Name'
--exec sp_dropserver @ServerName
exec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'
-- Set options
exec sp_serveroption @ServerName, 'collation compatible', 'true'
exec sp_serveroption @ServerName, 'data access', 'true'
exec sp_serveroption @ServerName, 'rpc', 'false'
exec sp_serveroption @ServerName, 'rpc out', 'true'
exec sp_serveroption @ServerName, 'use remote collation', 'false'
-- Test connection
declare @SQL nvarchar(200)
set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'
exec sp_executesql @sql
-- test retreival
select count (*) from ServerName.master.DBO.sysdatabases
¤ §unshine ¤
March 24, 2008 at 5:02 pm
Try adding "[font="Courier New"], @datasrc=@ServerName[/font]" to your [font="System"]sp_addlinkedserver[/font] statement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2008 at 9:29 pm
Agree with RBarry. Adding datasource will resolve issue.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 25, 2008 at 9:39 am
It works for sa users, not for Windows Authenticated users. 🙁 They get this error.
OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Invalid connection string attribute".
¤ §unshine ¤
March 26, 2008 at 12:42 am
sunshine (3/25/2008)
It works for sa users, not for Windows Authenticated users. 🙁 They get this error.OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Invalid connection string attribute".
Boy, I should write an article on this one.
See my previous post in another thread: http://www.sqlservercentral.com/Forums/FindPost473248.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 26, 2008 at 7:41 am
Thank you so much! An article is a great idea! 😀
¤ §unshine ¤
November 26, 2008 at 11:49 am
Thanks guys... This was usefull to me. I had that same issue and this artickle helped me resolve it.
January 16, 2009 at 1:19 pm
I had the same problem when my login was not a user in the database I was selecting against.
January 16, 2009 at 10:31 pm
sunshine (3/24/2008)
I can select from a master table however get this error for all user databases. any idea what is going on?Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "Server Name" does not contain the table ""DBname"."dbo"."sysfiles"". The table either does not exist or the current user does not have permissions on that table.
This is the setting from my linked server.
declare @ServerName varchar(50)
Set @ServerName = 'Server Name'
--exec sp_dropserver @ServerName
exec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'
-- Set options
exec sp_serveroption @ServerName, 'collation compatible', 'true'
exec sp_serveroption @ServerName, 'data access', 'true'
exec sp_serveroption @ServerName, 'rpc', 'false'
exec sp_serveroption @ServerName, 'rpc out', 'true'
exec sp_serveroption @ServerName, 'use remote collation', 'false'
-- Test connection
declare @SQL nvarchar(200)
set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'
exec sp_executesql @sql
-- test retreival
select count (*) from ServerName.master.DBO.sysdatabases
USE master;
GO
EXEC sp_addlinkedserver 'servername', N'SQL Server'
GO
USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'data access', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'servername', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dbapwd'
GO
note : dba user has acces on table which u want
October 13, 2010 at 8:07 am
Interesting responses.
When faced with the same problem, I went to the remote server and changed the permissions on the account "NT AUTHORITY\ANONYMOUS LOGON". Giving this sysadmin privileges fixed the problem, but that is overkill! In the end, I simply gave the account read access to all the databases on the server.
--gordon
November 19, 2013 at 8:14 am
The OLE DB provider "SQLNCLI10" for linked server "A" does not contain the table "DatabaseA""."dbo"."TableA"".
The table either does not exist or the current user does not have permissions on that table.
E.G.
Connecting from server A to Server B using a linked server
Example
From Server B
select *
from [A].[DatabaseA].dbo.TableA
error message:
The OLE DB provider "SQLNCLI10" for linked server "ServerA" does not contain the table ""DatabaseA"."dbo"." TableA "".
The table either does not exist or the current user does not have permissions on that table.
Check the login context for linked server on server B
Server Objects
Linked Servers
[ServerB]
Right click
Properties
Security
REMOTE LOGIN username..----------------------------------------------------------*
Then go to Server A
Go
Security
Logins
Username from --------------------------------------------------------------------------* above
Make sure this Username has access to Database being queried above. (DatabaseA)
Give DBO access or atleast DATAREADER..
November 19, 2013 at 8:17 am
The OLE DB provider "SQLNCLI10" for linked server "A" does not contain the table "DatabaseA""."dbo"."TableA"".
The table either does not exist or the current user does not have permissions on that table.
E.G.
Connecting from server B to ServBr A using a linked server
Example
From Server B
select *
from [A].[DatabaseA].dbo.TableA
error message:
The OLE DB provider "SQLNCLI10" for linked server "ServerA" does not contain the table ""DatabaseA"."dbo"." TableA "".
The table either does not exist or the current user does not have permissions on that table.
Check the login context for linked server on server B
Server Objects
Linked Servers
[ServerB]
Right click
Properties
Security
REMOTE LOGIN username..----------------------------------------------------------*
Then go to Server A
Go
Security
Logins
Username from --------------------------------------------------------------------------* above
Make sure this Username has access to Database being queried above. (DatabaseA)
Give DBO access or atleast DATAREADER..
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply