2

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 = '[email protected]',
    @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 = '[email protected]',
    @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 = '[email protected]',
    @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 = '[email protected]',
    @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?

Improve this question
11
  • 1
    D:\ is going to be a mapped drive for a specific user. You need to make sure that the account that is trying to access \\network share\ actually has access. You can figure out who it is by running whoami from that job.
    – Aaron Bertrand
    Feb 3, 2017 at 18:38
  • Make sure your service account is an admin on the box.
    – SQL_Deadwood
    Feb 3, 2017 at 18:58
  • 1
    @Aaron, he said that the D:\ worked, the network path did not. Vic, Try putting a C:\ on it's own line in the script before accessing drive/network resources. I've found that the PowerShell built into SQL Server mis-interprets the file system access unless I put that first.
    – Jonathan Fite
    Feb 3, 2017 at 19:02
  • @AaronBertrand I did not think of that, that is something I may need that in the future. Sadly, the account displayed from "whoami" is in a group that has access to that share.
    – Vic Work
    Feb 3, 2017 at 20:12
  • 1
    @JonathanFite he said that D:\ worked on one machine but it was clearly a mapped drive so would not be eligible on other machines or even for other users on this machine.
    – Aaron Bertrand
    Feb 3, 2017 at 21:19

1 Answer 1

Reset to default
3

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.

Improve this answer

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.