SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell for network path


Powershell for network path

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40762 Visits: 14413
Scott Newman-212556 (9/5/2012)
You need to change the provider when the job runs. It's running under the sqlserver:/ provider. Just do a cd c: at the top of your script and it will now accept the unc path.

Scott

Hmm, that was not necessary for me. My test was successful with only a single command like so:

Copy-Item "\\server\path\file.extension" -Destination "C:\localpath\localfilename.extension"


For the record I am using 2008 R2 Build 10.50.2806.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
bwperrin
bwperrin
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 56
"cd c:" works! Thanks!!
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40762 Visits: 14413
bwperrin (9/13/2012)
"cd c:" works! Thanks!!

What build are you on?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
rkneeshaw
rkneeshaw
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 27
sgnewman (9/5/2012)
You need to change the provider when the job runs. It's running under the sqlserver:/ provider. Just do a cd c: at the top of your script and it will now accept the unc path.

Scott


Nailed it! This is the only place I've been able to find on the whole internets that has the solution. This has been driving me crazy for days! I chose to use the newer PowerShell command "Set-Location c:" instead, which does the same thing.

opc.three (9/13/2012)
bwperrin (9/13/2012)
"cd c:" works! Thanks!!

What build are you on?


I'm willing to bet it worked for the guy using SQL 2012. Our powershell scripts were all working perfectly fine running as SQL agent jobs in SQL 2008 R2. We upgraded to SQL 2012 and all of our powershell scripts that performed file copy or moves would fail with "invalid path". Scott knew exactly what is happening now in SQL 2012.

Thanks Scott!
sgnewman
sgnewman
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1166
You're very welcome.

Scott
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40762 Visits: 14413
It must have had something to do with the switch in SQL Agent 2008 to 2012 from them dropping us into a mini-shell versus dropping us into a full blown PowerShell.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sgnewman
sgnewman
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1166
More than likely. I've just gotten into the habit of just calling powershell directly in a cmd line from the job server instead of putting it into a powershell job step just because of niggly little issues like this.

Scott
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40762 Visits: 14413
That's what I do too, and recommend on 2008 R2 but I was hopeful with the change in the approach to PowerShell steps introduced in 2012 that the user experience would have improved to the point where I would not need to do that anymore. Using powershell.exe some of my command lines have become pretty gnarly with all the parameters I pass to my scripts. However, since I do not run 2012 as a primary platform in production just yet my knowledge is limited to some specialized instances I have rolled out plus some local testing. I have not rolled my generic maintenance routines to a 2012 environment just yet to see how they would react if I converted them to use PowerShell steps.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40762 Visits: 14413
ryan 8226 (7/28/2013)
I chose to use the newer PowerShell command "Set-Location c:" instead, which does the same thing.

By the way, cd is not a different, older command than Set-Location, it's an alias, so they're one in the same:



There are a bunch of built-in aliases in your default PowerShell session, cd being just one of many. Type alias at a PowerShell prompt to see them all:



You can also define new aliases of you own as well using New-Alias in case you are interested. Check the help for complete details:



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
3.jpg (372 views, 17.00 KB)
4.jpg (377 views, 80.00 KB)
5.jpg (376 views, 107.00 KB)
Henry_Lee
Henry_Lee
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 1661
This was a huge help. It solved my problem.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search