Have you tried changing the target CPU to x86 instead of “Any CPU” in the advanced compiler options? I know that this solves some problems with other OLEDB providers by forcing the use of the 32-bit version.
From ConnectionStrings “If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash. To always use IMEX=1 is a safer way to retrieve data for mixed data columns. ..” Please note that the IMEX value … Read more
If you have powershell available, just paste this into a powershell command prompt: foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator()) { $v = New-Object PSObject for ($i = 0; $i -lt $provider.FieldCount; $i++) { Add-Member -in $v NoteProperty $provider.GetName($i) $provider.GetValue($i) } $v } Credits and more advanced usage: http://dbadailystuff.com/list-all-ole-db-providers-in-powershell
ended up using this: using (OleDbConnection conn = new OleDbConnection(connString)) { conn.Open(); dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, “TABLE” }); Sheet1= dtSchema.Rows[0].Field<string>(“TABLE_NAME”); }
Adodb (ActiveX Data Objects DB) is an API layer over OLE DB. It works well with MS-based databases such as Sql Server, providing a consistent API and optimizations. That being said you can use ADODB to connect with non-MS data sources as well but that would mean that you will require an OLEDB/ODBC Provider for … Read more
The following method has proven useful for me. It’s super quick and practical and doesn’t require PowerShell: Open up Notepad and create an empty text file, then click File -> click Save -> and save it with the File name: TestConnection.udl to your desktop. Go to your desktop and double-click on the TestConnection.udl file you … Read more
Using .Net 4.0 and reading Excel files, I had a similar issue with OleDbDataAdapter – i.e. reading in a mixed data type on a “PartID” column in MS Excel, where a PartID value can be numeric (e.g. 561) or text (e.g. HL4354), even though the excel column was formatted as “Text”. From what I can … Read more
Basically, if you’re on a 64-bit machine, IIS 7 is not (by default) serving 32-bit apps, which the database engine operates on. So here is exactly what you do: 1) ensure that the 2007 database engine is installed, this can be downloaded at: http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en 2) open IIS7 manager, and open the Application Pools area. On … Read more
There are many articles about writing code to import an Excel file, but this is a manual/shortcut version: If you don’t need to import your Excel file programmatically using code, you can do it very quickly using the menu in SQL Server Management Studio (SSMS). The quickest way to get your Excel file into SQL … Read more
Take a look at Linq-to-Excel. It’s pretty neat. var book = new LinqToExcel.ExcelQueryFactory(@”File.xlsx”); var query = from row in book.Worksheet(“Stock Entry”) let item = new { Code = row[“Code”].Cast<string>(), Supplier = row[“Supplier”].Cast<string>(), Ref = row[“Ref”].Cast<string>(), } where item.Supplier == “Walmart” select item; It also allows for strongly-typed row access too.