Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Agent and Active X script

Posted on 2014-08-13
1
Medium Priority
?
555 Views
Last Modified: 2014-08-13
Hello Experts Exchange
I have a ActiveX Script and I'm trying to get it to work in a SQL Agent task.

The code is the following;
strExcelFile = "F:\Graham Rock\OEE Scripting\MouldingBlue561.xls"
 strMacroName = "Macro1"
 Set objExcel = CreateObject("Excel.Application")
 Set objWB = objExcel.Workbooks.Open(strExcelFile)
 objExcel.Visible = True
 objWB.Application.Run strMacroName
 objWB.Close False
 objExcel.Quit

Open in new window


The script works when I run it as a VBS file.

But I get the following error when I try to save it to a SQL Agent task;

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The command script does not destroy all the objects that it creates. Revise the command script. (.Net SqlClient Data Provider)


Can anyone help me to resolve the error please?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
1 Comment
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40257989
I would run the macro when the user opens the Excel file..

But your error should vanish, when you free the pointers at the end of the script:

Set objWB = Nothing
Set objExcel = Nothing

Open in new window


Caveat: under what account does the job runs? objExcel.Visible = True is necessary for some operations in Excel, but some service accounts do not interact with the desktop, thus these actions cannot be executed succesfully.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Integration Management Part 2

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question