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?

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 doesnt 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

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

Hi Joe,

Did you find a resolution to this?

Cheers,

Ben

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

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.

• Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator Tuesday, July 29, 2014 12:28 AM
• Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Friday, August 29, 2014 1:25 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.

• Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator Tuesday, July 29, 2014 12:28 AM
• Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Friday, August 29, 2014 1:25 AM

That was it.  Thank you Robin Wang!

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

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

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

This started happening after installing WMF 4.0.  Commenting out the those lines worked.  Thank you!

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.... :)

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.

• Edited by MatticusAuMicrosoft employee Friday, October 24, 2014 5:56 AM

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.

• Edited by MatticusAuMicrosoft employee Friday, October 24, 2014 5:56 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

I am still having this issue using Azure PowerShell downloaded today.  Has this been listed as a bug on Connect yet if you know?

Thank you for the suggestion, but the services were not stopped so it did not apply to me.

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

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

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