unable access sql provider in powershell without running an invoke-sqlcmd first

  • Steve T

    SSCommitted

    Points: 1987

    I encountered a weird issue and hope that somebody may have a fix for this.

    Microsoft SQL Server 2014 (SP2-GDR) (KB4505217) - 12.0.5223.6 (X64)  (yeah I know...it's a dev server)

    Windows 2012 R2

    PSVersion 4.0

    I load the SQL assemblies in the PS script

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

    The following will fail if I run this immediately after loading the assemblies

    Get-ChildItem SQLSERVER:\SQL\$env:COMPUTERNAME

    Get-ChildItem : Cannot find drive. A drive with the name 'SQLSERVER' does not exist.

    If I run the following first, I'm fine.

    Invoke-Sqlcmd -Query "SELECT TOP 1 * FROM sys.sysobjects" | Out-Null

    Get-ChildItem SQLSERVER:\SQL\$env:COMPUTERNAME

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Steve T

    SSCommitted

    Points: 1987

    I just wanted to amend that I just need to run

    Invoke-Sqlcmd  | Out-Null

    This seems to then fully load the assemblies into memory and everything is OK after.

     

     

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

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