Post reply

WMI connection error using Invoke-Sqlcmd

  • December 19, 2018 at 3:12 am

    #376663

    Hi all,

    I'm brand new to powershell and am trying to convert an old batch script that run some sql files via sqlcmd command to a powershell script.

    This is my test script so far:

    $server = Read-Host 'Server name: '
    $db = Read-Host 'Database name: '
    $user = Read-Host 'Username: '
    $pass = Read-Host 'Password: '
    $outputpath = (pwd).path
    $File01 = $outputpath + '\Code\Scripts\Summarise_Data.sql'
    # Output File
    $Summary = $outputpath + '\Summary.csv'

    if (Test-Path $Summary)
    {
    Remove-Item $Summary
    }
    Invoke-Sqlcmd -ServerInstance $server -User $user -Password $pass -Database $db -InputFile $File01 | Export-Csv -Delimiter ',' -NoTypeInformation $Summary

    The code works, and creates the output CSV file with the correct data in it.  However, when the Invoke command is run, the following error is returned to the console 5 times.

    WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' failed with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

    The error appears to be a red herring  because as I said above, the actual code and output works fine.

    As I am new to powershell I'm not sure how I can suppress this error, or what I need to do to prevent it happening.  I've tried searching for a solution but nothing I have seen has given a solution.  The one solution I did find involved changing a config file, but permissions on the file wouldn't allow me to do this, even though I am a local admin on my machine.

    Has anybody else experienced this and been able to resolve?

    I'm running Windows 10.  I experience this on all instances of SQL I have tried, regardless of SQL version.

    Regards

    Steve

  • December 21, 2018 at 6:34 pm

    #2017147

    smw147 - Wednesday, December 19, 2018 3:12 AM

    ... am trying to convert an old batch script that run some sql files via sqlcmd command to a powershell script.

    WHY?  :blink:  Was the old script not working?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2018 at 2:31 am

    #2017155

    I'm trying to learn a new skill. I've never used powershell before. I thought using existing cmd batch routines Ive written as a good project to start with.

    Regards

    Steve

  • December 22, 2018 at 10:49 am

    #2017162

    Heh... ok.  Understood.  But, if your stuff is working, you should be teaching PowerShell users a new skill. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 28, 2018 at 8:50 am

    #2017518

    Microsoft sure likes powershell so its probably not a bad idea to get familiar with it. I'm not crazy about the language tho, but its becoming a bigger part of the environment and I'm sort of biting the bullet too.

    You might want to take the error message literally and check why your wmi isn't answering.

    https://stackoverflow.com/questions/11330874/get-wmiobject-the-rpc-server-is-unavailable-exception-from-hresult-0x800706

    I know you're probably not calling get-wmiobject yourself, but powershell seems like it has lots of programming going on behind the scenes including libraries and plugins (excuse the illiterate guesses of mine about what actually goes on with powershell LOL) and something else certainly seems like its making use of wmi so that's what I'd try to troubleshoot.

  • December 28, 2018 at 9:46 am

    #2017534

    Could also be related to the issue in this article - worth taking a look if you're using SQLPS:
    PowerShell & SQL :: A Quick Fix for the SQLPS Problems

    Sue

  • December 31, 2018 at 7:55 am

    #2017648

    Sue_H - Friday, December 28, 2018 9:46 AM

    Could also be related to the issue in this article - worth taking a look if you're using SQLPS:
    PowerShell & SQL :: A Quick Fix for the SQLPS Problems

    Sue

    Heh... I figured out a way to avoid all issues with PowerShell... I've stopped using it. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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