Post reply

Upgrade To powershell 3.0, xp_cmdshell can no longer find powershell.exe

  • September 11, 2015 at 11:43 am

    #321144

    This is more a post for helping others than a question, though anyone can feel free to comment.

    Yesterday I upgraded powershell to version 3.0 to take advantage of some new functionality. I have an existing job that used powershell 2.0 to script out all our sql agent jobs for a backup copy.

    Last night when the job ran, it failed with an error 'powershell.exe' is not recognized as an internal or external command,

    I was running the following commands as a test.

    declare @sql varchar(3000)

    --set @sql = 'c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -c "$Host.Version.Major"'

    set @sql = 'powershell.exe -c "$Host.Version.Major"'

    --set @sql = 'echo %path%'

    EXEC xp_cmdshell @sql

    If I used the full path, it would work. From the server itself, if I would open a command prompt and type powershell.exe it worked fine. It just seemed to be SQL that couldn't use the path variable. When I printed the path variables from within SQL I got the following results.

    C:\Oracle11g\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn; C:\Windows\System32\WindowsPowerShell\v1.0\

    As you can see, powershell path is there, at the end.

    I manually edited the path variable to move the powershell path to be first, printed path again and got the same exact results.

    Apparently SQL caches the path variables when the service starts.

    I restarted SQL and then got the path variables again. This time I got:

    C:\Windows\System32\WindowsPowerShell\v1.0\; C:\Oracle11g\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn;

    This time when I ran the command to get the powershell version it worked. Hopefully this will help someone else as the only reference I found regarding this suggested adding the full path, which I didn't want to do.

    Is anyone else aware of a limit in the length of the path variable that xp_cmdshell can use?

  • September 11, 2015 at 7:06 pm

    #1826182

    Doing a google search suggests the max length for a PATH is 260 characters.

Viewing 2 posts - 1 through 1 (of 1 total)

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