February 16, 2006 at 12:37 pm
This answer to this is likely very simple. I'm new to ActiveX-vb scripting in SQL. My problem...
The following simple activex script fails when run by sql agent, but works when I manually run it. Excel is installed on the server. The SQLAgent job runs under a windows account setup for DTS jobs (it has network rights to save files). Any suggestions? Thanks in advance!
-----------------------------------------------------
Function Main()
Dim appExcel
Dim Book
Dim oSheet
Set appExcel = CreateObject("Excel.Application")
Set Book = appExcel.Workbooks.open("\\server\folder\OpenOrders.xls")
Set oSheet = Book.worksheets("OpenOrders")
oSheet.Range("A3").Value = now 'Month(Now) & "/" & Day(Now) & "/" & Year(Now)
Book.Save
appExcel.quit
set oConn = nothing
set appExcel = nothing
set book = nothing
set osheet = nothing
Main = DTSTaskExecResult_Success
End Function
smv929
February 16, 2006 at 12:47 pm
I was wrong. Excel was installed on the served, but with an error. If you try to start it manually, it returns an error. I will get that resolved and try again.
smv929
February 17, 2006 at 1:49 pm
THe problem still happens even though Excel is now installed successfully. It must be security.
I can successfully manually run the DTS package while logged into the server as myself (a local administrator). However, if I run the SQLAgent job or the job runs on its own at a scheduled time, it fails. The error indicates the problem is with the activex task line...
Set Book = appExcel.Workbooks.open "\\server\folder\OpenOrders.xls")
Any suggestions? Thanks in advance.
smv929
February 17, 2006 at 9:06 pm
I had to do this once before and the problem stemmed from SQL Server using a temp directory that wasn't accessible to everyone. In this case though I was using OPENROWSET to open an Excel file. I know it's not the same, but I'll pass this along anyhow. It's from a KB article.
If you logon to the machine using the same credentials that SQL Server uses to connect to your network you can do the following.
Create a C:\Temp dircetory on the computer and allow full access to everyone.
Go to My Computer\properties
Open the Advanced tab, and Click Environmental variables.
Change the location for the TEMP and TMP directories to C:\Temp from the default setting (%USERPROFILE%\Local Settings\Temp).
logoff and logon using the normal logon.
In the article it said that SQL creates a temporary table in it's temp directory when importing Excel files, and that because the directory isn't accessible to all, it causes problems.
Like I said it's not exactly the same scenario, but it may help.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply