0

My SQL Server instance has a linked server to an Eloquence database. I need to select data from one of the tables in the Eloquence database, but I need to do it with SQL syntax, since I'm running the code from SQL Server Management Studio (SSMS).

One of the columns has a forward slash in its name (I didn't create it, nor do I have the authority to change it), which throws the following syntax error when I run my query:

OLE DB provider [ProviderName] for linked server [LinkedServerName] returned message "Prepare: Syntax error or access violation {Syntax error in item list (select).}".

OLE DB provider [ProviderName] for linked server [LinkedServerName] returned message "Prepare: Syntax error or access violation {Expression syntax error in aggregate function.}".

Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT [TableName.ColumnName/WithForwardSlash] FROM [TableName]" for execution against OLE DB provider [ProviderName] for linked server [LinkedServerName].

I have tried:

  • Select * from.... throws the same error
  • Aliasing the column...throws the same error
  • Selecting the tablename.column name...throws the same error
  • Enclosing column name in brackets...throws the same error
  • Escaping the forward slash...throws 'Column not found' error

Note: I lack permissions to access the server directly, so I must query it via the linked server from SQL Server.

How can I select this column from SSMS via the linked server if it has a forward slash in it? How can I get around this error?

The SQL/R documentation: SQL/R A.03 Documentation

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Stan Shaw
  • 3,014
  • 1
  • 12
  • 27
  • 1
    Did you try selecting `[TableName].[ColumnName/WithForwardSlash]`? – Malk Aug 05 '15 at 18:05
  • If Malk's suggestion does not work try escaping the slash with two of them (ie `//`) – Hogan Aug 05 '15 at 18:10
  • I have already tried both of those and neither worked. I will update my question. – Stan Shaw Aug 05 '15 at 18:13
  • Because it throws a syntax error - but when I try escaping it it says 'Invalid Column Name', that tells me that it understands that the column with a forward slash in its name does in fact exist. However, I think the SQL syntax is being converted to run against the eloquence database - and it is unable to convert the column name that has the forward slash. That's probably why SELECT * from...throws the same error. – Stan Shaw Aug 05 '15 at 18:15
  • Can you create (or have someone else create) a view on the SQL Server and alias the column name in the view, then query that view instead of the table? – JSR Aug 05 '15 at 18:19
  • Creating a view will result in the same exact error, since the view needs to run the same query that I'm running in order to return a result set, right? I have full SA permissions on the SQL Server, so I can do whatever I want there. But I cannot access the eloquence database except via the linked server in SQL server. – Stan Shaw Aug 05 '15 at 18:22
  • Have you tried bringing it in through Excel and seeing what the results of that query is? http://www.marxmeier.com/sqlr/A0300/windows_odbc.html – TTeeple Aug 05 '15 at 18:27
  • @TTeeple As I said in the post, I cannot access the server directly, so that is an impossibility. – Stan Shaw Aug 05 '15 at 18:30
  • Have you tried using [`OPENQUERY()` syntax](https://msdn.microsoft.com/en-us/library/ms188427(v=sql.100).aspx)? Many third party clients require it for linked servers. – Bacon Bits Aug 05 '15 at 19:21
  • @BaconBits I tried that and it threw the same error - thank you for the suggestion, though, that was a new one! – Stan Shaw Aug 05 '15 at 19:40

0 Answers0