Ask the Experts

PowerShell for SQL Server Use of sql server tokens

Use of sql server tokens

Roust_m over 5 years ago

Hi,

I am creating a new job which has powershell job steps.

I know that in TSQL you can make use of tokens:

PRINT N'Current instance name is $(ESCAPE_SQUOTE(INST))' ;

PRINT N'Current server name is $(ESCAPE_SQUOTE(MACH))' ;

Is there a way to embed it into Powershell without running TSQL queries?

I need to be able to do something like this:

$InstanceName = $(ESCAPE_SQUOTE(INST))' ;

$ServerName = $(ESCAPE_SQUOTE(MACH))' ;

Thanks.

Chrissy LeMaire over 4 years ago

Yes, you can use SMO.

# Load assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

# Connect to SQL Server instance "sql01\sqlexpress"using Windows Auth
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sql01\sqlexpress"
$server.ConnectionContext.Connect()

If you just type $server, you will see a list of all the properties that you can use. Note that while this specific operation takes a few seconds, it's only because it's enumerating so much info, not because SMO is slow (SMO is awesome and fast).

Some properties you may find useful:

$server.Name
$server.InstanceName
$server.NetName or $server.ComputerNamePhysicalNetBIOS

To use SQL Authentication with SMO, you would use the following:

$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sql01\sqlexpress"
$server.ConnectionContext.LoginSecure = $false
$sqllogin = Get-Credential
$server.ConnectionContext.set_Login($sqllogin.username)
$server.ConnectionContext.set_SecurePassword($sqllogin.Password)
$server.ConnectionContext.Connect()

Please let me know if this does not answer your question. I know this is a little late, but it will be a good reference for others, even if you've already solved your problem.
Cancel
Up 0 Down

Reply

Cancel