I have been trying unsuccessfully to link my excel file to SQL server like I used to with SQL 2005.
In SQL 2008R2 I have installed the 64-bit data access components from Office 2010 to get the provider 'Microsoft.ACE.OLEDB.12.0' avilable.
When I try this command:
*FROMOPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="E:\ADUpdates\Employee List1.xlsx"; Extended properties=Excel 12.0')...[Sheet1$]
I get this error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
The Access Denied error seems to be form the worksheet and not the file itself. Evidence is that if I enter a bogus sheet name it tells me the sheet doesn't exist and after this error the file is locked open and can't be saved again from Excel until the
query windows is closed. The sheet is not protected in Excel so I don't know how access to it can be denied.
Does anyone actually have this working in SQL 2008R2?
1 Answer Found
Some minor progress on this issue by running SQL Server service under a domain account with Administrator priv on the SQL server.
This query now works in a query window:
SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="E:\ADUpdates\Employee List.xlsx"; Extended properties=Excel 12.0')...[EmployeeData];
However, creating a Linked server object for the same spreadsheet still does not work:
EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'Excel 12.0', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'E:\ADUpdates\Employee List.xlsx'
The test connection to the linked server failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST" returned message "Unrecognized database format 'E:\ADUpdates\Employee List.xlsx'.". (Microsoft SQL Server, Error: 7303)
For help, click:
Naturally, the help link provided only confirms the contempt microsoft holds for their customers by not providing any information at all.
(appologies for the weird colour and fonts, this is a microsoft product, just have to live with obvious defects).
Our evaluation version of SQL Server 2008 R2 that was installed ran out and so I have upgraded the edition to standard edition using the "Upgrade Editions" Wizard.
The SQL server upgrade was successful without any errors. This is a single server with SQL Server 2008 R2 and Search Server 2010 Express installed.
After rebooting now the search service is now broken. End users get the following message when when trying to view results page: -
Internal server error exception:
Troubleshoot issues with Microsoft SharePoint Foundation.
Correlation ID: 1507c5db-7840-418b-8e9b-f6a2dba21c3e
Date and Time: 1/19/2011 2:50:44 PM
When I go into Central Administration > Search Service Application: Search Administration The following is displayed in the System Status:
Crawl status The search application 'Search Service Application' on server WDCSEARCH did not finish loading. View the event logs on the affected server for more information.
Background activity The search application 'Search Service Application' on server WDCSEARCH did not finish loading. View the event logs on the affected server for more information.
Recent crawl rate 0.00 items per second
Searchable items All Errors
Recent query rate 0.00 queries per minute
Propagation status Idle
Default content access account The search application 'Search Service Application' on server WDCSEARCH did not finish loading. View the event logs on the affected server for more information.
Contact e-mail address Proxy server None
Scopes update status Idle
Scopes update schedule Automatically scheduled
Scopes needing update 0
Search alerts status On Disable
Query logging On Disable
There are no items to show in this view.
Search Application Topology
Category Server Name Status
Administration Component WDCSEARCH Online
Crawl - WDCSEARCH\Search_Service_Application_CrawlStoreDB_759aedc2023642b7bb108dbf3f5d752b
Crawl Component 0 WDCSEARCH Online
Administration Database : Search_Service_Application_DB_1a5cf294b0254df4a712b56b9f8bb479 WDCSEARCH
Crawl Database : Search_Service_Application_CrawlStoreDB_759aedc2023642b7bb108dbf3f5d752b WDCSEARCH
Property Database : Search_Service_Application_PropertyStoreDB_c960e2c456924b7490c252928b8680eb WDCSEARCH
Index Partition - 0 - WDCSEARCH\Search_Service_Application_PropertyStoreDB_c960e2c456924b7490c252928b8680eb
Query Component 0 WDCSEARCH Online
Microsoft PartnerRead more...
I have 2 identical brand new servers Running Windows Server 2008 R2 not on a domain but simple LAN. I Installed a default instance of SQL server 2008 R2 enterprise on each one with all the default settings. No Problems during the Installaion. I Added
the Windows Administrators Group and Users group to the SQL admins role during the SQL Server Installation. Remote connections are enabled on each server. I restored the same database from a copy on each Installation. Configured the user accounts
on each maching and made them sysadmins and granted permissions where they were needed. Now from server A when i try to establish a connection to Server B from SQl server Management Studio I get An error stating that the server was not found or accesible.
keep in mind i can \\server B\c$ and vice versa, named pipes are enabled. I have never had this problem before in my life. And it's happening on both servers. I really need each installation of SQL to be able to see the other
remote installation as i have to create a mirror.
I've believe I've installed everything to read Mobile SQL .SDF files. I've insalled Sql Server Management Studio, Sql Server 2008 R2, .NET 4.0 Framework and just about everything else I've read to see the contents of an .SDF file created for what
I read directly from these product sources as this DotNet Compact Framework is the greatest new SQL stuff on the planet. I can connect using the Management Studio and see the TABLE Stucture easy enough and I can query using some simple SQL Commands from
the Management Studio but what really bugs me is if this is all so wonderful, how come I cannot connect to what the SQL Server 2008 R2 Server type "SQL Server Compact" Authentication: "SQL Server Compact Authentication in Office 2010 Access, Excel
or even "WOW" PowerPivot! I've run across a couple 3rd party .SDF Readers - one that exports to Excel such as flyhoward.com I'm growing old with all the work around import export stuff and just want to use PowerPivot to excersise the
data......or should I use something that actually works like Tableau??Read more...
I have been sent some Excel files in xlsx format, but when attempting to open them in Excel 2010, I get a file corruption error and neither of them will open. However I can open the same files in Excel 2003 (SP3) using the File Converter with no corruption
of the data that I can see. There are no embedded objects, not even any formulas, just data 5 columns wide by 60K rows. I am using Windows XP Pro SP3
Has anyone else encountered this?Read more...
I'm having trouble in opening .xlsx files created using excel 2007. File is on the sharepoint site (WSS 3.0). Since I have installled Office 2010 I am not being able to open this file directly from the browser. However, if I download it on my local PC its
seems to open fine.
Excel 2010 shows error "Excel cannot open file "filename" because the file extension or file format is not correct. Verify the file is not corrupted..."
Please can someone suggest what I am missing on my sharepoint site?
I've posted this question in a MS Access forum but nobody was able to figure out what the issue is.
I am getting the following error when I try to link MS Access 2010 to a SQL Server Express 2008R2 table:
'idx_table name' is not a valid name. make sure it does not include invalid characters or punctuation and that is not too long.
- the table name is: italpasta-etp.etp.timehistory
- the connection: 64bit ODBC (test successful)
- I get the same error if I try to import the table
- both MS Access and SQL Server Express are new, 64bit installations (I don't know if that matters)
I tried changing the SQL table name to a single word (like "TEST") but it didn't make any difference.
Anyone came across a similar issue?Read more...
Any suggestion is appreciated.
I'm trying to create a linked server from SQL Server r2 to SQL Server 2005 (both 64 bit Enterprise) using the following script
@server = N'TestLS'
I verified that the Kerberos is working.
When I try to run a four part query as below it gives me the error
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI10" for linked server "TestLS" returned message "Invalid connection string attribute".
If I try to set up the linked server against localhost (i.e. SQL 2008 R2, it works just fine).
there is any oppurtunity to export data from Report (R2) to .xlsx, not .xls ?
It depends on servers drivers or installation options? We migrated to R2 and expected that there will be expert to xlsx, because this format support more rows that xls, but it doens't happened..Read more...
I need some way to read in an Excel 2007 file (xlsx or xls) to a SQL database. What's the best method to do this? Do I convert to XML first? Can I read in an XLS file instead? Or is there a more suitable format to convert to? The Excel file contains a very large table too large to print (except in large format printing). All I want is to store this data in a database that, when queried, returns the same data in a table, but in different ways. And are there some sample C# code that I could use, at least to show how to read the file in and write it to the database? I can do everything else. I'm using SQL server 2008 Developer edition. Thanks.Read more...
Secure Store Service (SSS) on SharePoint is configured. All connectivity between TFS and SP works. Data source connection in Excel report is correctly configured to use SSS ID as configued in SharePoint. Excel reports work when viewed in the Portal.
Clicking on "Open in Excel" while viewing report in the Portal launches Excel and the report loads but does not work and fails with the following error:
"Initialization of the data source failed.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database."
Any idea what may be happenning? I've looked at logs and I cannot see what's causing the problem. I will double check the Excel Web Services logs to see if I can find something more.
Leo Vildosola - InCycle SoftwareRead more...
I am probably doing something stupid BUT I am creating a linked server to an excel file, I have done this on sql2005sp2x64 using the ace ole 12 provder, and replicated on another machine running sql2005sp2x32 using the jet4 provider and I am having the same
problem on both. I have turned allow inprocess option on on both providers. I as an administrator on the machine where the database and datafile are residing have no problem accessing the linked server and the file underneath however any enduser
that is not in the local administrators group gets the error Cannot initialize the data source object of OLE DB provier "Either provider I have tried" for linked server SQL Server Error 7303. I have checked file permissions,
and they are open to the world, I have tried both use existing authentication and use no authentication when accessing the linked server. and I have tried endless different combinations of providers and provider strings. I have went as far as having
the enduser create a linked server to an excel file on his local machine (which he is admin on and I am not) and he can access the file .. and I can not. so I am just assuming there is some sort of security setting somewhere .. that I am
I have a xlsx document which contains Forms and Modules and plenty of VBA code. I am trying to import this into VS 2010 by doing the following.
File -> New Project -> Excel 2010 WorkBook
When the next dialog box appears I select the Copy an Existing document and browse to the locatoin of this xlsx document.
It seems like it imports is successfully but none of the forms or any of the modules or code is appearing.
In the solution explorer it is only showing the sheets but absolutely no code. Any ideas why? :(
The above setup is in vmware. The 2-node cluster works fine. Able to failover to and fro. Just that when I tried to change the port 1433 to 12345, the cluster is not able to online the SQL Service. Below is the application error log:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 274d; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
The service status becomes Pending Online and after a while, it becomes offline. Using the SQL Configuration Manager, I revert to 1433 and it works fine again. May I know what am I missing ?
(1)I have a working WPF 2010 program that manipulates a table in SQL Server 2008 Express
(2) the program does delete, update, select and insert correctly however
in its own project file folder logbooklmk.mdf logbooklmk_log.ldf
(3) I want the actual SQL Server 2008 express files to be changed
yes I know the issue is sharing and the copy option only applies to the local project files.
I found this out by trying always copy, never copy and copy if newer.
I probably have to change the .xsd folder to refer to the actual SQL Server 2008 Express file.
Tried that once and it failed. Something about sharing not allowed.
Hi. I have had an issue trying to get distributed transactions to work in SQL Server 2008 using a linked server. The error message I get is
OLE DB provider "SQLNCLI10" for linked server "pod1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "pod1" was unable to begin a distributed transaction.
Windows Server 2008 Enterprise 64 bit, SQL Server 2008 Enterprise 64 bit
Problem occurs with multiple different builds of SQL Server 2008 - I have been able to reproduce the problem with 10.0.1600.0, 10.0.1779.0 (CU 2), as well as 10.0.2531 (SP1)
I am aware that other people have had this issue and have reviewed all existing posts. I have verified that the MSDTC is configured correctly on both machines. I have also used DTCTester to verify that DTC is working correctly on all machines in question.
None of the mentioned resolutions has solved this problem for me.
I am not seeing this problem occur on my 32 bit test machines - it is only occurring if at least one of the two machines is 64 bit.
Hi...our organization is upgrading to sharepoint 2010 from an earlier version. In the earlier version, it was very easy to link to a document in a sharepoint library, from MS Access or Excel. But now, on 2010 version, simply linking to the URL
of the document no longer works. It's like the file does not really exist in the location noted if you grab a shortcut URL.
Does anyone have any idea how we can continue to link to sharepoint 2010 documents, so all of our nicely linked access and excel processes continue to work
Hopefully I am making sense?
Had posted this question in another forum and someone suggested me to post here.
I am reading an excel file (xlsx) which is in Office Open zipped format. I am using classes in System.IO.Packaging domain for it.
Qns - Before I open the excel file, i want to check whether the file is in zipped excel format or not. How do I do this?
I could not find any class which checks whether the given file is in zipped format (of Office Open) or not.
Qns - If there is a way to do this, Is it possible to do this check without opening the excel file ?
We have created an excel addin that allows the user to create a copy of their excel work into a completely new file without having to save the original changes (using SaveCopyAs(..)). This works really well for xls files, however if the user (using Excel 2003 SP2 with the compatability pack) opens an Excel 2007 workbook (.xlsx) and clicks our addin button, the new file is not created correctly.
Both examples below use the same code:
Example 1 (working): User opens original.xls, makes changes, clicks our addin button that creates a new file with the the content of the workbook. newfile.xls is created and works.
Example 2 (not working): User opens original2007.xlsx, makes changes, clicks our addin button that creates a new file with the contents of the workbook, new2007file.xlsx is created, however, the user is not able to open new2007file.xlsx. this error is shown when the user attempts to open the new file:
"Excel cannot open the file 'new2007file.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."
If I change the file extension to .xls, it will open just fine. If anyone knows how I can continue to use the SaveCopyAs(..) function and keep the file as a .xlsx and have it open, I would be greatly appreciative!
This is Excel 2003 (11.8033.8036) SP2 with the compatibility pack installed.
I am importing excel file to my c# application... Here is problematic code:
Row row = worksheetPart.Worksheet.Descendants<Row>().Where(r => i == r.RowIndex).FirstOrDefault();
int j = 0;
if (row != null)
foreach (Cell c in row.ChildElements)
// DO SOMETHING.....
row.ChildElements does not contain empty cells. How can I force load all cells?