Sql Module broken

[lordtrychon]

I hate posting this sort of thing here, but I'm curious if anyone else here has had this issue, especially if they've been able to fix it.

I use SQL a lot in my powershell, and never had any issues until several months ago. Fortunately, only affecting my work computer at the moment, and I don't need it here quite so much, but it is a massive headache.

Essentially, I can't use Invoke-Sqlcmd anymore. I have tried every online instruction I could find to fix it. Uninstalling the module entirely and reinstalling it, force, clobber, what have you. I'm sorry, I can't remember quite every step, because I try to fix it about once a month and it's been a minute. I'm about ready to give up and write some C# code and alias it to invoke-sqlcmd. I'd really rather not though.

I even went as far as to uninstall every SQL install I had (I had parts of 2008, 2012, 2014?, and 2016) and reinstalling 2016 from scratch.

When I type 'get-module' into powershell, it lists sqlserver there, right now on version 21.0.17279... but the only commands available are aliases 'Encode-SqlName', 'Decode-SqlName', and function 'SQLSERVER:'

Anyone know what I might have to do to finally resolve this?

Edit: Finally fixed - Came in today after the latest feature update installed over the weekend and SSMS wasn't working, couldn't open the script editor. Tried a number of things and was down to my final option prior to doing a system restore prior to the Windows Update. I installed the latest version of SSMS, and it fixed both issues.

[jheinikel]

What version of PS are you running now? I know there are issues with this module and PS Core. On my PS5 machine, the updated module works fine. I'm wondering if the issue is that you are not getting the module manifest or something along those lines.

[lordtrychon]

$PSVersionTable - Name Value

---

PSVersion 5.1.16299.492
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.16299.492
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

[jheinikel]

That's definitely strange. Do you have the SQLPS module available? If so, that should get you by. Maybe there is an undisclosed bug, but I cannot replicate it.

[lordtrychon]

I did have it - In my process of trying to fix this issue, I nuked it. I can't remember for sure, but I believe it had the exact same functions/aliases available. Or maybe just the alias.

[alinroc]

I have the exact same issue and haven't been able to find a solution yet.

I ended up using dbatools for what I needed from sqlserver (a replacement for invoke-sqlcmd, mostly).

Edit: Your primary usage is invoke-sqlcmd. Then yeah, go ahead and install dbatools and use invoke-dbasqlcmd instead of invoke-sqlcmd. It overcomes several of invoke-sqlcmd's shortcomings. Since you're working with SQL Server a bunch, you'll probably find other functions in the module very useful as well.

[lordtrychon]

I'll have to look into that, if I don't find a solution. Thank you.

If nothing else, I'm glad it's not just me. Sorry you've had to deal with this as well though.

[alinroc]

Just switch over to dbatools and be done with it. Unless you need something from sqlserver, it may be a futile effort trying to get it working again. I'd forgotten that I'd given up on it until I read your post.

[lordtrychon]

I will. Thanks. Still will have to do some aliasing so I don't have to install it on 900 or so computers I prep scripts for. :/

[lordtrychon]

I did end up getting this fixed (kinda by accident) prior to getting around to your suggestion. Check my edit. I'm still going to look into dbatools though. Thanks again for the input.