sql query error

baskaran chellasamy

14y1.5k

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi14y ago
Check this one

http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx
baskaran chellasamy14y ago
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 Shaikh14y ago
once you have modified the code in your procedure to match the solution, you can run the procedure in a normal fashion.
baskaran chellasamy14y ago
How can i run this procedure. is this as usual way to run normal store procedure?
Akkiraju Ivaturi14y ago
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, day,reason from [sheet1$])')
Javeed M Shaikh14y ago
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