Powershell code for sql throwing errors while running

Question

Am trying to use below code but am getting a syntax error on line 4 chr 5. For the love of me I am not able to find out what's wrong with my code. Can someone please help, Must be something really really stupid. PS, this is not my code, someone helped me with it.

$csv = Import-Csv 'D:\Chayan\POmiss\test.csv'
$SQLSERVER = "WSQL009D"

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

Can you post the error?

Steven
– Steven

2021-05-10 19:32:46 +00:00
Commented May 10, 2021 at 19:32 — Steven
– Steven, Commented May 10, 2021 at 19:32

HAL9256 · Accepted Answer · 2021-05-14 17:33:38Z

2

@Steven answer is good about using Here Strings make things simpler and easier to debug. In this case, it is not a PowerShell error that is happening. When you see "Msg 102, Level 15, State 1" that is a SQL error. You will have to check the schema of your database to ensure that you are passing in the right value. If you POID is a varchar type, then you may be simply missing a set of quotes around the VALUES that you are passing. e.g. :

...
values (
    '$($row.PurchaseOrderID)', 
     4,
     'Timeout waiting for mainframe to send response message', 
     getdate(), 
     'deb00535'
     )
...

Remember, you are replacing $($row.PurchaseOrderID) with the value in the CSV. You still need the single quotes in the query for SQL to not complain ;-). So your code becomes:

$csv = Import-Csv 'D:\Chayan\POmiss\test.csv'
$SQLSERVER = "WSQL009D"

Foreach ($row in $csv) {
    $query =
@"
    INSERT
        into  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage] 
        values (
            '$($row.PurchaseOrderID)', 
            4, 
            'Timeout waiting for mainframe to send response message', 
            getdate(), 
            'deb00535'
        )
"@

    Invoke-Sqlcmd -ServerInstance $SQLSERVER -Database BuyerWorksheetCentralRepository -Query $query

}

If you need debugging, you can always do:

Write-Host $query

And see if that query will actually work in SQL.

The last thing that might be happening is to check your CSV file in Notepad. You may have an extra row of data that is blank. The error message eerily seems like you have one entry that has a blank value that is trying to be inserted.

EDIT

@ChayanChakraborty Provided the sample file:

PurchaseOrderID PONumMaster DivisionNum HoursElapsedSinceLastStatusUpdate
--------------- ----------- ----------- ---------------------------------
16601566      536958           8                              2070
16601613      536998           8                              1471
16601626      537011           8                               700

Unfortunately @ChayanChakraborty, that is not a CSV file. Adding .CSV to the file name does not make it a Comma-Separated Values file. If you are exporting to file from Microsoft SQL Server Management Studio, you have to change the output options (see Here).

In Microsoft SQL Server Management Studio, go to Tools -> Options... menu. Then go to the Query Results -> SQL Server -> Results to Text section. Change the Output Format to Comma delimited, click Ok, and re-run your query. Your CSV file should only look like:

PurchaseOrderID,PONumMaster,DivisionNum,HoursElapsedSinceLastStatusUpdate
16601566,536958,8,2070
16601613,536998,8,1471
16601626,537011,8,700

You should verify that the end of the file also doesn't have the results as that will mess it up:

(3 rows affected)

Completion time: 2021-05-14T11:29:21.9260884-06:00

edited May 14, 2021 at 17:33

answered May 10, 2021 at 20:54

HAL9256

13.7k1 gold badge39 silver badges50 bronze badges

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

6 Comments

Steven Over a year ago

Do we need the single quotes if the column is defined as int? I think it would be typical of of an "ID". If I'm right I think it makes a case to cast the incoming CSV value to [Int] or leave it unquoted within the query string. Let me know what you think. Thanks!

Steven Over a year ago

Incidentally, one reason I moved the Query string out of the loop, was so I didn't have to worry about formatting, meaning not indenting @" & "@ ...

HAL9256 Over a year ago

@Steven you are right [INT]s don't need quoting, but I don't think we need to go to the extreme to do a "double" casting ;-), CSV [string] -> PSObj [string] -> Cast [int] -> $Query Cast [string]. I completely second you on pulling the Here Strings out of the loop for formatting (always hate to see the @" and "@ stick out like a sore thumb ;-))

Chayan Chakraborty Over a year ago

@HAL9256 this is what my csv looks like. I basically run a SQL query to find out Purchaseorderid which are still in status 4. Does this look like an issue?

Chayan Chakraborty Over a year ago

|

Steven · Accepted Answer · 2021-05-13 01:33:33Z

0

My guess is Invoke-SQLcmd isn't able to interpret the multi-line argument you gave -Query. This is typically done using a here string, like below:

$csv = Import-Csv 'D:\Chayan\POmiss\test.csv'
$SQLSERVER = "WSQL009D"

$QueryTemplate =
@"
INSERT INTO  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage]
    VALUES (
        %PurchaseOrderID%,
        4,
        'Timeout waiting for mainframe to send response message',
        GETDATE(),
        'deb00535'
    )
"@

Foreach ($row in $csv) {
    $Query = $QueryTemplate -replace '%PurchaseOrderID%', $row.PurchaseOrderID
    Invoke-Sqlcmd -ServerInstance $SQLSERVER -Database BuyerWorksheetCentralRepository -Query $Query
}

I changed it a little bit. Rather than recreate the entire query for every loop iteration I created a template with a known string to replace with data you're getting from the CSV, namely $row.PurchaseOrderID.

edited May 13, 2021 at 1:33

answered May 10, 2021 at 19:40

Steven

7,1271 gold badge17 silver badges16 bronze badges

3 Comments

Chayan Chakraborty Over a year ago

HI, Thank you so much for replying. But seems ike PS is still throwing that error in the line where invoke-sqlcmd is. Please see below:

Chayan Chakraborty Over a year ago

Invoke-Sqlcmd : Incorrect syntax near ','. Msg 102, Level 15, State 1, Procedure , Line 3. At line:18 char:5 + Invoke-Sqlcmd -ServerInstance $SQLSERVER -Database BuyerWorksheet ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Steven Over a year ago

That looks like SQL is returning the error. Take a look at HAL9256's Answer as he pointed out the same. It could be a quoting issue, take a look at the DB column itself to see if it takes an int datatype.

Collectives™ on Stack Overflow

Powershell code for sql throwing errors while running

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related