Calling a powershell script using xp_cmdshell with parameter in T-SQL

Question

I have been using the following references for this Calling powershell function from T_SQL. This is on SQL server 2016

The element I need help on is the format of the call in SQL to the powershell script passing in the parameters (filepath / file name / file extension). The ps script does a simple rename and move on file which has been used in a bulk upload.

The ps script is included at the bottom of the question but I have tested the file indepently of SQL and it runs as expected

The T-SQL I am developing is

    --Turn on cmd shell may need a sysadmin account
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE

Declare @sqlFilePath nvarchar(255)='"C:\BPA Exports\Change Point 1\Processing\"'
Declare @sqlfilename nvarchar(255)='ExportCP1Data'
Declare @sqlfilext nvarchar(20)='.csv'
Declare @PScmd nvarchar (255)
Set @PScmd='powershell -command "C:\SQL_Powershell\General\MoveFile.ps1" "'+ @sqlFilePath +'" "'+@sqlfilename+'" "'+@sqlfilext+'"'
Print @PScmd
EXEC xp_cmdshell @PScmd


--Turn off cmdshell for safety
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE


EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

The error message I received

The string is missing the terminator: ".
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordEx 
   ception
    + FullyQualifiedErrorId : TerminatorExpectedAtEndOfString
 
NULL

As this will hopefully end up in a usp called remotely could someone also confirm the recommended permissions for the db user.

I am thinking of using a AD user with file r/w permissions on the local locations of the ps script and the folders the files will moved from / to. My train of thought at the moment for the SQL usp is to use something like

CREATE PROCEDURE Utility.DoSomethingPotentiallyScary
WITH EXECUTE AS OWNER
AS

unless there is a better way?

The powershell script for this is as follows

 param
(
[String] $location,
[String] $filename,
[String] $filext

) 


Function RenameMoveFile($locationPath, $fileName, $extension, $archiveFolder)
{
    $date =Get-Date -Format "yyyyMMdd_HHmmss"
    $old = $locationPath + $fileName + $extension
    $new = $locationPath + $fileName + "_" + $date + $extension
    $archiveFolder = $locationPath + $archiveFolder + "\"
    Rename-Item $old $new
    Move-Item $new $archiveFolder
}
## This is the only part that we'd edit  
RenameMoveFile -locationPath $location  -fileName "ExportCP1Data" -extension ".csv" -archiveFolder "Archive"

---

Update I have done some testing and I believe it is the white space in the file path causing the issue. I have put this into ISE for testing and if I hardcode the file path within the ps file the command works. However when I try this in ISE powershell "C:\SQL_Powershell\General\MoveFile.ps1" "C:\BPA Exports\ChangePoint1\Processing" I get the following msg (note the \ positioning)

'C:\BPA\Exports\ChangePoint1\Processing' does not exist.

Can anyone suggest the way to escape this file path?

Thank you

Answer 1

Ok. Turns out that this is neither CMD.EXE parsing nor PowerShell parsing. Instead it's the rules for parsing command line arguments from the OS, documented here.

A double quote mark preceded by a backslash (") is interpreted as a literal double quote mark (").

main function and command-line arguments

This is explained in the Powershell docks here: about_Parsing : Passing arguments that contain quote characters

So the first argument to the powershell script becomes:

C:\BPA Exports\Change Point 1\Processing" -filename ExportCP1Data -filext .csv 

And the other two are null. So when passing a command line argument ending in \" you must double the \ or omit it. So something like:

Declare @sqlFilePath nvarchar(255)='C:\BPA Exports\Change Point 1\Processing'
Declare @sqlfilename nvarchar(255)='ExportCP1Data'
Declare @sqlfilext nvarchar(20)='.csv'
Declare @PScmd nvarchar (255)
Set @PScmd='powershell -File "C:\SQL_Powershell\General\MoveFile.ps1" -location "'+@sqlFilePath+'" -filename "'+@sqlfilename+'" -filext "'+@sqlfilext+'" '

Print @PScmd
EXEC xp_cmdshell @PScmd

Answer 2

If you look at the line that your print command return, it looks like this:

powershell -command "C:\SQL_Powershell\General\MoveFile.ps1" ""C:\BPA Exports\Change Point 1\Processing\"" "ExportCP1Data" ".csv"

Note the "" before and after the path... I guess those should be simple " (not double).

Take them out of you @sqlFilePath variable and try again.