Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server PowerShell Extensions (SQLPSX) Part 2


SQL Server PowerShell Extensions (SQLPSX) Part 2

Author
Message
cmille19
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
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.



Fatherjack
Fatherjack
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 627
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 .... !!!")
cmille19
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
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'"



Fatherjack
Fatherjack
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 627
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.
valenzat
valenzat
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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?
cmille19
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
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.



valenzat
valenzat
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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)
cmille19
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
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?



valenzat
valenzat
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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?
cmille19
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
Are CSV files on the local SQL Server? Is the SQL Agent running under Local System or a Windows account?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search