CODEWITHSUNDEEP
sql-serverpowershellstored-procedures
lit
lit

Reputation: 16266

Running a stored procedure from Invoke-Sqlcmd

Can sp_who2 be run as the query on PowerShell?

I know from

Invoke-Sqlcmd -ServerInstance 'DB001' -Query "sp_who2 'ADDOM\username';"

that '107' is a spid for this user. This execution of a stored procedure does not provide an opportunity to provide a column name alias using AS.

PS C:\> Invoke-Sqlcmd -ServerInstance 'DB001' -Query "sp_who2 '107';"

Invoke-Sqlcmd: Duplicate column names are not permitted in SQL PowerShell. To repeat a column, use a column alias for the duplicate column in the format Column_Name AS New_Name.

PS C:\> $PSVersionTable.PSVersion.ToString()
7.2.5

Upvotes: 0

Views: 724

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

You can insert the results of a stored procedure into a table. It's verbose for powershell, but still manageable.

$cmd = @'
    drop table if exists #results;
    create table #results (
        SPID smallint NULL,
        Status VARCHAR(20) NULL,
        Login sysname NULL,
        HostName sysname NULL,
        BlkBy varchar(40) NULL,
        DBName sysname NULL,
        Command varchar(100) NULL,
        CPUTime bigint NULL,
        DiskIO bigint NULL,
        LastBatch varchar(20) NULL,
        ProgramName sysname NULL,
        RepeatedSPID smallint NULL,
        REQUESTID int NULL
    )
 
    insert into #results
    exec sp_who2 'yourlogin';

    select *
    from #results
'@

invoke-sqlcmd -ServerInstance . -Query $cmd;

Is that too much to type all at the commandline? For sure. Wrap that in a function (along with the ability to provide a parameter for the login) and throw it in your powershell profile.

Upvotes: 3

Related Questions