Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Open Excel Error Using PowerShell in SQL Server Agent Job Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 7:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
I am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job step, . The script runs fine by itself, and from BIDS. I have tried running as an SSIS pkg with a proxy, and when that didn't work I setup the step to simply run the Powershell script.
This is the error I receive:

A job step received an error at line 17 in a PowerShell script. The corresponding line is
'$Workbook=$objExcel.Workbookds.Open("\\files\reporting\Dailyreport_MMDDYYYY.xls",0,$False,1,"password")'
Correct the script and reschedule the job.
The error information returned by PowerShell is: "Exception calling "Open" with "5" arguments(s).
"Microsoft Excel cannot access the file "\\files\reporting\Dailyreport_MMDDYYYY.xls".
There are several possible reasons:
The file name or path does not exist (Yes, it does!)
The file is being used by another program (No, it’s not!)
The workbook you are trying to save has the same name as a currently open workbook." (No again, no workbooks open)
Process Exit Code -1. The step failed

It appears that it does not want to open Excel, but why?

FYI: 64-bit System, 64-bit SQL 2008 R2, 32-bit Office 2010.
I have already tried adding "Desktop" to SysWoW64 and editing my DCOM settings, with no change in results.

Thanks,
Lorna
Post #1461569
Posted Tuesday, June 11, 2013 7:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 5,195, Visits: 2,818
I know that you need to run SSIS in 32-bit mode if you are using Excel to ensure that the ODBC (Jet) engine it uses (which is 32-bit) runs correctly. Have you tried running the 32-bit version of DTExec?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1462087
Posted Tuesday, June 11, 2013 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
Interesting, I tried that and now get this error:

"The process could not be created for step 1 of job...
The system cannot find the path specified" Step failed.

If I am understanding correctly, this means it could not locate (or access?) the powershell script (aka "The process")?
Post #1462247
Posted Tuesday, June 11, 2013 12:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 5,195, Visits: 2,818
I guess it really depends upon what Step 1 is. Have you tried to execute Step 1 outside of SQL Server Agent? What is it?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1462318
Posted Tuesday, June 11, 2013 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
Hi, Step 1 is to execute the SSIS Package (using 32-bit DTExec per your suggestion)

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /DTS "\MSDB\SSIS_Pkgs\ExcelModifer" /SERVER sqlreport /CHECKPOINTING OFF /REPORTING E

The package contains the Powershell script that opens and modifies Excel.

I have run the package from BIDS, and from Integrations Services with no problem.
I have run the Powershell script by itself with no problem.
However, when I run as a Package OR as a Powershell task in a SQL Job Step, I get the same result: Exception Error opening Excel.
I am using a Proxy, I have checked all the permissions I can think of, I have run all the suggestions for DCOM modifications and for creating a Desktop file.
At least this time it did something different, but I think it was a step backwards.

Any ideas are greatly appreciated at this point, Thank you!
Post #1462323
Posted Tuesday, June 11, 2013 12:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 5,195, Visits: 2,818
Have you tried to change the PowerShell script to a simpler one (that creates an empty file) to demonstrate that it isn't PowerShell per se that is the problem?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1462332
Posted Tuesday, June 11, 2013 1:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
Hi,
I haven't but I wouldn't think it would get to line 17 of the script (see original post) if Powershell was the problem?
Are you thinking that if I can get Powershell to run using the 32-bit DTExec, then it will open Excel? I will try anything at this point.

Thanks
Post #1462353
Posted Tuesday, June 11, 2013 3:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 5,195, Visits: 2,818
Perhaps not but as you have moved to calling the 32 bit executor I would prove each step to confirm that it is working. Tackling it in one go is asking to get lucky.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1462394
Posted Wednesday, June 12, 2013 7:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
Hi,
Thanks again, it ran the script but wouldn't save:

"Exception calling "SaveAs" with "1" argument(s):"SaveAs method of Workbook class failed"

The script runs fine outside of job agent, it creates an excel workbook, adds data to cells, saves, and closes.

I tried to save to C:\Temp and also to \\files\reports (location of files on original script that won't open Excel).

I am not sure what this means, although it consistently fails to complete the script (for different reasons), it does appear to run the script.
Post #1462610
Posted Wednesday, June 12, 2013 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 5,195, Visits: 2,818
Lrobinson 93181 (6/12/2013)
Hi,
Thanks again, it ran the script but wouldn't save:

"Exception calling "SaveAs" with "1" argument(s):"SaveAs method of Workbook class failed"

The script runs fine outside of job agent, it creates an excel workbook, adds data to cells, saves, and closes.

I tried to save to C:\Temp and also to \\files\reports (location of files on original script that won't open Excel).

I am not sure what this means, although it consistently fails to complete the script (for different reasons), it does appear to run the script.


A quick search pops up this: http://vwiki.co.uk/Excel_(PowerShell)


Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1462612
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse