Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "CHIMSSQLDEV" does not contain the table ""ArrowProdDB"."dbo"."sysfiles"".... Expand / Collapse
Author
Message
Posted Monday, March 24, 2008 3:58 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:14 AM
Points: 361, Visits: 1,177
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 ¤
Post #473766
Posted Monday, March 24, 2008 5:02 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
Try adding ", @datasrc=@ServerName" to your sp_addlinkedserver statement.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #473787
Posted Monday, March 24, 2008 9:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 5:36 PM
Points: 1,339, Visits: 596
Agree with RBarry. Adding datasource will resolve issue.

--www.sqlvillage.com
Post #473848
Posted Tuesday, March 25, 2008 9:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:14 AM
Points: 361, Visits: 1,177
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 ¤
Post #474183
Posted Wednesday, March 26, 2008 12:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #474536
Posted Wednesday, March 26, 2008 7:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:14 AM
Points: 361, Visits: 1,177
Thank you so much! An article is a great idea! :D

¤ §unshine ¤
Post #474707
Posted Wednesday, November 26, 2008 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 2:00 PM
Points: 1, Visits: 12
Thanks guys... This was usefull to me. I had that same issue and this artickle helped me resolve it.
Post #609344
Posted Friday, January 16, 2009 1:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 14, 2009 10:49 AM
Points: 2, Visits: 15
I had the same problem when my login was not a user in the database I was selecting against.
Post #638475
Posted Friday, January 16, 2009 10:31 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 7:41 AM
Points: 323, Visits: 464
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


_____________________________________________________________________________________________________________
Paresh Prajapati
+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Post #638648
Posted Wednesday, October 13, 2010 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 8:07 AM
Points: 1, Visits: 42
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
Post #1003646
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse