I'm inserting into a .xls spreadsheet from TSql code. Runs fine as an Agent job and on command from the server, but not on my dev box.
Both complex and simple commands like:
Insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]') SELECT 'hello' Select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]')
SQL Server 2008 R2 64bit installed on Windows Server 2008 R2. SSMS installed, these commands work there.
When trying to run the same commands from another computer (my dev box), I can't do it, even though I've tried the suggestions below and elsewhere. I have Windows 7 64bit, SSMS 2014 installed here, but had the same issues with 2012 and 2008 R2.
When I run this command in SSMS on my dev box, does it send the entire command to the SQL server? Which security context does it run in? I really want to be able to develop these kinds of stored procs on my dev box, right now this is the only reason I ever RDP to my server, which I'd rather not do.
I've tried the steps listed here: [http://www.codeproject.com/Questions/692004/The-OLE-DB-provider-Microsoft-ACE-OLEDB-for-l] to no help.
EDIT: The error I'm getting is:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
UPDATE: Turns out installing the 2007 Access Engine allows me to work with Microsoft.ACE.OLEDB.12.0 from my dev box, but only when they're stored directly on the SQL Server. My files are stored on a separate computer (accessed via share), and I still cannot work with those files. Looks like a double-hop permissions issue, but I still can't find it.
EDIT: I can do these queries from my dev box when logged in as SA, but not as myself.