How can I access a SQL Server sequence from PowerShell using invoke-sqlcmd?

In my SQL Server 12.0, I 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 fine:
select Next value for  dbo.SESSIONI_dv_seq 

      

return the correct value.

At this point in powershell script I want to get 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 does not work (no errors returned by the script).

I tried other options in the request and they work great.

+3


source to share


1 answer


Something like that?



$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

      

+1


source







All Articles