Solved

Powershell SMO script not working.

Posted on 2016-11-10
18
121 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 12

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:EugeneZ
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 42

Expert Comment

by:EugeneZ
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 18

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:EugeneZ
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

A brief introduction to what I consider to be the best editor for PowerShell.
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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