Using variables in sqlps - SQL PowerShell











up vote
1
down vote

favorite












Is there a way to replace parts of these commands with local variables? When I've tried to do so, it goes out of scope, so instead of running Get-ChildItem on the SQL Server's directory, it does so on the local folder where the command is run from, e.g C:temp



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_1DEFAULTAvailabilityGroupsMyAvailabilityGroup_1AvailabilityDatabases }


I want to replace DATABASESERVER_1 with $MyServerNameVariable .



Also, in this part



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_2"MYINSTANCENAME,25001"MyAvailabilityGroup_2AgPxrCompanyStageAvailabilityDatabases }


Is there another way to write the instance name of the SQL Server, when you actually want the port number in the string? I know that you normally when configuring a connectionstring, either use only an instance name (SERVER_1INSTANCE_NAME), OR a FQDN (SERVER_1.DOMAIN.COM,25001) with port number, but in this case it is necessary, since we're using several Availability Groups on the same physical server, and need to separate them.






sql-server powershell







share|improve this question
























  • Presuming $MyServerNameVariable is a local variale you might try to replace DATABASESERVER_1 with $using:MyServerNameVariable or $($using:MyServerNameVariable)
    – LotPings
    18 hours ago






  • 1




    @Lotpings tried that and got this error: sqlps : A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer.
    – GordonLiddy
    4 hours ago















up vote
1
down vote

favorite












Is there a way to replace parts of these commands with local variables? When I've tried to do so, it goes out of scope, so instead of running Get-ChildItem on the SQL Server's directory, it does so on the local folder where the command is run from, e.g C:temp



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_1DEFAULTAvailabilityGroupsMyAvailabilityGroup_1AvailabilityDatabases }


I want to replace DATABASESERVER_1 with $MyServerNameVariable .



Also, in this part



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_2"MYINSTANCENAME,25001"MyAvailabilityGroup_2AgPxrCompanyStageAvailabilityDatabases }


Is there another way to write the instance name of the SQL Server, when you actually want the port number in the string? I know that you normally when configuring a connectionstring, either use only an instance name (SERVER_1INSTANCE_NAME), OR a FQDN (SERVER_1.DOMAIN.COM,25001) with port number, but in this case it is necessary, since we're using several Availability Groups on the same physical server, and need to separate them.






sql-server powershell







share|improve this question
























  • Presuming $MyServerNameVariable is a local variale you might try to replace DATABASESERVER_1 with $using:MyServerNameVariable or $($using:MyServerNameVariable)
    – LotPings
    18 hours ago






  • 1




    @Lotpings tried that and got this error: sqlps : A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer.
    – GordonLiddy
    4 hours ago













up vote
1
down vote

favorite









up vote
1
down vote

favorite











Is there a way to replace parts of these commands with local variables? When I've tried to do so, it goes out of scope, so instead of running Get-ChildItem on the SQL Server's directory, it does so on the local folder where the command is run from, e.g C:temp



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_1DEFAULTAvailabilityGroupsMyAvailabilityGroup_1AvailabilityDatabases }


I want to replace DATABASESERVER_1 with $MyServerNameVariable .



Also, in this part



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_2"MYINSTANCENAME,25001"MyAvailabilityGroup_2AgPxrCompanyStageAvailabilityDatabases }


Is there another way to write the instance name of the SQL Server, when you actually want the port number in the string? I know that you normally when configuring a connectionstring, either use only an instance name (SERVER_1INSTANCE_NAME), OR a FQDN (SERVER_1.DOMAIN.COM,25001) with port number, but in this case it is necessary, since we're using several Availability Groups on the same physical server, and need to separate them.






sql-server powershell







share|improve this question















Is there a way to replace parts of these commands with local variables? When I've tried to do so, it goes out of scope, so instead of running Get-ChildItem on the SQL Server's directory, it does so on the local folder where the command is run from, e.g C:temp



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_1DEFAULTAvailabilityGroupsMyAvailabilityGroup_1AvailabilityDatabases }


I want to replace DATABASESERVER_1 with $MyServerNameVariable .



Also, in this part



sqlps -NoLogo -Command { Get-ChildItem -Path SQLSERVER:SQLDATABASESERVER_2"MYINSTANCENAME,25001"MyAvailabilityGroup_2AgPxrCompanyStageAvailabilityDatabases }


Is there another way to write the instance name of the SQL Server, when you actually want the port number in the string? I know that you normally when configuring a connectionstring, either use only an instance name (SERVER_1INSTANCE_NAME), OR a FQDN (SERVER_1.DOMAIN.COM,25001) with port number, but in this case it is necessary, since we're using several Availability Groups on the same physical server, and need to separate them.






sql-server powershell




sql-server powershell






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 4 hours ago

























asked 19 hours ago









GordonLiddy

164




164












  • Presuming $MyServerNameVariable is a local variale you might try to replace DATABASESERVER_1 with $using:MyServerNameVariable or $($using:MyServerNameVariable)
    – LotPings
    18 hours ago






  • 1




    @Lotpings tried that and got this error: sqlps : A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer.
    – GordonLiddy
    4 hours ago


















  • Presuming $MyServerNameVariable is a local variale you might try to replace DATABASESERVER_1 with $using:MyServerNameVariable or $($using:MyServerNameVariable)
    – LotPings
    18 hours ago






  • 1




    @Lotpings tried that and got this error: sqlps : A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer.
    – GordonLiddy
    4 hours ago
















Presuming $MyServerNameVariable is a local variale you might try to replace DATABASESERVER_1 with $using:MyServerNameVariable or $($using:MyServerNameVariable)
– LotPings
18 hours ago




Presuming $MyServerNameVariable is a local variale you might try to replace DATABASESERVER_1 with $using:MyServerNameVariable or $($using:MyServerNameVariable)
– LotPings
18 hours ago




1




1




@Lotpings tried that and got this error: sqlps : A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer.
– GordonLiddy
4 hours ago




@Lotpings tried that and got this error: sqlps : A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer.
– GordonLiddy
4 hours ago

















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














 

draft saved


draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265914%2fusing-variables-in-sqlps-sql-powershell%23new-answer', 'question_page');
}
);

Post as a guest























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes
















 

draft saved


draft discarded



















































 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265914%2fusing-variables-in-sqlps-sql-powershell%23new-answer', 'question_page');
}
);

Post as a guest






































































-

Popular posts from this blog

Biblatex bibliography style without URLs when DOI exists (in Overleaf with Zotero bibliography)

Image
2 2 I have a Zotero bibliography in Overleaf. I use biblatex . usepackage[backend=biber,citestyle=authoryear]{biblatex} addbibresource{zoteroALH.bib} Bibliography entries look like: @book{de_saint-gervais_uniformization_2016, title = {Uniformization of {Riemann} {Surfaces}}, isbn = {978-3-03719-145-3}, url = {https://www.ems-ph.org/books/book.php?proj_nr=198&srch=series%7Chem}, urldate = {2019-02-03}, author = {de Saint-Gervais, Paul Henri}, year = {2016}, doi = {10.4171/145} } In the bibliography down the paper the reference shows like this: There is the DOI, great, but also the URL which is unnecessary when the DOI is there. How can I remove the URL when the DOI is present? biblatex bibliographies overleaf zote
Read more

ComboBox Display Member on multiple fields

Image
2 I'm trying to set the .DisplayMember property of a ComboBox in C#, but I want to bind it to multiple columns in the .DataSouce . My SQL looks like this: SELECT PersNbr, PersFirstName, PersMiddleName, PersLastName FROM Pers WHERE PersNbr = :persNbr; I'm saving this query in a DataTable so each column selected has it's own column in the Datatable . I want to make the .DisplayMember a combination of PersFirstName + PersMiddleName + PersLastName so their full name appears like this: comboBox.DisplayMemeber = "PersFirstName" + "PersMiddleName" + "PersLastName" I know I can just to this on the query: SELECT PersNbr, (PersFirstName || PersMiddleName || PersLastName) PersName and then just do this: comboBox.DisplayMember = "PersName"; but I
Read more

Is it possible to collect Nectar points via Trainline?

Image
2 Since I am not eligible for any Railcard, the only available discount/bonus scheme is Nectar Card for me. GWR, SWR, LNER and Virgin Trains allow to collect Nectar points and I have already linked a card to my GWR account. A friend suggested me to use Trainline App, because it can search for bus tickets either. I had a look at the application, but could not see any option for Nectar. Is there a way to collect Nectar points when I buy tickets via Trainline? uk trains loyalty-programs trainline share | improve this question asked Dec 2 '18 at 15:50 ahmedus 3,224 5 19 49
Read more