Use of sql server tokens

Roust_m
over 4 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.

 

 

 

  • 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.