OpenDataSource - Datasource = web address?

[inspiraller]

Hello

I would like to make a connection to an excel database on an external server from sql server via a stored procedure and import the data from it.

I have tried importing data from the excel database sitting on the local harddrive of the sql server and that works, however I will need to access it from an external server.

WORKING VERSION (EXCEL DB ON SAME PC AS SQL SERVER)

create procedure sp_excel_to_sql
as
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="F:\Program Files\Microsoft SQL Server\stevesdb\test3.xls";Extended properties=Excel 8.0')...[Sheet1$]
GO

NON WORKING VERSION

create procedure sp_excel_to_sql
as
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="

http://localhost/stevesdb/test3.xls";Extended

properties=Excel 8.0')...[Sheet1$]
GO


ERROR:

Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialiaze::Initialize returned 0x80004005: The provider did not give any information about the error.].

 

[bborissov]

I seriously doubt that you could open file that way. To open file you must have access to drive as file server not HTTP one. Try:

create procedure sp_excel_to_sql
as
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="\\localhost\stevesdb\test3.xls";Extended properties=Excel 8.0')...[Sheet1$]
GO

But how to do this from WWW? may be you must first download this file from server to local computer and then open it. An maybe I am totaly wrong

Borislav Borissov

 

[inspiraller]

thanks Boris

I tried that with forward or backslashes and that didn't work either. I appreciate what you are saying. I can only think the best way would be to first to upload data from excel into an asp or php page etc and then update sql database dynamically from there.

 

[bborissov]

OOOPS. Not localhost:

Create procedure sp_excel_to_sql
as
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="\\Local Station Name Here\stevesdb\test3.xls";Extended properties=Excel 8.0')...[Sheet1$]
GO

To upload file to ASP or PHP and then update SQL from there must work.

Borislav Borissov

 

[inspiraller]

Same error message even with that. Cheers anyway though.