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