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 asked 19 hours ago
GordonLiddy
164
add a comment |
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 asked 19 hours ago
GordonLiddy
164
Presuming$MyServerNameVariableis a local variale you might try to replaceDATABASESERVER_1with$using:MyServerNameVariableor$($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
add a comment |
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 asked 19 hours ago
GordonLiddy
164
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 asked 19 hours ago
GordonLiddy
164
asked 19 hours ago
GordonLiddy
164
edited 4 hours ago
asked 19 hours ago
GordonLiddy
164
asked 19 hours ago
GordonLiddy
164
asked 19 hours ago
GordonLiddy
164
164
Presuming$MyServerNameVariableis a local variale you might try to replaceDATABASESERVER_1with$using:MyServerNameVariableor$($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
add a comment |
Presuming$MyServerNameVariableis a local variale you might try to replaceDATABASESERVER_1with$using:MyServerNameVariableor$($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
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Presuming
$MyServerNameVariableis a local variale you might try to replaceDATABASESERVER_1with$using:MyServerNameVariableor$($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