WMI connection error using Invoke-Sqlcmd

  • smw147

    Ten Centuries

    Points: 1387

    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

  • Jeff Moden

    SSC Guru

    Points: 995164

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • smw147

    Ten Centuries

    Points: 1387

    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

  • Jeff Moden

    SSC Guru

    Points: 995164

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • x

    SSC-Insane

    Points: 23485

    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.

  • Sue_H

    SSC Guru

    Points: 90287

    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

  • Jeff Moden

    SSC Guru

    Points: 995164

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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