Major Error 0x80040E14, Minor Error 25501
> SELECT * FROMOPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\SampleDataImport.xls;Extended Properties=Excel 8.0')...[Sheet1$]
There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = OPENDATASOURCE ]
I am running:
WindowsXP Pro SP3
Visual Basic Express 2008, which comes with...
SQL Server Compact 3.5
(I have also DLed MS SQL Server Mgmt Studio 2008 to connect to my Compact 3.5 DB to test queries and whatnot)
I am trying to import data from an Excel 2007 spreadsheet into a SQL Server Compact 3.5 DB. Microsoft lists OPENDATASOURCE as a valid keyword for SQL Server Compact 3,5. I cannot find any examples of how to use OPENDATASOURCE with Excel and SQL Server Compact 3.5 (or if this combo is even supported).
The spanish post (I think) eventually recommends using on OleDbConnection object (as opposed to T-SQL) to open the Excel sheet. I think I can do the same, then eventually transfer the Excel data to the SQL Server Compact 3.5 DB via object manipulation (SQLServerCE objects and DataSet or RowSet obejcts resulting from the OleDbConnection).
But I am wondering if OPENDATASOURCE can be used and the Excel data can be imported to a SQL Server Compact 3.5 DB using T-SQL?
Edited byscottydelTuesday, August 25, 2009 3:40 AMspacing
OPENDATASOURCE is a reserved word in SQL Compact 3.5, yes. But it is not a valid SQL function in SQL Compact. You will probably have to write code to read a csv file, (or beta test the latest source here :-) (I have added CSV import))Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com
Please mark as answer, if this was it.