none
OpenDataSource with SQL Server Compact 3.5

    Question

  •  Hi,

    Search found a nice post on this topic, but unfortunately it's in spanish.  So here it goes in English...

    Does SQL Server Compact 3.5 support the OPENDATASOURCE T-SQL method?  Here is my T-SQL code, and the error message it produces:

    SELECT * FROM 
    OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
                'Data Source=C:\SampleDataImport.xls;Extended Properties=Excel 8.0')...[Sheet1$];
    








    Major Error 0x80040E14, Minor Error 25501 > SELECT * FROM OPENDATASOURCE('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?

    Thanks,

    Scott

    • Edited by scottydel Tuesday, August 25, 2009 3:40 AM spacing
    Tuesday, August 25, 2009 3:01 AM

Answers

  • 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.
    Tuesday, August 25, 2009 6:53 AM