OPENROWSET or OPENDATASOURCE Example to get Data from .xlsx or .csv file on file system of Remote Server

Question

There are plenty of examples around showing how to use OPENROWSET and OPENDATASOURCE to open queries against tables on remote SQL servers, and to get data from files on other servers. Unfortunately, none of them tell me how to do the specific thing that I need to do, which is use one of these commands to get either a .csv or .xlsx Excel file from a remote server using the server IP address and windows login where the server has no SQL Server instance installed - only either IIS7 or 8.

It would be great if Microsoft documentation didn't omit basic examples of how to use their tools in the most likely of ways. It takes no real intelligence to try 40 odd different combinations of parameters list: it's just an inefficient waste of time. Developers have more important things to do than waste hours trying to discover some 'secret knowledge' recipe which is really just a not-properly-documented variant of the command parameters, and pretending that this is something to do with being clever.

So to assuage my philosophical angst, could someone please provide an example of how to use either OPENROWSET or OPENDATASOURCE to get/select content from either a .csv file or an excel spreadsheet using the remote server IP address XXX.XXX.XXX.XXX, a port number if it should be included, the file system path correctly appended to that or included in the command, and the correct arrangement of the username and password parameters. There is no SQL server instance on the remote server - just IIS and a website with windows auth. If you have an example that works with SQL server instance on the remote server, that will do (although I think some of the REMOTEDATASOURCE examples cover this already) but I REALLY want an example where I don't have to have an SQL server instance on the remote web server.

I know that you can use linked servers for this, but there is some extra baggage to put on the remote server containing the file to access, and in my case the server containing the excel or .csv text file will not even have a SQL Server instance on it - only IIS and a website.

(Also - can someone confirm whether you can use linked servers with NO SQL Server or other database server instance on the remote server with the desired text data file? Is it linked database servers only, or linked servers where the remote server can just be a windows and web server with no SQL server installed on it?)

This example is handy, but does not tell me if SERVERPROPERTY('MachineName') AS nvarchar(128)) can contain an IP address of a remote windows server with no SQL server instance on it, and it is not about accessing text files.

This example comes blisteringly close, but there does not seem to be a positive outcome in the post, and there is no text file involved - only a DB query so the remote system has SQL server installed?:

SELECT * 
FROM OPENROWSET('PACISOleDb', '192.168.200.172';'admin';'admin', 'SELECT * FROM DB')

Existing examples that are good but don't help much:

SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=YourServer;UID=UserID;PWD=Password',
   'select * from sys.sysobjects')

(Source)

SELECT ContactName, CompanyName, ContactTitle
FROM OPENDATASOURCE('SQLOLEDB',
              'Data Source=ServerName;User ID=MyUID;Password=MyPass' ).Northwind.dbo.Customers

(source)

-- SQL Server 2012
SELECT
    *
FROM
    OPENDATASOURCE ('SQLNCLI11', 
   'Data Source=SQLInstanceName;Catalog=DBName;User ID=SQLLogin;Password=Password;').DBName.SchemaName.TableName


SELECT *
FROM OPENROWSET('SQLNCLI11',
   'DRIVER={SQL Server};SERVER=SQLInstanceName;UID=SQLLogin;PWD=Password',
   'select * from DBName..TableName')  

--Access DB
SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0', 
                              'Data Source=D:\MyDB\MyAccessDB.accdb')...TableName   

(source)

SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=MyServer;UID=MyUserID;PWD=MyCleverPassword',
   'select @@ServerName') 

(source. I take it that {SQL Server} here has to be a SQL server name - not a remote IP Address? It is not clear.)

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\test.xls;HDR=No',
                'SELECT * FROM [Sheet1$]') b

(source)

--Excel 2007-2010
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx',
    'SELECT * FROM [ProductList$]');

--Excel 97-2003
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\temp\Products.xls',
    'select * from [ProductList$]');

(source. Again - no remote server, no IP server address, no credentials)

Lots to chose from here. GREAT blog post, but not useful for my specific ends:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml;
   Database=C:\DataFiles\EmployeeData1.xlsx',
   [vEmployee$]); 

SELECT * 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\DataFiles\EmployeeData1.xlsx;
   Extended Properties=Excel 12.0 Xml')...[vEmployee$];

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=YES;
   Database=C:\DataFiles\EmployeeData1.xlsx',
   [vEmployee$]);

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=NO;
   Database=C:\DataFiles\EmployeeData1.xlsx',
   [vEmployee$]);

SELECT * INTO EmployeeInfo3
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=YES; IMEX=1;
   Database=C:\DataFiles\EmployeeData1.xlsx',
   [vEmployee$]);

(source)

The MS openrowset documentation has an '|' in the command schema suggesting this cannot be done remotely:

SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt', 
    FIRSTROW=2, 
    FORMAT='CSV') AS cars;  

H. Accessing data from a CSV file without a format file:

    tsql
     Copy
    SELECT * FROM OPENROWSET(
       BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\MSSQL\DATA\inv-2017-01-19.csv',
       SINGLE_CLOB) AS DATA;

So I am thinking that the correct OR ONLY approach involves OPENDATASOURCE with the jet driver present and accessible on the remote server. However, where in the following example from MS does the IP address of the remote server and the login password and username go? If it cannot then that seems contradictory to the claims of the capability of the command in the documentation (based on the words they use), and it would be great if they could say 'you cannot do this', as it is pretty clear that most people will try...

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

So,

Something like:

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'|'Microsoft.Jet.OLEDB.4.0'|'PACISOleDb', 'Data Source=XXX.XXX.XXX.XXX\DataFolder\Documents\TestExcel.xls';User ID=MyUID;Password=MyPass;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

or maybe

(TWIDDLING THUMBS - BORING BORING BORING)

I should give up and use a linked server:

EXEC sp_addlinkedserver
  @server = 'ExcelLinkSrv1',
  @srvproduct = 'Excel', 
  @provider = 'Microsoft.ACE.OLEDB.12.0',
  @datasrc = 'C:\DataFiles\EmployeeData1.xlsx',
  @provstr = 'Excel 12.0 Xml; HDR=YES; IMEX=1';
GO

But again - can I have:

@server = '202.217.XXX.XXX'

?

Yes - I know that you would normally not hard code it, but let's start simple in SSMS. I wanted to avoid linked server for different reasons. How do you do it with OPENROWSET or else OPENDATASOURCE against excel spreadsheet or else .csv file?

You can have all of my reputation points or whatever they are if your solution or information works (and someone else did not get them first), because I don't care about that stuff.

Answer 1

Simply use windows sharepaths for that.

EXEC sp_addlinkedserver
@server = 'ExcelLinkSrv1',
@srvproduct = 'Excel', 
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = '\\SERVEROTHER\c$\DataFiles\EmployeeData1.xlsx',
@provstr = 'Excel 12.0 Xml; HDR=YES; IMEX=1';