Sql-server – SQL Agent Job step using Powershell can not find Network Share

powershellsql serversql-server-agent

I have a SQL Job step running powershell that cannot find a network drive in one of its steps For brevity, I am calling this server Production, the network share is D:\ on Production and the Job is running on Production. The powershell Job step with issues in running under the SQL Server Agent Service Account.

The first Job step creates an input file on a network share. The second Job step queues an email based on information in the first steps output file. This job is run on server we will call Production. The network share is based the server were the Job is run specifically what we will call here the D:\ drive. When writing the Job step if I use SELECT-STRING .. -Path "\\network share.." my job fails it cannot find the Path, but if I use SELECT-STRING .. -Path "D:.. it runs successfully!

I kept the Job tokens in the code below in case the option for creating a script comes up. I have a solution here, but there is a possiblity I will need to use this script on a different server that does not a have a D:\ and I want to use the network share name for maintainability.

First Job step creates file(run as CmdExec):

SQLCmd -S <server> -U <me> -P <Passw0rd> -d <DB> -w 1000 -m-1 -b -i "\\<network share>\Results\20120117_02 Err Message Monitor.sql" -o "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Second Job step run as Powershell(specific problem line listed below)

$sel = SELECT-STRING "Nothing to do" -path "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"
if ($sel -eq $null) {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'My@email.com',
    @Subject = '(Prod) Message Monitor',
    @AttachmentList = '\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}
else {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'Me@email.com',
    @Subject = 'Nothing to do (Prod) Message Monitor',
    @AttachmentList = '\\<network share\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}

Problem is here

$sel = SELECT-STRING "Nothing to do" -path "\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

This works:

$sel = SELECT-STRING "Nothing to do" -path "D:\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"
if ($sel -eq $null) {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'My@email.com',
    @Subject = '(Prod) Message Monitor',
    @AttachmentList = '\\<network share>\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}
else {
    Invoke-Sqlcmd -Query "
SET QUOTED_IDENTIFIER ON;
EXEC DBATools.dbo.s_DBAEmailINS
    @ToList = 'Me@email.com',
    @Subject = 'Nothing to do (Prod) Message Monitor',
    @AttachmentList = '\\<network share\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt';"
}

There are no Proxy accounts.

How can I get my -Path to access a network share in this Job?

Best Answer

I cannot explain why this is the answer but it is:

$sel = SELECT-STRING "Nothing to do" -path "D:\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Changed to:

New-PSDrive -Name "P" -PSProvider FileSystem -Root "\\<network share\Results"
$sel = SELECT-STRING "Nothing to do" -path "P:\Ad-hoc\Results\20120117_02\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Runs successful! There were NO permission changes but I guess powershell needs a [temporary with PS-Drive] local path to run. Again ZERO permission changes and if I were to replace the "P:\" with the "\\network share\Results" it would fail.

This is what I needed. The network share name will not be changing but a drive letter on the server can change so this is maintainable for me and reusable in my other Jobs.

Related Question