AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Monday, August 19, 2013

Running Excel under SQL Agent task

I have a long time running small VB6 application as SQL Agent task, which creates an Excel spreadsheet, saves it on server and sends to mail recipient. Recently my customer has upgraded both Windows Server and SQL Server to 2008 R2, and application stopped working. I found that WorkSheet.SaveAs method keep failing and producing Error 1004 (Application-defined or object-defined error), which looks like Excel has no permission to access file system. I checked user account, used to run this step, he has enough rights to destination folder, and when I run this app under that user - it works ok. Only under SQL Agent it fails! It took me 3-4 hours to google this problem and found this one - The SSIS and Excel Story Continues! I run "MMC comexp.msc", go to the properties of Microsoft Excel Application, under Identity, changed it to The Interactive User from The Launching User (which is set by default). After making this change and restart SQL Agent my application starts working. But only for a while…

I found that sometimes it works, sometimes produces same error, and sometimes can't even launch Excel. Fortunately, in the same blog I found another post on this issue Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library from SSIS, which helped me to fix this issue. You only have to create one folder:

For Windows 2008 Server x64: C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows 2008 Server x86: C:\Windows\System32\config\systemprofile\Desktop

So, looks like a configuration problem of Server 2008 R2 and Office 11, people talking about it since last year, Microsoft really could fix it for that time…

Labels: ,

3 Comments:

Anonymous John said...

I am more of a MS Access guy, fiddled a bit with the MSSQL Server but never heard about SQL Agent. From your description, this seems to be a very powerful feature, automating the execution of script it seems.

Going to explore it more.

4:45 PM  
Blogger Alex Dybenko said...

Hi John,
right, SQL Agent helps a lot in tasks automation, have a look at it!

10:28 AM  
Anonymous data visualization designer said...

Your site contains much other information that gives more knowledge and many more ideas about the topics you have given in your site.

9:33 AM  

Post a Comment

Links to this post:

Create a Link

<< Home