Hi friends
This is my stored procedure
alter procedure sp_excelforgeneralholidays(@filname nvarchar(max))
as
declare @datavar varchar(200)
declare @sql varchar(500)
set @datavar = 'Excel 8.0;DATASOURCE=' + @filname
set nocount on
begin
--create table #temptable (Date date ,Day varchar(30),Reason varchar(100))
set @sql = SELECT * FROM OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','+@datavar +;HDR=YES','SELECT Data,Day,Reason FROM [Sheet1$]')
exec (@sql)
end
In the parameter of storedprocedure I just pass filename with their location. But this query is not run correctly.please rectify the error if i made the error.
and the error when i run this storeprocedure
Msg 156, Level 15, State 1, Procedure sp_excelforgeneralholidays, Line 10
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure sp_excelforgeneralholidays, Line 10
Incorrect syntax near ','.
Loading
hj jhPosted Aug 23, 2012, 7:10 AM
Set @sql =(select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES;',
'SELECT * FROM [Sheet1$]'))
try giving local file name statically for testing & if it works then replace it by your @datavar accordingly
Let me know if any problem!