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