0

PowerShell has a cool module for interacting with SQL Server. One of the features is the ability to navigate database objects like file system objects. For example:

get-childitem -Path "SQLSERVER:\SQL\MSI\DEFAULT\DATABASES"

However, instead of a list of objects, I get the following error message:

get-childitem : Cannot find drive. A drive with the name 'SQLSERVER' does not exist.
At line:1 char:1
+ get-childitem -Path "SQLSERVER:\SQL\"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:String) [Get-ChildItem], DriveNotFoundException
    + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

After spending a few hours and acquiring a few gray hairs, I accidentally discovered that it is possible to get it to work if at least once to make the following a call:

invoke-sqlcmd

Then get-childitem sees the SQLSERVER disk and returns me a list of tables. Beauty :-)


Obviously, calling invoke-sqlcmd changes something in the environment that allows get-childitem to interact with SQLSERVER. However, neither in the documentation nor in the examples on the Internet, I did not find anything about this, which makes me think that the problem is in my system. What did I miss? Did they just forget to mention it in the documentation? Or is it not a bug but a feature, and invoke-sqlcmd performs authorization?

I used windows authorization in tests

OS: Windows 10 Enterprise and Windows Server 2019

SQL Server version: 15.0.2000.5 developer edition

2
  • 2
    Sounds like a missing module which needs to be loaded first Commented Mar 24, 2023 at 16:15
  • 1
    I have the same problem on Windows 10 & SQL Server 2019 (although I've no reason to believe it's limited to those versions). I have yet to find a solution, though invoke-sqlcmd solves it in the short-term. I think Charlieface is right about the missing module but I didn't see any errors when I ran through my setup. Commented Mar 27, 2023 at 16:18

1 Answer 1

2

I think what you're seeing is the module auto-loading which exists in PowerShell 3+. The SQLSERVER module is not loaded into your session (so the PSDrive doesn't exist) until you execute one or more functions from that module.

You can explicitly load the module with import-module sqlserver in your session/script prior to using the SQLServer drive and it will be available from that point forward.

2
  • Yes, it looks like the reason is related to module auto-loading. Thank you! If execute import-module sqlPS then everything works (during tests, I found that it turns out I use the sqlPS module instead of SqlServer)
    – Ivan
    Commented Apr 9, 2023 at 15:04
  • 1
    @Ivan please don't use the sqlps module. It was obsoleted in 2016 or 2017 when the sqlserver module was released.
    – alroc
    Commented Apr 9, 2023 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.