0

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

2 Answers 2

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
1
  • 1
    Thank you. Whilst the first response point to the right area. This has solved the issue. Merry Christmas
    – Ian W
    Dec 24, 2021 at 23:47
1

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.

1
  • Please see updated question
    – Ian W
    Dec 22, 2021 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.