Clever Geek Handbook

SQL Syntax Error calling PowerShell

I have PowerShell code that I am using in a SQL Server Agent job. The job itself connects to the FTP site and pulls out all the filenames in a specific directory.

PowerShell works fine when run through PowerShell ISE without error, but when run in SQL it throws a syntax error.

The person of the violation is the first line, which

$FTPRequest = [System.Net.FtpWebRequest]::Create("$($Server)$($Directory)")

I did some research and found that SQL doesn't like it $($

and removes the first 2 characters $(

. However, this will not work, because immediately another comes up, which I also deleted, and now the code works, but does nothing.

The error displayed in the job history is

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

Any ideas on how to get this to work in SQL?

+3


source to share


2 answers


The subexpression operator is $()

not needed on this line because PowerShell correctly expands the two variables to strings:



$FTPRequest = [System.Net.FtpWebRequest]::Create("$Server$Directory")
+2


source


You are getting the most error because of the SQL Server Agent User Tokens . SQL Server Agent treats it $()

as a special character sequence and, if found, will try to resolve the value within a specific user token. Since one of them doesn't match, you get a syntax error. Obviously, PowerShell views this sequence in a completely different way, so running the same code in ISE does not result in an error.



The easy answer is when it comes to using PowerShell in the SQL Server Agent job steps, you cannot use subexpression syntax in your code. You will need to recode it. A little more experience in my experience.

+2


source






More articles:


All Articles