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

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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

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

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

  • Grant Fritchey

    SSC Guru

    Points: 395586

    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

    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]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply