i m using ur's code ...but sum error i m getting..
ALTER PROCEDURE [dbo].[ex_pro]
@sheetName varchar(100),
@fileName nvarchar(200),
@HDR varchar(10)
AS
BEGIN
declare @sql nvarchar(1000)
set @sql='select *from OPENDATASOURCE '
set @sql=@sql+'('' Microsoft.ACE.OLEDB.12.0'',''data source='
set @sql=@sql+@fileName+';Extended properties=''''Excel 12.0;IMEX=1;Hdr='
set @sql=@sql+@HDR+''''''')...['
set @sql=@sql+@sheetName+']'
exec sp_executesql @sql
END
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider " Microsoft.ACE.OLEDB.12.0" has not been registered.
But when the File Path is static i.e., file path ,sheet name etc are fixed..
select *from OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;Hdr=Yes;
database=E:\GCS Appl\GCS\Data\excel file\Anugul\Anugul-Anugul.xlsx')...[code$]
It works