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.
Course Of The Month
1 day, 19 hours 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 6 days ago
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 6 days ago
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 6 days ago
Qlemo: you did good- >files "code" "error" were attached and visible from the beginning
Active today
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 today
# 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 today
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 today
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 today
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
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses
Course of the Month1 day, 19 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.