The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any inform

SQL Bulk insert from Excel 2003 - Microsoft.Jet. OLEDB.4.0

 

--http://www.sqlteam.com

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 

    'Excel 8.0;Database=D:/testing.xls;', 

    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 

into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 

    'Excel 8.0;Database=D:/testing.xls;HDR=YES', 

    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 

    'Excel 8.0;Database=D:/testing.xls;HDR=YES', 

    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 

	@outputfile = 'd:/testing.xls', 

	@query = 'Select * from Database_name..SQLServerTable', 

	@colheaders =1, 

	@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)


5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns

(

	@db_name	varchar(100),

	@table_name	varchar(100),	

	@file_name	varchar(100)

)

as



--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select 

	@columns=coalesce(@columns+',','')+column_name+' as '+column_name 

from 

	information_schema.columns

where 

	table_name=@table_name

select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')



--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('/',reverse(@file_name)))+'/data_file.xls'



--Generate column names in the passed EXCEL file

set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''

exec(@sql)



--Generate data in the dummy file

set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''

exec(@sql)



--Copy dummy file to passed EXCEL file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

exec(@sql)



--Delete dummy file 

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'








SQL Bulk insert from Excel 2007 - 

ACE 







Office 2007 is ACE - not JET






so try :



This should work - just make sure you are not in Excel when you try to connect...





EXEC sp_addlinkedserver MyExcel,

     'ACE 12.0',

     'Microsoft.ACE.OLEDB.12.0'

,
     'c:/DistExcl.xls',                          
      NULL ,
     'Excel 12.0;'

--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin MyExcel, FALSE, NULL , Admin, NULL

--now can select from your spreadsheet (oh and to see what "tables" are in there, use EXEC sp_tables_ex MyExcel )
select * from myexcel...Sheet1$
 
-- remove server
sp_dropserver 'MyExcel', 'droplogins';


But, can also try :

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:/DistExcl.xls;HDR=YES', 'SELECT * FROM [sheet1$]')