unable to run power shell script from SQL server agen

Question

Post reply

unable to run power shell script from SQL server agen

mhafiez

Old Hand

Points: 369
More actions
September 10, 2013 at 1:39 pm

#277110

hello,
i created this power shell script to retun SQL server configurationsconfiguration, the script works fine when I run it from Powershell ISE, however it fails when i schedule it to run as power shell step in sql agent as ajob, it gives me syntax error, any ideas what I'm doing wrong?, i read some articles about assigning the get-date to a variable, i tried it without any lock
Import-Module SQLPS -DisableNameChecking
#One way to map
New-PSDrive -Name UNCPath -PSProvider FileSystem -Root \\Share\Servers\
foreach ($instancename in get-content -path UNCPath:\Servers.txt)
{
$Date =Get-date -Format "yyyy-m-d_hmmtt"
$folder= "UNCPath:\servers"
$filename = "$($instanceName)_Configuration_$($Date).csv"
$fullpath =Join-Path $folder $filename
$server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $instanceName
$server.Configuration.Properties | Export-Csv -Path $fullpath
}
remove-psdrive -name UNCPath

Viewing 7 posts - 1 through 6 (of 6 total)

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

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

try changing

$filename = "$($instanceName)_Configuration_$($Date).csv"

to

$filename = "$instanceName_Configuration_$Date.csv"

I wasn't able to get it to run in my test but making after making this change i no longer got the bad syntax error.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

mhafiez (9/10/2013)
hello,
i created this power shell script to retun SQL server configurationsconfiguration, the script works fine when I run it from Powershell ISE, however it fails when i schedule it to run as power shell step in sql agent as ajob, it gives me syntax error, any ideas what I'm doing wrong?, i read some articles about assigning the get-date to a variable, i tried it without any lock
Import-Module SQLPS -DisableNameChecking
#One way to map
New-PSDrive -Name UNCPath -PSProvider FileSystem -Root \\Share\Serversforeach ($instancename in get-content -path UNCPath:\Servers.txt)
{
$Date =Get-date -Format "yyyy-m-d_hmmtt"
$folder= "UNCPath:\servers"
$filename = "$($instanceName)_Configuration_$($Date).csv"
$fullpath =Join-Path $folder $filename
$server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $instanceName
$server.Configuration.Properties | Export-Csv -Path $fullpath
}
remove-psdrive -name UNCPath

I strongly recommend that you post the text of the exact error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mhafiez Old Hand Points: 369 More actions · Answer 3

the error message i got from sql agent history is:

step Name1

Duration00:00:00

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Unable to start execution of step 1 (reason: line(14): Syntax error). The step failed.

mhafiez Old Hand Points: 369 More actions · Answer 4

thanks Bob, i changed it to $filename = "$instanceName_Configuration_$Date.csv"

and started the sql job with the same error i had before syntax error, i can run the script inside ISE without any issues

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

i wasn't paying attention, I had the filename string incorrect.

try this:

$filename = $instanceName+"_Configuration_"+$Date+".csv"

I was able to get it to run successfully with a modified script on sql server 2008 r2. looks like you cant use import-module.

foreach ($instancename in get-content -path e:\temp\server.txt)

{$Date =Get-date -Format "yyyy-m-d_hmmtt"

$folder= "e:\temp\"

$filename = $instanceName+"_Configuration_"+$Date+".csv"

$fullpath =Join-Path $folder $filename

$server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $instanceName

$server.Configuration.Properties | Export-Csv -Path $fullpath }

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

mhafiez Old Hand Points: 369 More actions · Answer 6

mhafiez

Old Hand

Points: 369

September 10, 2013 at 4:54 pm

#1649060

Bob,

Thanks alot, it worked, appreciate it.