<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Chad Miller
»
SQL Server PowerShell Extensions (SQLPSX)...
25 posts, Page 2 of 3
««
1
2
3
»»
SQL Server PowerShell Extensions (SQLPSX) Part 2
Rate Topic
Display Mode
Topic Options
Author
Message
cmille19
cmille19
Posted Thursday, October 09, 2008 10:31 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:46 AM
Points: 257,
Visits: 671
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
Fatherjack
Fatherjack
Posted Wednesday, November 05, 2008 7:41 AM
SSC Veteran
Group: General Forum Members
Last Login: 2 days ago @ 12:53 PM
Points: 273,
Visits: 451
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
cmille19
cmille19
Posted Wednesday, November 05, 2008 2:14 PM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:46 AM
Points: 257,
Visits: 671
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
Fatherjack
Fatherjack
Posted Thursday, November 06, 2008 2:57 AM
SSC Veteran
Group: General Forum Members
Last Login: 2 days ago @ 12:53 PM
Points: 273,
Visits: 451
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
valenzat
valenzat
Posted Monday, December 08, 2008 8:36 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, April 04, 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
cmille19
cmille19
Posted Monday, December 08, 2008 9:26 PM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:46 AM
Points: 257,
Visits: 671
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
valenzat
valenzat
Posted Monday, December 08, 2008 11:47 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, April 04, 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
cmille19
cmille19
Posted Tuesday, December 09, 2008 8:05 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:46 AM
Points: 257,
Visits: 671
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
valenzat
valenzat
Posted Tuesday, December 09, 2008 5:01 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, April 04, 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
cmille19
cmille19
Posted Tuesday, December 09, 2008 6:34 PM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:46 AM
Points: 257,
Visits: 671
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 »
25 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
