sql query error

Question

Hi friends
this is my store procedure for getting external excel file and save into sql database.
create procedure sp_excelforgeneralholidays(@filname nvarchar(max))
as
set nocount on
begin
select * from OPENROWSET('Microsoft.jet.OLEDB.4.0','Excel 8.0;DATABASE=' + @filname + ', Select Date,Day,Reason from [sheet1$])'
end
When i Run this error is near syntax error + . but i dont know how to solve this problem please help me

Akkiraju Ivaturi · Answer

Check this one http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

baskaran chellasamy · Answer

This is the error. SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries' see Surface Area Configuration in SQL Server Books Online.

Javeed M Shaikh · Answer

once you have modified the code in your procedure to match the solution you can run the procedure in a normal fashion.

baskaran chellasamy · Answer

How can i run this procedure. is this as usual way to run normal store procedure?

Akkiraju Ivaturi · Answer

You cannot append strings to a select query directly. Use dynamic sql for select statement and use exec to run the query. replace select statement with the following: exec('select * from OPENROWSET(''Microsoft.jet.OLEDB.4.0''''Excel 8.0;Database='+@filename+'select date dayreason from [sheet1$])')

Javeed M Shaikh · Answer

The arguments to OPENROWSET must be a quoted string not a variable or mix. try this. create procedure sp_excelforgeneralholidays(@filname nvarchar(max)) as declare @datavar varchar(200) declare @sql varchar(500) set @datavar = 'Excel 8.0;DATABASE=' + @filname set nocount on begin set @sql = 'SELECT * FROM OPENROWSET(''Microsoft.jet.OLEDB.4.0'''''+@datavar +''' ''SELECT * FROM [Sheet1$]'')' exec (@sql) end