InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 21:38:13 GMT20http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxGreat article!I found that I also had to change the $StartInfo.WorkingDirectory = "<valid directory>" in the Run-SmoToCSVFile script. This was not mentioned in the article that I saw.Wed, 08 Jul 2009 10:46:21 GMTBryan Conlonhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxI had issues trying to run powershell from sql agent but got the following to work:(1) use the SqlCmd in the step.(2) created a batch file that containted the following:REM change to the correct driveI:REM change to my working directorycd \mydirREM now call powershell scriptC:\WINDOWS\SysWOW64\windowspowershell\v1.0\powershell.exe -Nologo -NoProfile -Noninteractive -Command "& 'I:\DataXfer\ssh\myscript.ps1'"-------------------------------------------------------NOTE: the string following the -Command you must have a "& '\pathname_script' "-------------------------------------------------------Mon, 13 Apr 2009 09:01:31 GMTdaytripperhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxI have stop all sql server services, manually added sysadmin sql server role to the windows account used by the agent and restarted all sql services and it is now working.I am not sure if that really solved the problem but it seems fine and data is correctly bulk inserted to the db! Im now going through ssrsThanks for your help ChadWed, 10 Dec 2008 00:12:35 GMTvalenzathttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxIt seems to be a known issue..http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1565378&SiteID=1&pageid=0It must something to do with permissions and the sql agent (although the account has local admin and is sa on the server). Ill keep looking and post any updates if I find anything.Tue, 09 Dec 2008 20:54:13 GMTvalenzathttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxThe CSV files are on the server and the sql agent is using a windows accountTue, 09 Dec 2008 18:53:20 GMTvalenzathttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxAre CSV files on the local SQL Server? Is the SQL Agent running under Local System or a Windows account?Tue, 09 Dec 2008 18:34:28 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxyes it works if im not using the agent.I also tried using xp_cmdshell with T-sql step but no luckIm 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?Tue, 09 Dec 2008 17:01:30 GMTvalenzathttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspx[quote][b]valenzat (12/8/2008)[/b][hr]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)[/quote]Are you able to run successfully manually outside of SQL Agent?Tue, 09 Dec 2008 08:05:50 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxThank 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)Mon, 08 Dec 2008 23:47:10 GMTvalenzathttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxI 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.Mon, 08 Dec 2008 21:26:38 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxI am getting the same problem as Jonathan, is there a workaround for this when using the agent to run the job?Mon, 08 Dec 2008 20:36:45 GMTvalenzathttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxYes - 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.Thu, 06 Nov 2008 02:57:22 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxThis 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'"Wed, 05 Nov 2008 14:14:26 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxUnfortunately 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 .... !!!")Wed, 05 Nov 2008 07:41:25 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxSo 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:[code]WAITFOR DELAY '00:30'[/code]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.Thu, 09 Oct 2008 10:31:15 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxmany apologies, i hadnt seen your two posts before mine.I downloaded the files and copied them to the server - i didnt spot that the article was different. I have them as Write-SmoCsvToDb.ps1[b]$CsvDir = "$ScriptRoot\output\" #"$scriptRoot\Data\"$arcDir = "$scriptRoot\Output\Archive\" #"$scriptRoot\Data\Archive\"[/b]Write-SmoToCsvFile.ps1[b]$dir = "$scriptRoot\output\" #"$scriptRoot\Data\"[/b]The logging actions from these files work - thats to say the .log (.err in the original files) iscreated and then collected up and deposited in the data/archive/...date... folder. Its whole contents are:2008-10-09 15:11 processCsv Login2008-10-09 15:11 processCsv ServerPermission2008-10-09 15:11 processCsv ServerRole2008-10-09 15:11 processCsv SqlLinkedServerLogin2008-10-09 15:11 processCsv SqlUser2008-10-09 15:11 processCsv DatabasePermission2008-10-09 15:11 processCsv ObjectPermssion2008-10-09 15:11 processCsv DatabaseRole2008-10-09 15:11 archiveCsv C:\SQLPSX\output\20081009the log files created by the SQL Job steps get created in a different folder (same level as Archive). SQLPSX_SMOtoCSV.log is emptySQLPSX_CSVtoDB.log contains[b] Directory: Microsoft.PowerShell.Core\FileSystem::C:\SQLPSX\Output\ArchiveMode LastWriteTime Length Name ---- ------------- ------ ---- d---- 09/10/2008 15:11 20081009 Setting the directory C:\SQLPSX\Output\Archive\20081009\ to compress new files [OK] Compressing files in C:\SQLPSX\Output\Archive\20081009\smocsvtodb.log 398 : 398 = 1.0 to 1 [OK]2 files within 2 directories were compressed.398 total bytes of data are stored in 398 bytes.The compression ratio is 1.0 to 1.[/b]The SQL job reports success on all steps now - The history for step 2 (Run-SmoToCsvFile) shows [b]MessageExecuted as user: . The step did not generate any output. Process Exit Code 0. The step succeeded.[/b]I would have expected the output of the script creating the csv files running into this message. At least in part if not in full.Sorry I cant say it's all working yet...Thu, 09 Oct 2008 08:31:12 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxHave you had a chance to try this fix?[quote][b]cmille19 (10/8/2008)[/b][hr]I see a mistake in the article where I set the archive and data directory to the same directory. Here's what I have in my production script [b]Write-SmoCsvToDb.ps1:[/b][code]$CsvDir = "$scriptRoot\Data\"$arcDir = "$scriptRoot\Data\Archive\"[/code]And here's what I have in my [b]Write-SmoToCsvFile.ps1[/b][code]$dir = "$scriptRoot\Data\"[/code]You can use the $scriptRoot variable which is set at the beginning of each script or you can specify the path. Ensure the archive and data directories exist. The Write-SmoCsvToDb.ps1 script will create a subdirectory under \Data\Archive\ for each run and NTFS compress the subdirectory.Sorry about the error -- Let me know if this works and I'll correct the article.[/quote]Thu, 09 Oct 2008 05:23:56 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxNice series ...Thu, 09 Oct 2008 01:48:18 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxOK, SQLCMD isnt the big deal, I was trying to use that to debug why the job wast working...any ideas on the reason that the csv files dont get generated, just the log files confirming that nothing has been done?!Thanks for the help so far.JWed, 08 Oct 2008 14:41:12 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxIt doesn't seem like you can run PowerShell through SQLCMD. I just tried running !!powershell -version and all I see are unprintable characters. I often run PowerShell through SQL Agent using the Operating System (CmdExec) type without issue. You do have to specify the full path of script files.Wed, 08 Oct 2008 11:11:21 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxI see a mistake in the article where I set the archive and data directory to the same directory. Here's what I have in my production script [b]Write-SmoCsvToDb.ps1:[/b][code]$CsvDir = "$scriptRoot\Data\"$arcDir = "$scriptRoot\Data\Archive\"[/code]And here's what I have in my [b]Write-SmoToCsvFile.ps1[/b][code]$dir = "$scriptRoot\Data\"[/code]You can use the $scriptRoot variable which is set at the beginning of each script or you can specify the path. Ensure the archive and data directories exist. The Write-SmoCsvToDb.ps1 script will create a subdirectory under \Data\Archive\ for each run and NTFS compress the subdirectory.Sorry about the error -- Let me know if this works and I'll correct the article.Wed, 08 Oct 2008 10:08:11 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxyup spotted that one pretty early on. still not getting it to run from the job though. :(SQLPSX_SMOtoCSV.log is emptySQLPSX_CSVtoDB.log has:Move-Item : Cannot create a file when that file already exists.At C:\SQLPSX\Write-SmoCsvToDb.ps1:75 char:10+ Move-Item &lt;&lt;&lt;&lt; "$CsvDir*.log" "$arcDir$((Get-Date).ToString(`"yyyyMMdd`"))" Setting the directory C:\SQLPSX\Output\Archive\20081008\ to compress new files [OK] Compressing files in C:\SQLPSX\Output\Archive\20081008\1 files within 2 directories were compressed.0 total bytes of data are stored in 0 bytes.The compression ratio is 1.0 to 1.SMOCSSVTODB.log file has:2008-10-08 16:16 processCsv Login2008-10-08 16:16 processCsv ServerPermission2008-10-08 16:16 processCsv ServerRole2008-10-08 16:16 processCsv SqlLinkedServerLogin2008-10-08 16:16 processCsv SqlUser2008-10-08 16:16 processCsv DatabasePermission2008-10-08 16:16 processCsv ObjectPermssion2008-10-08 16:16 processCsv DatabaseRole2008-10-08 16:16 archiveCsv C:\SQLPSX\output\20081008and thats it.the job completes with success but move zero rows of data.if i run "[b]./run-smotocsvfile.ps1[/b]" it ships the expected data, i then run "[b]./write-smocsvtodb.ps1[/b]" and SSRS shows details perfectly.I cannot run the job step in SQLCmd mode ie:"[b]!!C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "run-SmoToCsvFile.ps1" &gt;&gt; C:\SQLPSX\SQLJobLog\SqlSec_Err.LOG[/b]"Do I need to give the full location of the run-smotocsvfile.ps1 file? --&gt; c:\sqlpsx\run-smotocsvfile.ps1?running this from the Command prompt succeeds:"[b]C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "C:\SQLPSX\run-SmoToCsvFile.ps1[/b]"thanks.Wed, 08 Oct 2008 09:35:40 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxCheck the "Advanced" page under the job step "Run-SmoToCsvFile" and ensure the path to the SQLPSX.err output file exists. This is one difference between the command-line and SQL Agent execution where I'm using the output file option on job steps and probably explains why it works executing directly from command-line.Wed, 08 Oct 2008 07:57:44 GMTcmille19http://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxThis is a great way to collect the security state of my servers. Took a bit of tinkering to get it all to work - i wanted the SQLPSX data in an existing meta data database that i have so wanted all the objects in their own schema. Lots of minor changes to the script, changing [b]dbo.[/b] to [b]SQLPSX.[/b] and we got there!I cannot (yet) get the SQL job to run via SQL Agent. The 2nd step "[b]C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "C:\SQLPSX\run-SmoToCsvFile.ps1 2&gt;&1"[/b]" fails with error "[b]Executed as user: . Unable to open Step output file. The step failed.[/b]"When i run the same command from the command prompt it executes with no error so i am thinking its permissions based??Off to tweak the reports now.Many, many thanks for such a useful projectJonathanWed, 08 Oct 2008 04:51:18 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic582357-106-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/powershell/64350/">SQL Server PowerShell Extensions (SQLPSX) Part 2</A>[/B]Wed, 08 Oct 2008 00:38:14 GMTcmille19