Made me think this would be quite easy... so
1. I created a Excel Sheet named testfile.xls with
the column headings from my temp table
2. Saved and closed this xls
3. Tried to run the following:
USE [MainAdmin];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\RAJ Infotech\testfile.xls',
'SELECT * FROM
[Sheet1$]')
SELECT * FROM MainAdmin.dbo.Table_1
GO
Where E:\Raj Infotech\testfile.xls is where I
saved test.xls, Table_1 is the table I
have populated in the firstplace and MainAdmin is the database name.
When I run this the following error crops up:
OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "The Microsoft Jet database engine could not find the
object 'Sheet1$'. Make sure the object exists and that you spell its name
and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB
provider "Microsoft.Jet.OLEDB.4.0" for linked server
"(null)".
Before running either of these 'export' queries I
did as instructed in the first link:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Which produced results:
Configuration option 'show advanced options'
changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries'
changed from 0 to 1. Run the RECONFIGURE statement to install.
I assumed from that I had successfully enabled Ad
Hoc Distributed Queries - Is that correct?
Both methods seem to throw up a similar error, an
error which makes me think its some kind of SQL authentication issue.
Has anyone successfully exported from SQL to Excel
- if so, any tips?!