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 ««123»»

SQL Server PowerShell Extensions (SQLPSX) Part 2 Expand / Collapse
Author
Message
Posted Thursday, October 9, 2008 10:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
So at this point your job is completing and successfully loads the data and archives the csv files, but it does not include job step output information in the job history?

If this is the case the reason why the step output does not include job step output information for the second step is due to the implementation of some very basic threading (I'm not sure I should even call it threading). In run-SmoToCsvFile.ps1 there is a function called "LaunchThread" which uses the .NET System.Diagnostics.ProcessStartInfo to start several new independent powershell consoles running Write-SmoToCsvFile.ps1 (the number depends on your $maxthread setting). The problem with this approach is the original Powershell command executed from SQL Agent has no knowledge of the independent shells. It just starts them, kind of like sp_start_job in SQL Server. If you execute sp_start_job you only receive success or failure of the job starting and not whether the job itself completed or failed.

This causes some little problems. First as you pointed out the original session does not get back information which would ultimately show up in the job step. This is also why Write-SmoCsvToDb which does not launch additional powershell consoles returns job step output information. The second problem is that Write-SmoCsvToDb can start prior to all the independent powershell consoles running Write-SmoToCsvFile completes. To overcome the second problem I had to include a WAIT step in the job after Run-SmoToCsvFile:

WAITFOR DELAY '00:30'

For the first problem I've tried to include output files rather than use the job step output.

Admittedly the solution of launching multiple Write-SmoToCsvFile.ps1 scripts from Run-SmoToCsvFile.ps1 is less than ideal. On the plus side I'm able to reduce total runtime significantly by launching multiple processes instead of processing through each server sequentially.



Post #583470
Posted Wednesday, November 5, 2008 7:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:11 AM
Points: 279, Visits: 575
Unfortunately not, the job just generates the log files. There are no csv files created or archived. apparently the first PS script doesnt begin, just the SS job log files are created...

I am wondering whether to create a .bat or .vbs file that will run the PS scripts and then start that off from the SS job ("the knee bone is connected to the ankle bone, the ankle bone is connected to the .... !!!")
Post #597345
Posted Wednesday, November 5, 2008 2:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
This might seem obvious but your execution policy is set to remotesigned? Also you may want to just run Write-SmoToCsvFile.ps1 and pass in the server name instead of using Run-SmoToCsvFile.ps1 as a test:

C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "C:\SQLPSX\Write-SmoToCsvFile.ps1 'MyServer'"




Post #597692
Posted Thursday, November 6, 2008 2:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:11 AM
Points: 279, Visits: 575
Yes - remotesigned.

Tried the single server name in threee styles -
- SERVER\INSTANCE
- SERVER
- INSTANCE

and there is no difference in the execution. A log files gets created and archived.

no other work done.
Post #597965
Posted Monday, December 8, 2008 8:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 10:02 PM
Points: 11, Visits: 231
I am getting the same problem as Jonathan, is there a workaround for this when using the agent to run the job?
Post #615891
Posted Monday, December 8, 2008 9:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
I have dozens SQL Agent jobs in 2000 and 2005 servers which call powerhell scripts from powershell.exe. The JobStep type is set to CmdExec. Try testing a single job step:
C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "C:\SQLPSX\Write-SmoToCsvFile.ps1 'MyServer\InstanceName'"
Ensure you have all of the .ps1 files in the same directory and they all been unblocked and of course your execution policy is set to remote signed. Whenever I've had trouble running powershell.exe from SQL Agent it has been either an execution policy or script path issue.



Post #615909
Posted Monday, December 8, 2008 11:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 10:02 PM
Points: 11, Visits: 231
Thank you for your reply cmille19, the job works fine for generating the csvs but after running run-smo step im getting this:
The step did not generate any output. Process Exit Code 0. The step succeeded.
(i.e. I am getting this error when running the single job step)
The logs are also fine and the jobstep type is set to cmdexec
(execution policy is set to remotesigned)
Post #615943
Posted Tuesday, December 9, 2008 8:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
valenzat (12/8/2008)
Thank you for your reply cmille19, the job works fine for generating the csvs but after running run-smo step im getting this:
The step did not generate any output. Process Exit Code 0. The step succeeded.
(i.e. I am getting this error when running the single job step)
The logs are also fine and the jobstep type is set to cmdexec
(execution policy is set to remotesigned)


Are you able to run successfully manually outside of SQL Agent?



Post #616184
Posted Tuesday, December 9, 2008 5:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 10:02 PM
Points: 11, Visits: 231
yes it works if im not using the agent.
I also tried using xp_cmdshell with T-sql step but no luck
Im getting the csv files but nothing gets inserted to the db..am i missing something with using the agent?is there a way to troubleshoot that?
Post #616591
Posted Tuesday, December 9, 2008 6:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
Are CSV files on the local SQL Server? Is the SQL Agent running under Local System or a Windows account?


Post #616602
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse