[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Powershell SMO script not working.

Posted on 2016-11-10
18
Medium Priority
?
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
18 Comments
 
LVL 14

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 1000 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 43

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 71

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
Looking for the Wi-Fi vendor that's right for you?

We know how difficult it can be to evaluate Wi-Fi vendors, so we created this helpful Wi-Fi Buyer's Guide to help you find the Wi-Fi vendor that's right for your business! Download the guide and get started on our checklist today!

 
LVL 43

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 71

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 43

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 71

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 71

Accepted Solution

by:
Qlemo earned 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

650 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