Powershell and SQL - Must declare the scalar variable "@"

Question

when I execute the code "Must declare the scalar variable "@" returns to me. I'm trying to insert values from winevent to DB, when variables are "text" works fine, but when I insert variables from array doesn't work.

foreach ($servidor in $listadoServidores) {
$prueba = Get-WinEvent -ComputerName $servidor -FilterHashtable @{
    logname   = $logAuditar;
    StartTime = $fechaInicioBusqueda;
    EndTime   = $fechaFinBusqueda;
    Level     = $nivelEvento;} | select Id, LevelDisplayName, MachineName, TimeCreated, ContainerLog, ProviderName, Message

    foreach ($x in $prueba) {
        $contador++
        $SQLinsert = "use $SQLbd 
                      insert into $SQLtabla (id, eventid, servidor, log, nivel, origen, descripcion, fecha, hora)
                      values('$contador',$x.Id,'servidor','system','prueba','OrigenPrueba','pruebaDescripcion','$fechaActual','$horaActual');"

        invoke-sqlcmd -query $SQLinsert -ServerInstance $SQLinstancia -Username $SQLusuario -Password $SQLpassword
    }

}

ERROR: invoke-sqlcmd : Must declare the scalar variable "@". At line:48 char:13 + invoke-sqlcmd -query $SQLinsert -ServerInstance $SQLinsta ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand — SheZz0, May 07 '18 at 12:20
You can edit your question if you like to add something you forgot. — sticky bit, May 07 '18 at 12:22
It is not obvious, but this is ultimately a duplicate of https://stackoverflow.com/q/1145704/45375 — mklement0, May 07 '18 at 14:39

score 2 · Accepted Answer · edited May 07 '18 at 14:38

2

If you are using dot notation with any variable inside the string, use $(...) to get the value. Example, in your insert query use

 $($x.Id)

to get the Id from variable $x.

edited May 07 '18 at 14:38

mklement0

382,024
64
607
775

answered May 07 '18 at 12:42

Arulraj

423
3
10

Arulraj Ty so much! I'm a noob :D – SheZz0 May 07 '18 at 12:45

Powershell and SQL - Must declare the scalar variable "@"

1 Answers1