none
Import-Module 'sqlps' -DisableNameChecking is returning a bunch of warnings I do not want to see in output. How do I supress them?

    Question

  • Hi All,

    I find that with SQL 2012 and SQL 2014 I get the following types of WARNING: messages displayed when I import the sqlps module.

    WARNING: The local computer does not have an active instance of Analysis Services.
    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)

    I've tried adding -WarningAction SilentlyContinue and I've tried piping the output to null by adding | Out-Null but I still get this garbage that I don't care about on the servers I'm running these scripts on.  Do any of you know how to suppress these warnings?

    Joe


    Joe Moyle

    Thursday, June 26, 2014 3:46 PM

Answers

  • These warnings have been bugging me as well, and this seems to work (assuming you don't plan to ever use Azure SQL cmdlets, which is the case for me). Open the following file:

    C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\ServiceManagement\Azure\Azure.psd1

    Comment out the following lines:

    '.\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml'
    '.\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.dll',

    Save the file and try importing SQLPS again. Don't forget to remove the extra comma before the commented line in TypesToProcess.

    Monday, July 28, 2014 5:50 AM

All replies

  • Hi

    According to your description, there has been a 0x800706BA error message, you can try to use the -EA SilentlyContinue parameter combining with -WarningAction SilentlyContinue to suppress the warning. Or you can try to create a script with the built-in $host object in your profile, to change the warning text color and customize your console output. For more details, please review the similar thread: suppressing the 'yellow' warning output.

    In addition, actually I have done a test using the DisableNameChecking parameter when importing the sqlps module, everything is ok and it doesn’t return the warnings as your post. The message ‘The RPC server is unavailable ‘always means that the Remote Procedure Call service on the remote machine couldn't be contacted, thus make sure that your remote machine is online, and then try to import the sqlps module.


    Thanks
    Lydia Zhang

    Monday, June 30, 2014 4:45 AM
    Moderator
  • Hi Lydia and Other Gurus,

    Thank you for taking the time to reply.  I had tried it with -WarningAction SilentlyContinue prior to this post and that does remove the first warning about not having an active instance of Analysis Services.  I tried adding the -ErrorAction SilentlyContinue even though that is not the behavior I want and it also did not help.  I don't really care that it is yellow as I don't normally use PowerShell interactively.  I don't want the warnings at all so that they don't show up in the SQL Agent job history or in my log files of whatever I'm scripting that must interact with MS SQL.

    I am still getting the 10 warnings about not being able to connect to Azure.  I am not attempting to connect to Azure and I'm not sure why I am getting these warnings.  I'm simply attempting to Import-Module 'sqlps' -DisableNameChecking.  I don't understand why I would be getting RPC failures on a service I'm not even attempting to connect to.  I'm not even attempting to connect to my local server at this point.  Simply load the libraries.

    Joe


    Joe Moyle

    Monday, June 30, 2014 9:49 PM
  • Hi Joe,

    Did you find a resolution to this?

    Cheers,

    Ben


    Ben Weeks
    SharePoint Consultant @ Webtechy Ltd
    T: +441223922070 F: +448701315220
    Web: http://www.webtechy.co.uk Blog: http://blog.webtechy.co.uk

    Thursday, July 10, 2014 9:05 AM
  • Hi Ben,

    I have not found a solution yet but I haven't spent any time.  It is only on one test server.  The one where I have loaded Azure PowerShell tools associated with Visual Studio 2013.  I suspect that some newer (broken in my opinion) version of the SQL PS cmdlets has taken precedence over the ones that get installed with SQL 2014 by default.

    I'm a working DBA and just haven't had time to mess with this since it is just a test server.  When I posted this I was hoping I wasn't the only person experimenting with Azure who uses PS to manage database servers and that some brilliant soul would inform me of what I've overlooked.

    I will update this if I find a solution since I'm sure others will encounter this annoyance too.

    Joe


    Joe Moyle

    Friday, July 11, 2014 12:58 AM
  • These warnings have been bugging me as well, and this seems to work (assuming you don't plan to ever use Azure SQL cmdlets, which is the case for me). Open the following file:

    C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\ServiceManagement\Azure\Azure.psd1

    Comment out the following lines:

    '.\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml'
    '.\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.dll',

    Save the file and try importing SQLPS again. Don't forget to remove the extra comma before the commented line in TypesToProcess.

    Monday, July 28, 2014 5:50 AM
  • That was it.  Thank you Robin Wang!

    Joe Moyle

    Monday, July 28, 2014 3:26 PM
  • Try this:
    import-module sqlps -DisableNameChecking 3>$Null
    • Proposed as answer by Hieyeque Thursday, August 28, 2014 4:49 PM
    • Edited by Hieyeque Thursday, August 28, 2014 8:13 PM
    Thursday, August 28, 2014 4:49 PM
  • Hi Hieyeque,

    Thanks for answering but Robin Wang's answer that appears to have originally come from Lydia Zhang hit the nail on the head.

    Joe


    Joe Moyle

    Thursday, August 28, 2014 5:49 PM
  • This started happening after installing WMF 4.0.  Commenting out the those lines worked.  Thank you!
    Friday, September 5, 2014 3:14 PM
  • Indeed, however, I proposed my answer for 2 reasons.

    1.  The other answer requires the modification of the module itself - which doesn't scale well.  My approach allows you to import the module in cases where you may have hundreds of servers on which to perform the operation and still achieve the same result.

    2.  To demonstrate the method of redirection of warning to null and just get it out on the web.  

    Since you were originally trying to "handle" the output, I demonstrated how you can achieve that - the other method by both Robin and Lydia prevent the output from occurring, which is great, but doesn't allow you to handle other situations where you encounter warning output.

    Anyway, just sharing and caring.... :)

    Friday, September 5, 2014 5:28 PM
  • Hi

    These errors have nothing to do with the loading of the SQLPS CmdLets/Functions/etc...... They are because of a general failure when you try and locate the Azure modules and therefore this only occurs when you install the Azure PowerShell modules.... let me explain

    But before I do, a warning, DO NOT MODIFY THE FILES I MENTION!!!

    When you run: Import-Module SQLPS

    PowerShell loads up the Module Mainfest C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.PSD1

    This references the script file SqlPsPostScript.PS1 in the same path..... here is where the problem lies.

    This script has a step which is designed to import the SQLASCmdlets module and it is this step which is throwing the warnings. However, again it has nothing to do with the SQL module. The problem actually even happens if you just run: Get-Module -ListAvailable

    Now this is where we get stuck as the Azure SQL module is a Binary Module (aka it loads .dlls). Best option is to live with the warnings, because if you suppress them and another module starts to give you warnings then you won't know something is going wrong.

    And finally I just checked and there is a new Azure PowerShell SDK released on the 2nd October so it would be worth downloading that as this might be fixed.

    P.S. I am going to blog about this and will update with a link when I do.


    Friday, October 24, 2014 5:55 AM
  • While the error indicates that the RPC service is unavailable, Control Panel...|Services show that RPC is supposed to start automatically. Despite this you get the error. Now start the RPCLocator service and import again and you will not get this error.

    Details of successfully importing are available here;

    http://hodentekmsss.blogspot.com/2014/11/how-to-overcome-error-while-importing.html


    Jayaram Krishnaswamy

    Tuesday, November 25, 2014 9:00 PM
  • I am still having this issue using Azure PowerShell downloaded today.  Has this been listed as a bug on Connect yet if you know?

    Scott Saari

    Wednesday, June 10, 2015 2:19 AM
  • Thank you for the suggestion, but the services were not stopped so it did not apply to me.

    Scott Saari

    Wednesday, June 10, 2015 2:20 AM
  • Yep still an issue. I have collected the debug and traces for this, just hadn't found time to pass the feedback onto the product teams. I will do this ASAP and post the Connect link so everyone can vote.

    The issue is because in the Azure module they have a folder path of /SQL to store the types file but the SQL Provider treats anything with /SQL from the root of the provider (which we enter during an import of SQLPS) as a connection to a SQL Server which is why we try WMI connections.... I'll provide the full info in the connect

    Wednesday, June 10, 2015 2:25 AM
  • Matt,

    I am very grateful to you.  I have to uninstall Azure PowerShell until it's fixed, and I'm trying to start using Azure, so this is an impediment to the process of learning what I need to learn.

    Thanks,

    Scott


    Scott Saari

    Wednesday, June 10, 2015 2:35 AM
  • Hi All

    I have finally found some time to log the connect item.

    https://connect.microsoft.com/SQLServer/feedback/details/1420992/import-module-sqlps-may-take-longer-to-load-but-always-returns-warnings-when-the-azure-powershell-module-is-also-installed

    Time to get voting on the issue.

    Cheers
    Matt

    Thursday, June 11, 2015 3:49 AM
  • This is great because I DO plan to use my Azure module, and hacking it to disable functionality is not a 'solution'. This elegantly manages suppressing the annoying warning output nicely, which is all I really wanted to do.

    Thursday, April 21, 2016 8:51 PM
  • Just put up with it?  What a lazy suggestion.  I've got code that relies on not getting  anything back from this cmd.  I don't mind necessarily that the errors are there, but they should respect the WarningAction... what else is it there for if not to suppress these types of warnings?

    So "just live with it" isn't an acceptable answer.

    Thursday, December 8, 2016 6:05 PM
  • Yours is indeed the most scalable answer.  I think modifying that file will only lead to unintended heartache in the future, and that means I have to now touch all my boxes and alter them.  Not a good solution.  Your solution works great for me and I love how easy it is.  One thing I don't get though... what's the 3> for?  Why is the 3 there?
    Thursday, December 8, 2016 6:07 PM
  • "3" is the warning stream.  See this for an explanation.

    Scott Saari

    Thursday, December 8, 2016 11:55 PM
  • This one is so bothering. Thank you.
    Sunday, March 31, 2019 2:44 PM