SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "CHIMSSQLDEV"...


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""....

Author
Message
sunshine-587009
sunshine-587009
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 1273
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 ¤
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15354 Visits: 9518
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."
Mohan Kumar
Mohan  Kumar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 596
Agree with RBarry. Adding datasource will resolve issue.

--www.sqlvillage.com
sunshine-587009
sunshine-587009
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 1273
It works for sa users, not for Windows Authenticated users. Sad 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 ¤
RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15354 Visits: 9518
sunshine (3/25/2008)
It works for sa users, not for Windows Authenticated users. Sad 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."
sunshine-587009
sunshine-587009
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 1273
Thank you so much! An article is a great idea! BigGrin

¤ §unshine ¤
ali-550825
ali-550825
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
Thanks guys... This was usefull to me. I had that same issue and this artickle helped me resolve it.
debbie.bull
debbie.bull
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 15
I had the same problem when my login was not a user in the database I was selecting against.
Paresh Prajapati
Paresh Prajapati
Right there with Babe
Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

Group: General Forum Members
Points: 785 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
Arrow +919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Gordon Linoff
Gordon Linoff
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search