SQL Server PowerShell Extensions (SQLPSX) Part 2

Question

SQL Server PowerShell Extensions (SQLPSX) Part 2

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

cmille19 SSCertifiable Points: 5970 More actions · Answer 1

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 Old Hand Points: 349 More actions · Answer 2

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 SSCertifiable Points: 5970 More actions · Answer 3

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 Old Hand Points: 349 More actions · Answer 4

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 SSCertifiable Points: 5970 More actions · Answer 5

Are CSV files on the local SQL Server? Is the SQL Agent running under Local System or a Windows account?

valenzat Old Hand Points: 349 More actions · Answer 6

The CSV files are on the server and the sql agent is using a windows account

valenzat Old Hand Points: 349 More actions · Answer 7

It seems to be a known issue..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1565378&SiteID=1&pageid=0

It 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.

valenzat Old Hand Points: 349 More actions · Answer 8

I 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 ssrs

Thanks for your help Chad

daytripper Valued Member Points: 51 More actions · Answer 9

I 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 drive

I:

REM change to my working directory

cd \mydir

REM now call powershell script

C:\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' "

-------------------------------------------------------

Bryan Conlon SSC Enthusiast Points: 118 More actions · Answer 10

Great article!

I found that I also had to change the $StartInfo.WorkingDirectory = "" in the Run-SmoToCSVFile script. This was not mentioned in the article that I saw.