How do I access Linked server Tables in sql server

Question

I have created a Linked server through LAN,

Linked server Name-[DELL-PC]

Here, I'm not able to run this query to select Values from Linked server

case :1
    select *From  [DELL-PC].[BTS_Server].[dbo].Assy_ProductionOrder_ParameterSetting
    select *From  [DELL-PC].[BTS_Server]..Assy_ProductionOrder_ParameterSetting

it says:

"Invalid object name 'BTS_Server.dbo.Assy_ProductionOrder_ParameterSetting'."

but if i use openquery, it is working smoothly

case :2
    select * 
    From openquery([DELL-PC],
    'Select *From [BTS_Server].[dbo].Assy_ProductionOrder_ParameterSetting ');

How do i access linked server Table like case 1 statements, kindly assist me.

In my case, linked server name contains IP address. See https://stackoverflow.com/a/60188969/9117945, as well. — Alireza Abdollahnejad, Feb 12 '20 at 13:05

Lukasz Szozda · Answer 1 · 2015-09-01T08:59:49.020

1

Check if your table contains XML column, if yes this syntax won't work.

SELECT * FROM [server].[database].[scheme].[table]

Possible workarounds:

Create view without the XML column(s) on remote server and query that.
Use a pass-through query in the form SELECT * from OPENQUERY (... )

Another possible answer:

Architecture mismatch 32/64-bit when inappropriate OLE DB Provider is used:

32 bit machines ARE able to query the linked server without using OPENQUERY. It is the 64-bit that must use OPENQUERY

edited Sep 01 '15 at 08:59

answered Sep 01 '15 at 06:35

Lukasz Szozda

162,964
23
234
275

No, I don't have any xml column in that table, also I have created dummy table with one nvarchar column,it says the same thing. – King_Fisher Sep 01 '15 at 06:43
In that exception it is not showing the linked server name in prefix of object name. – King_Fisher Sep 01 '15 at 06:46
@King_Fisher Probably you don't have permissions. See what is set in linked server - security tab and what permission has this user in remote database. – Lukasz Szozda Sep 01 '15 at 06:46
If I don't have permission, then openquery will not work. Am I right?, but openquery syntax is working – King_Fisher Sep 01 '15 at 06:47
Check that I ask and check also default catalog, just expand linked server under SSMS – Lukasz Szozda Sep 01 '15 at 06:48
I'm not able to check security tab of linked server, it says you can't create a local sql server as linked server, but I hope I have entered correct credentials – King_Fisher Sep 01 '15 at 06:51
@King_Fisher See http://i.stack.imgur.com/BOrvd.jpg, do you have catalog like screen with your database [BTS_Server]? – Lukasz Szozda Sep 01 '15 at 06:52
Obviously, yes I have catalogs, and tables, I see all tables – King_Fisher Sep 01 '15 at 06:54
@King_Fisher Ok, try this `select *From [DELL-PC].[BTS_Server].[dbo].[Assy_ProductionOrder_ParameterSetting]` – Lukasz Szozda Sep 01 '15 at 06:55
It says invalid object name 'bts_server.dbo.test123', I'm selecting my test123 table which I have created temporarily. – King_Fisher Sep 01 '15 at 06:58
@King_Fisher Next thing do you have mismatch between servers 32/64bit? – Lukasz Szozda Sep 01 '15 at 06:59
Mine is 64 bit,another is 32 – King_Fisher Sep 01 '15 at 07:00
@King_Fisher So you get your answer, as far as I know "32 bit machines ARE able to query the linked server without using OPENQUERY. It is the 64-bit that must use OPENQUERY" – Lukasz Szozda Sep 01 '15 at 07:00
@King_Fisher Can you try the same in opposite direction? – Lukasz Szozda Sep 01 '15 at 07:02
@King_Fisher It should work when '32-32', '64-64', '32-64' – Lukasz Szozda Sep 01 '15 at 07:04
Really, thank you so much,I will check and let u know shorty, thanks again, it helped me – King_Fisher Sep 01 '15 at 07:06
I have tried with my another machine, mine is 64,new one is 32, but it is working fine here in both directions. – King_Fisher Sep 01 '15 at 08:57
@King_Fisher So there is problem with `OLE DB provider` which you use to connect, use the same and check if it helps – Lukasz Szozda Sep 01 '15 at 08:59
Is there any possible of instance name? – King_Fisher Sep 01 '15 at 09:15
@King_Fisher Do you use Sqlcli11 on both cases? I can't help further without see actual configuration (permission/ole db driver(x64vsx86)/names) – Lukasz Szozda Sep 01 '15 at 09:17

score 0 · Answer 2 · answered Sep 01 '15 at 07:01

0

Like below

select * from LinkServername.Databasename.dbo.TableName

But make sure you have valid user and password to access the link server. This is done when you are creating link.

answered Sep 01 '15 at 07:01

Muhammad Idrees

21
4

score 0 · Answer 3 · answered Feb 12 '20 at 13:06

0

In my case, linked server name contains IP address, so see https://stackoverflow.com/a/60188969/9117945 for more types.

answered Feb 12 '20 at 13:06

Alireza Abdollahnejad

921
8
9

This is not an answer but a comment please visit and check [how to answer a question](https://stackoverflow.com/help/how-to-answer). – Yunus Temurlenk Feb 12 '20 at 13:27

How do I access Linked server Tables in sql server

3 Answers3