Sql-server – syntax for accessing table names containing slashes

linked-serversql-server-2005

I have a linked server registered in my SQL Server 2005 which accesses our SAP database (test system). It uses an ODBC driver provided by SAP, and is connected using the Microsoft OLEDB For ODBC driver. The remote database is a MaxDB 7.7.

I'm currently exploring the possibilities of directly accessing data within our SAP system, but have run into problems regarding certain tables.

The following works without problems:

SELECT Field FROM LinkedServer..Schema.Table WHERE MANDT = '110'

or, respectively

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT Field FROM Table WHERE MANDT = ''110''')

Now, there's some nasty table names within SAP that can contain slashes. I think it's meant to be a "namespace" type of thing contained within the actual table name. On the ABAP side of things, I can easily do

SELECT * FROM /MYWHATEVER/MYTABLE

provided the table exists. But I can't get the table names containing / working via linked server.

Trying

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM /MYWHATEVER/MYTABLE')

gives me "Error 7321 while preparing query…". Using

SELECT * FROM LinkedServer..Schema.[/MYWHATEVER/MYTABLE]

yields "Error 7306 OLEDB-Provider "MSDASQL" can't open table "Schema./MYWHATEVER/MYTABLE" for linked server LinkedServer, it contains errors or doesn't exist".

Is there any way I can access tables containing / on a MaxDB via LinkedServer?

[The error messages may not be literally the same as on an english system, since I had to translate them from german]

Best Answer

I'd try escaping stuff and see what happens

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM //MYWHATEVER//MYTABLE')
SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM \/MYWHATEVER\/MYTABLE')
...

I assume that the local ODBC layer is bollixing you. The bit inside OPENQUERY should be a literal to SQL Server so it is external to SQL Server. So it could be be the ODBC layer: can you profile (or log) what happens on the MaxDB to see what gets through?

Another option would be use a view or proc in the MaxDB to give "proper" names