Powershell 64 bit with Jenkins -Cannot find drive. A drive with the name 'SQLSER

This topic contains 1 reply, has 2 voices, and was last updated by  Richard Siddaway 1 year, 10 months ago.

  • Author
  • #32045

    Pritesh Ostwal

    To have Jenkins support Powershell 64 bit workflow. I added the path of JRE 64 bit to Environment variables and then changed Jenkins.xml to point to JRE64 Java.exe path.
    After the changes, I am running below script.

    Import-Module SQLPS.PSD1 -Verbose

    "Before Workflow"
    workflow SQLScript{

    $Inst = "ServerName"
    inlinescript {"Invoke list of Databases"}
    $databases = invoke-sqlcmd -ServerInstance ${Inst} -Database "msdb" -Query "SELECT name FROM sys.databases where name like 'abcde'"

    #cd A
    #cd B
    #cd C
    #cd D
    #cd Scripts

    inlinescript {"Loop through the database list"}
    foreach -Parallel ($database in $databases)
    # This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
    $DBname = $database.name #databasename from the select query
    $startDTM = (Get-Date)
    inlinescript {"Database Name is: $using:DBName"}

    #Write-Host "Delete All Junk Tables & Views"
    invoke-sqlcmd -ServerInstance ${Inst} -Database ${DBname} -Username "bdapi" -Password "1ap1BD5" -InputFIle "C:\Program Files (x86)\Jenkins\jobs\XXXXXXX\ScriptXXX.sql" -querytimeout ([int]::MaxValue) -Verbose
    $endDTM = (Get-Date)
    inlinescript {"Elapsed Time: $(($using:endDTM-$using:startDTM).totalseconds) seconds"}

    } #end foreach loop


    This script fails with error :
    Cannot find drive. A drive with the name 'SQLSERVER' does not exist.
    + CategoryInfo : ObjectNotFound: (SQLSERVER:String) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : DriveNotFound
    + PSComputerName : [localhost]

    Does anyone know, what I may be missing?

    Appreciate any help asap.

    Another interesting thing is if I run cd c: before Invoke-sqlcmd, it works. However I do not want to use fully qualified file path. i actually want to run Invoke-sqlcmd seamless so I could navigate to the scripts path and run them from relative path.

  • #32079

    Richard Siddaway

    When you run your workflow it executes in a new process so the SQLPS module hasn't been imported so it can't find Invoke-SQLcmd

    You can't use import-module directly in a workflow so need to use inlinescript

    InlineScript {Import-Module }

You must be logged in to reply to this topic.