0

I have a stored procedure that I'm trying to export to excel.

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\sales.xls;;HDR=YES', 'SELECT * FROM [Sheet1$]') 
   exec des_z_test '19FDA2C7-E494-E411-80D2-0050568C3F74', 'E1AB4FD4-4C3B-E411-80D2-0050568C3F74'

However, I'm getting this error. The stored procedure runs fine on it's own. I suspect the issue is from the insert statement

Msg 15281, Level 16, State 1, Line 1
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', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tjcinnamon
  • 345
  • 2
  • 7
  • 20
  • Looks similar to this question: http://stackoverflow.com/questions/14544221/how-to-enable-ad-hoc-distributed-queries. Are you sure your SQL Server has this feature enabled? – djangojazz Feb 26 '15 at 16:56
  • 1
    Sounds like a job for Integration Services! – Zane Feb 26 '15 at 17:04
  • I'm in a huge enterprise (state government), I can't touch the servers at all. I was curious if there was a way around it. – tjcinnamon Feb 26 '15 at 17:40
  • As @Zane said, if your server has Ad Hoc Distributed Queries disabled, which appears to be the case, your best bet would be to create an SSIS job to handle the export. – Jeff Beese Feb 26 '15 at 18:35
  • @Zane could you put yours as an answer and I'll mark it as such? – tjcinnamon Feb 26 '15 at 19:58

1 Answers1

0

Zane answered below, "Sounds like a job for Integration Services!"

tjcinnamon
  • 345
  • 2
  • 7
  • 20