1

I'm getting the following error on some of the linked server,when I try to query a table using a link server. The link server is created on SQL2014 instance to connect to an SQL2008r2 instance. enter image description here

eaon03
  • 11
  • 1
  • Can you try click RMB on table name in MSSMS and choose action 'SELECT TO'? Then you see generated query. – bontade Jan 21 '19 at 13:28
  • does your linked server user have proper permissions for remote table? – Bob Klimes Jan 21 '19 at 14:40
  • 1
    Please add the definition for the linked server. Which account (SQL Login) is used to access the linked server? Did you define a SQL Server Login for accounts that do not have a specific login? (The sa account is not sa on the linked server) – John K. N. Jan 21 '19 at 15:10
  • i have used this query to creat linked server... – eaon03 Jan 23 '19 at 04:23
  • i used this query to create linked server. .. USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'LEK', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.10.30.206' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LEK',@useself=N'False',@locallogin=NULL,@rmtuser=N'SA',@rmtpassword='#############' GO – eaon03 Jan 23 '19 at 04:29
  • Can you expand (in MSSMS UI) any of LinkedServer databases to see if tables or views will show up? Maybe user that you're using does not have enough rights. – bontade Jan 23 '19 at 09:40
  • yes ,i can connect linked server through ip address and i can views all the tables of that linked server and 'sa' user have full privilege .. – eaon03 Jan 24 '19 at 05:55

1 Answers1

0

What Provider are you using? Try the "Microsoft OLE DB Provider for SQL Server" or the "SQL Server Native Client". Also ensure the permissions in the linked server are correct, and that the account actually has access to the database on the remote server.

WadeH
  • 540
  • 4
  • 18
  • i am new on these thing so how do i check which provider is using .. and i can able to access database remotely by ip address... – eaon03 Jan 22 '19 at 11:26