Solved

Powershell SMO script not working.

Posted on 2016-11-10
18
130 Views
Last Modified: 2016-11-15
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
Comment
Question by:Zack
  • 6
  • 4
  • 3
  • +2
18 Comments
 
LVL 13

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 250 total points
ID: 41883905
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
 
LVL 42

Expert Comment

by:Eugene Z
ID: 41884174
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 41884695
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 42

Expert Comment

by:Eugene Z
ID: 41884722
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 41884733
Eugene, the SMO objects are already used, and there is no mentioning of SQL PS here (yet).
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 41884755
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
 
LVL 42

Expert Comment

by:Eugene Z
ID: 41884898
Qlemo: you did good- >files "code" "error"  were attached and visible from the beginning
0
 

Author Comment

by:Zack
ID: 41885983
Heyas,

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

Thank you.
0
 

Author Comment

by:Zack
ID: 41885985
# 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
 
LVL 69

Expert Comment

by:Qlemo
ID: 41886099
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
 

Author Comment

by:Zack
ID: 41886197
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
 
LVL 69

Accepted Solution

by:
Qlemo earned 250 total points
ID: 41886278
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
 

Author Comment

by:Zack
ID: 41887059
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
 

Author Comment

by:Zack
ID: 41888949
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
 

Author Closing Comment

by:Zack
ID: 41888950
Thank you for your assistance.
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question