0

I have two SQL Server instances:

  • SQLSERVER64
  • SQLSERVER32

In SQLSERVER32, I have a linked server to a Paradox database called ParadoxDBLinkedServer.

To access some of the Paradox tables in the server I just execute the following statement:

SELECT * 
FROM [ParadoxDBLinkedServer]...Clients

Then, I need to access to these objects from the SQLSERVER64 instance. In order to do so, I created a linked server called [.\SQLSERVER32] in this instance pointing to SQLSERVER32.

What I'm trying to do now is to access directly the ParadoxDBLinkedServer objects from the SQLSERVER64 instance. I mean, accessing a linked server from another linked server.

The query I´m trying to run is the following one:

SELECT * 
FROM [.\SQLSERVER32].[ParadoxDBLinkedServer ]...Clients

But I get this error:

The number name '.\SQLSERVER32.ParadoxDBLinkedServer ...Clients' contains more than the maximum number of prefixes. The maximum is 3.

Is there any way to perform an access like this? What am I missing here?

I'm trying to avoid creating a View for each table of the ParadoxDBLinkedServer in the SQLSERVER32 instance to reduce overhead.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andres
  • 2,729
  • 5
  • 29
  • 60
  • You get this error when you have excess objects in your four part naming convention: `[.\SQLSERVER32].[ParadoxDBLinkedServer ].[Schema_Name].[Table_Name]` try this.. – d_luffy_de Dec 28 '16 at 12:24
  • @Darshan_Viswanath_BI, the schema is missing. Actually it is in the default schema. But trying [.\SQLSERVER32 ].[ParadoxDBLinkedServer].[default].Clients I get: The OLE DB provider "%ls" for linked server "%ls" does not contain the table "%ls". The table either does not exist or the current user does not have permissions on that table. – Andres Dec 28 '16 at 12:30
  • Can you log into both servers and do a `select * from [Database].[dbo].[Client]` ? Prefixing with [ParadoxDBLinkedServer ] should be your solution. If the table does not exist or you do not necessary permissions you will know it. – d_luffy_de Dec 28 '16 at 12:56

2 Answers2

1

This is late, but hopefully can be helpful to someone else who comes to this post later. If you can use openquery then something like this should work:

SELECT * FROM OPENQUERY([.\SQLSERVER32], 'SELECT * FROM   OPENQUERY([ParadoxDBLinkedServer ],''SELECT * FROM Clients'')' )
Weihui Guo
  • 3,669
  • 5
  • 34
  • 56
0

select * from openquery (Linkedservername,'select * from tablename')