How do you access an SQL Server sequence from PowerShell with invoke-sqlcmd?

Question

In my sql server 12.0 I have created a sequence:

CREATE SEQUENCE [dbo].[SESSIONI_dv_seq] 
 AS [bigint]
 START WITH 1
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CYCLE 
 CACHE;

sql server management studio works perfectly:

select Next value for  dbo.SESSIONI_dv_seq

return the correct value.

At this point, in a powershell script I want to obtain a new sequence value using invoke-sqlcmd

$DataSet = Invoke-Sqlcmd -Database "alglogistica" -Query "SELECT SESSIONI_dv_seq_value=next value for dbo.sessioni_dv_seq;" -ServerInstance  "10.81.104.185\SQL14"

This statement doesn't work (without no error returned by the script).

I have tried other selects in the query and these work perfectly.

David Brabant · Accepted Answer · 2017-07-17 10:49:14Z

1

Something like this?

$result = Invoke-SQLCmd -Database alglogistica `
    -query "SELECT next value for dbo.sessioni_dv_seq as SESSIONI_dv_seq_value" `
    -serverinstance  "10.81.104.185\SQL14"
$id = $result.SESSIONI_dv_seq_value

answered Jul 17, 2017 at 10:49

David Brabant

44k16 gold badges93 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do you access an SQL Server sequence from PowerShell with invoke-sqlcmd?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related