SQLCMD powershell script with data taken from a file unable to output the expected number

83 Views Asked by At

I have a csv file which has one column named PurchaseorderID and some numbers below it. I have a PS script which is supposed to fetch each row from that file and complete a SQL query and run it. However, when I run below script:-

$csv = Import-Csv 'c:\path\to\file.csv'

Foreach ($row in $csv) {
    Invoke-Sqlcmd -ServerInstance '.\SQL' -Database DB01 -Query "INSERT
    into  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage] 
    values (
        $($row.PurchaseOrderID), 
        4, 
        'Timeout waiting for mainframe to send response message', 
        getdate(), 
        'deb00535'
    )"
}

I get below output:-

insert into  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage] 
values (, 4, 'Timeout waiting for mainframe to send response message',getdate(),'deb00535'

There no output, before "4" where its supposed to be.

Now when I remove the "PurchaseorderID" relation, it does output the numbers in the lines, but with a "H1=" induced header.

insert into  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage] 
values (@{H1=PurchaseOrderID}, 4, 'Timeout waiting for mainframe to send response message',getdate(),'deb00535')

Not sure where I am doing wrong. Please help.

This is the code through which I generate the CSV.

$ServerName = "SQLGBUYPROD"
$DatabaseName = "BuyerWorksheetCentralRepository"
$Query =  "SELECT PurchaseOrderID
FROM [BuyerWorksheetCentralRepository].[po].[PurchaseOrder]
where DivisionNum IN (8,10,28)
and ponumprefix = 0 -- Root
and inprocessstatusid IN (4) --- In Transmission
and updatetime > '2020-10-08 00:00:00.000' AND updatetime < DATEADD(hour, -1, GETDATE())
order by updatetime desc"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables | Out-File "D:\Chayan\POmiss\st6po.csv" -Append 
0

There are 0 best solutions below