Solved

Powershell SMO script not working.

Posted on 2016-11-10
18
115 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 68

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

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 68

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 68

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
This article will help you understand what HashTables are and how to use them in PowerShell.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now