[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Powershell SMO script not working.

Heyas,

I am using the following PowerShell script recommended (see attached) to me via this MSDN article:

https://msdn.microsoft.com/en-us/library/ee210565.aspx
# Get Ucp connection  
$UcpServerInstanceName = "UCH-SQLC01-D001\MSSQLSERVER";  
$UtilityInstance = new-object -Type Microsoft.SqlServer.Management.Smo.Server $UcpServerInstanceName;  
$UcpConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $UtilityInstance.ConnectionContext.SqlConnectionObject;  
$Utility = [Microsoft.SqlServer.Management.Utility.Utility]::Connect($UcpConnection);  
  
# Now remove the ManagedInstance from the SQL Server Utility  
$ServerInstanceName = "UCH-SQLC01-D001\MSSQLSERVER";  
$Instance = new-object -Type Microsoft.SqlServer.Management.Smo.Server $ServerInstanceName;  
$InstanceConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $Instance.ConnectionContext.SqlConnectionObject;  
$ManagedInstance = $Utility.ManagedInstances[$ServerInstanceName];  
$ManagedInstance.Remove($InstanceConnection);  

Open in new window

Then loading the necessary SMO https://msdn.microsoft.com/en-us/library/hh231286.aspx

However, when I run the script I get the following error, see attached.
Power Shell ErrorI am using the SQL command to get the Server/Instance name.

@@servername + '\' + @@servicename

I don't understand how this is occurring as I am running the script locally.

Any assistance is appreciated.

Thank you.
0
Zack
Asked:
Zack
  • 6
  • 4
  • 3
  • +2
2 Solutions
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It looks like you have a default instance, hence the instance name in the script should be set to "UCH-SQLC01-D001", not "UCH-SQLC01-D001\MSSQLSERVER". Can you update the instance name in the script and try again?
0
 
Eugene ZCommented:
it is the same sql name when you run
select @@SERVERNAME AS instance_name. Right?
What is your sql server version? Sql 2016?
if yes, check  this https://msdn.microsoft.com/en-us/library/ee210568.aspx

Can you connect it from SSMS (remote)?
on UCH-SQLC01-D001 server check if Sql server Browser service is running,,
0
 
QlemoDeveloperCommented:
The error message doesn't fit to the script, The error messages wants to connect a local unnamed instance, while the script is about a remote or local named instance. What is your $UcpServerInstanceName, and as asked above, your local instance name (if any)?
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
Eugene ZCommented:
also run

# To check whether the Sqlps module is installed.
Get-Module -ListAvailable -Name Sqlps

https://msdn.microsoft.com/en-us/library/hh231286.aspx
0
 
QlemoDeveloperCommented:
Eugene, the SMO objects are already used, and there is no mentioning of SQL PS here (yet).
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
in your code:
$UtilityInstance = new-object -Type ype Microsoft.SqlServer.Management.Smo.Server $UcpServerInstanceName;  

Open in new window


what is ype?  is that error in typing?
0
 
Eugene ZCommented:
Qlemo: you did good- >files "code" "error"  were attached and visible from the beginning
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Heyas,

Tried setting the script to 'UCH-SQLC01-D001' attached is the error I received.
picAny other suggestions?

Thank you.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
# Get Ucp connection  
$UcpServerInstanceName = "UCH-SQLC01-D001";  
$UtilityInstance = new-object –Type Microsoft.SqlServer.Management.Smo.Server $UcpServerInstanceName;  
$UcpConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $UtilityInstance.ConnectionContext.SqlConnectionObject;  
$Utility = [Microsoft.SqlServer.Management.Utility.Utility]::Connect($UcpConnection);  
  
# Now remove the ManagedInstance from the SQL Server Utility  
$ServerInstanceName = "UCH-SQLC01-D001";  
$Instance = new-object -Type Microsoft.SqlServer.Management.Smo.Server $ServerInstanceName;  
$InstanceConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $Instance.ConnectionContext.SqlConnectionObject;  
$ManagedInstance = $Utility.ManagedInstances[$ServerInstanceName];  
$ManagedInstance.Remove($InstanceConnection);  

Open in new window

0
 
QlemoDeveloperCommented:
I've reviewed your question again, and see a conflict. Do you really want to connect to MSSQL to get your instance name? If so, above code is the wrong approach, as it requires you to know the instance name already.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Qlemo,

I will embed code in future thank you. As for my query, I am trying to 'Remove an Instance of SQL Server from the SQL Server Utility' and using the script provided to me in this article: https://msdn.microsoft.com/en-us/library/ee210565.aspx.

Since the method described in the article is the wrong approach what would be the right approach?

Thank you.
0
 
QlemoDeveloperCommented:
Do you really mean the UCP? It is  a centrally managed SQL Server collecting and monitoring data from different other MSSQL instances, something you only use in "bigger" environments.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Qlemo,

My manager told me to try to get Utility Explorer working for this server instance, so I am trying to get the UCP working.

Part of long standing query on EE:

https://www.experts-exchange.com/questions/28977654/SQL-Server-2012-HA-Cluster-Utility-Controller-No-Data.html

Thank you.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Heyas,

I resolved my underlying question:  https://www.experts-exchange.com/questions/28977654/SQL-Server-2012-HA-Cluster-Utility-Controller-No-Data.html

Hence this question is no longer required.

Awarding points accordingly.

Thank you.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Thank you for your assistance.
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

  • 6
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now