How can I avoid including full path for SQL script files when using sqlcmd inside powershell?

• Marios Philippopoulos

  SSC Guru

  Points: 57030

  January 8, 2009 at 12:56 pm

  #383707

  I have the following lines in my powershell script. The sql-script file is in the same directory as the actual powershell script. Is there a way to avoid having to constantly include the full path to the sql-script file? I find it very annoying...

  I haven't been able to find a satisfactory solution to this on the web.

  $instance = "myServer\myInstance"

  sqlcmd `-S $instance `-i C:\Scripts\POWERSHELL\...\...\checkDB.sql

  __________________________________________________________________________________
  SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
  Persisting SQL Server Index-Usage Statistics with MERGE[/url]
  Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

• Todd Engen

  SSCrazy Eights

  Points: 9869

  January 8, 2009 at 7:29 pm

  #923856

  Try adding the location of your scripts to the path environment variable.

  PS C:\>$env:path += ";C:\MyScripts"

• Grant Fritchey

  SSC Guru

  Points: 395586

  January 9, 2009 at 6:03 am

  #924071

  I've had to do that, or run the script from the directory where I want to store/read all the files. I'm not that aware of any other choices either.

  Adding the path could work in some circumstances.

  ----------------------------------------------------
  The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
  Theodore Roosevelt

  The Scary DBA
  Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
  Product Evangelist for Red Gate Software

• Marios Philippopoulos

  SSC Guru

  Points: 57030

  January 9, 2009 at 7:52 am

  #924181

  Thanks guys for your suggestions.

  In the past, I remember running a sql script file on the same folder as the powershell script itself, and getting an error if I did not specify the full path of the sql script.

  I tried it now and it is working without having to include the full path...

  I will post here if I see this happen again.

  In any case, thanks again!

  $instance = "srvr\instName"

  sqlcmd `-S $instance `-i someScript.sql

  write-output 'done'

  __________________________________________________________________________________
  SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
  Persisting SQL Server Index-Usage Statistics with MERGE[/url]
  Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]