Unable to open excel files using a cscript in SQL Server Jobs
· Disclaimer : Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
· The following article discusses the complications that developers may face. The article also offers alternatives to Automation that can speed performance. Developers should be aware, however, that the suggestions that this article provides are for informational purposes only.
https://support.microsoft.com/kb/257757
· Please follow the steps provided in the “Resolution” section only if you have a critical business requirement to resolve the issue discussed below in the “Problem Description” section.
Problem Description
************************
· SQL Server jobs are used to execute a cscript
· This cscript tries to open an excel file
· Though the job executes successfully the excel file was not opened and an error was logged in the Job history
· The Type of SQL job is "operating system (cmdexec)"
· Command used in the SQL Job is
o cscript "c:\sample\sample.vbs"
***<Sample CSCRIPT>***
option explicit
Dim m_oExcel
Dim m_oWorksheet
Dim oWB
Dim oSheet
Dim m_sTemplate
m_sTemplate = "c:\sample\sample.xls"
WriteOut " "
WriteOut ">>>>>>>>>>>>>>>>>>>>>>>Starting Excel at " & cstr(now) & " >>>>>>>>>>>>>>>>>>>>>>>"
WriteOut " "
Set m_oExcel = CreateObject("Excel.Application")
'm_oExcel.Visible = False
'm_oExcel.ScreenUpdating = False
m_oExcel.Visible = True
m_oExcel.ScreenUpdating = True
WriteOut ">>>>>>>>>>>>>>>>>>>>>>>Pre-Open"
Set oWB = m_oExcel.Workbooks.Open(m_sTemplate, 3)
WriteOut ">>>>>>>>>>>>>>>>>>>>>>>File Opened "
Set oSheet = oWB.Worksheets(1)
'm_oExcel.Visible = True
'm_oExcel.ScreenUpdating = True
m_oExcel.DisplayAlerts = False
m_oExcel.Quit()
Set m_oExcel = Nothing
'End of main code
'Begin of function definitions
Function WriteOut(sString)
WScript.Echo sString
End Function
***</Sample CSCRIPT>***
· The error in the sql job history is
Date 4/9/2010 8:55:21 AM
Log Job History (test)
Step ID 1
Server <Server name>
Job Name <Job name>
Step Name <Step name>
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: <User name>. Microsoft (R) Windows Script Host Version 5.7 Copyright (C) Microsoft Corporation. All rights reserved.
Microsoft Office Excel: Microsoft Office Excel cannot access the file '<location>\<filename>.xls'.
There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook. Process Exit Code 0. The step succeeded.
· When tried to run the cscript from the command it would open the xls file and write value into it
· We tried to reproduce the issue on a 32bit SQL Server and got the same error
Resolution
************
· A “Desktop” folder seems to be necessary in the “systemprofile” folder in the location C:\Windows\SysWOW64\config\ to open an Excel file
· Create the “Desktop” folder for Windows 2008 Server (x64) under the location C:\Windows\SysWOW64\config\systemprofile
· And for a 32 bit Windows 2008 Server create the “Desktop” folder under the location C:\Windows\System32\config\systemprofile
· After creating the folder the SQL Server jobs should execute successfully
Bharath Kumar
SE, Microsoft SQL Server
Reviewed by
Shamik Ghosh, Akbar Farishta & Amit Banerjee
CSS , Microsoft SQL Server