0

While connected to a local sql server database, I can make queries to retrieve and write data. However, this query fails:

SELECT 
    *
INTO
    #tmp_table
FROM OPENROWSET
    (  
        'MSDASQL'
        ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
        ,'select * from csv_filePath'
    );

with the error message: SQL Error [7222] [S0255]: Only a SQL Server provider is allowed on this instance.

I am using dbeaver on Linux Mint 19.2. I tried the same command on SQL Server Management Studio on a windows VM, but got the same message. My firewall is set up as in the picture below: Firewall config

Please help.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Rutwik Kharkar
  • 45
  • 7
  • It looks like your local sql database does not allow the Microsoft Access Driver. Is this a full sql server or a sql express instance? – casenonsensitive Jul 13 '20 at 15:26
  • You do not give the path of the file you are trying to read. Try putting the filename in the `Select` statement. e.g. `'select * from C:\Test.CSV'` – SS_DBA Jul 13 '20 at 15:27
  • 1
    SQL Server on Linux does not support linked server types other than other SQL Server instances. That is [documented](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2017?view=sql-server-ver15#Unsupported). – Thom A Jul 13 '20 at 15:31

1 Answers1

2

As I mentioned in the comment, SQL Server on Linux does not support Linked servers for anything other than other SQL Servers. From the documentation Editions and supported features of SQL Server 2017 on Linux:

Unsupported features & services

The following features and services are not available SQL Server 2017 on Linux. The support of these features will be increasingly enabled over time.

Area                Unsupported feature or service
Database engine     Merge replication
                    Stretch DB
                    PolyBase
                    Distributed query with 3rd-party connections
                    Linked Servers to data sources other than SQL Server
                    System extended stored procedures (XP_CMDSHELL, etc.)
                    Filetable, FILESTREAM
                    CLR assemblies with the EXTERNAL_ACCESS or UNSAFE permission set
                    Buffer Pool Extension

Notice that it specifically states "Linked Servers to data sources other than SQL Server". If you need a linked server to a different source, you will need to use SQL Server on Windows at this time.

Thom A
  • 88,727
  • 11
  • 45
  • 75