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

• Steve T

  SSCommitted

  Points: 1987

  October 23, 2019 at 1:49 am

  #3691177

  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

  October 24, 2019 at 2:10 am

  #3691504

  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

  October 24, 2019 at 6:40 pm

  #3691738

  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.