What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
COURSE of the MONTH
13 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Powershell SMO script not working.
Posted on 2016-11-10
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
However, when I run the script I get the following error, see attached.
I 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.
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);
However, when I run the script I get the following error, see attached.
I 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.
[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
Active 1 day ago
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\MSSQLSERV
Active today
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,,
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,,
Active today
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)?
Active today
also run
# To check whether the Sqlps module is installed.
Get-Module -ListAvailable -Name Sqlps
https://msdn.microsoft.com/en-us/library/hh231286.aspx
# To check whether the Sqlps module is installed.
Get-Module -ListAvailable -Name Sqlps
https://msdn.microsoft.com/en-us/library/hh231286.aspx
Active today
Eugene, the SMO objects are already used, and there is no mentioning of SQL PS here (yet).
Active today
in your code:
what is ype? is that error in typing?
$UtilityInstance = new-object -Type ype Microsoft.SqlServer.Management.Smo.Server $UcpServerInstanceName;
what is ype? is that error in typing?
Active today
Qlemo: you did good- >files "code" "error" were attached and visible from the beginning
Active 1 day ago
Heyas,
Tried setting the script to 'UCH-SQLC01-D001' attached is the error I received.
Any other suggestions?
Thank you.
Tried setting the script to 'UCH-SQLC01-D001' attached is the error I received.
Any other suggestions?Thank you.
Active 1 day ago
# 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);
Active today
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.
Active 1 day ago
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.
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.
Active today
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.
Active 1 day ago
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.
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.
Active 1 day ago
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.
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.
Featured Post
Managing Active Directory does not always have to be complicated. If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Course of the Month13 days, 1 hour left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.