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

How to change which sound is reproduced for terminal bell?

Image
3 Recently upgrade to 18.10 from 18.04, and using the terminal is becoming annoying: the new alert sound is a drip ( /usr/share/sounds/gnome/default/alerts/drip.ogg ), and the previous one is still located at ( /usr/share/sounds/ubuntu/stereo/bell.ogg ). My issue is that at the gnome-terminal, when autocompleting or pressing left BOTH sounds are reproduced, at the same time. I want to just have ubuntu/stereo/bell.ogg active, not both . At the moment I have been only able to reduce the volume of the alerts (but still both are played), or turn the alerts down (but I dislike both "solutions"). gnome-terminal 18.10 teminal-bell share | improve this question
Read more

Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

Image
up vote 5 down vote favorite 2 I would like to make it so that the vertical space below the title of each chapter is the same when using the Bjornstrup package (i.e. so that the descenders do not affect the spacing - as shown by the red arrow in the picture below). I think I'm fairly happy with the spacing around "A chapter", so I'd really like titles without descenders (e.g. "Contents") to have the same vertical spacing as "A chapter" below the title. I would also like to remove the large "0" in the contents title. Any help appreciated, as always. documentclass[11pt,a4paper]{book} % Packages[![enter image description here][1]][1] usepackage[left=3.5cm, right=3.5cm, top=4.3cm, bottom=4.25cm]{geometry} usepackage[utf8]{inputenc} usepackage{libertine} usepackage[libertine]{newtx
Read more

Can I use Tabulator js library in my java Spring + Thymeleaf project?

Image
0 I want to add Tabulator js to my java project. I added tabulator.min.js, tabulator.min.css to resources folder. After that added to my html page these dependencies as recommending in readme.md. <link rel="stylesheet" th:href="@{/css/tabulator.min.css}"> <script type="text/javascript" th:src="@{/js/tabulator.min.js}"></script> Now my tables view all elements, that I have in the html page, but not correct. In the documentation of Tabulator is example how to create header and table, var table = new Tabulator("#example-table", { columns:[ {title:"Name", field:"name", sorter:"string", width:200, editor:true}, {title:"Age", field:"age", sorter:"
Read more