powershell : pipe get-content to ps1 file with parameters

Question

I'm trying to write a script which uses the powershell cmdlet get-content tail and inserts the new lines into the sql server table. i can't get the syntax to pipe the tail to the sqlinsert.ps1 file that handles the table insert.

i'm looking for help on how to pipe "get-content tail" to a sqlinsert.ps1 file to do a sql database insert statement using the following :

$startTime = get-date
Write-Host "\\iisserver\logs\Logs-$("{0:yyyyMMdd}" -f (get-date)).txt"
get-content "\\iisserver\logs\Logs-$("{0:yyyyMMdd}" -f (get-date)).txt" -tail 1 -wait  |  & "sqlinsert.ps1" -stmp $("{0:yyyy-MM-dd hh:mm:ss.fff}" -f (get-date)) -method "Error" -msg $_
# %  { "$_ read at $(Get-Date -Format "hh:mm:ss")" }

in the sqlinsert.ps1 :

param ([string]$stmp, [string]$method, [string]$msg ) 
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$serverName';database='$databaseName';User ID = $uid; Password = $pwd;"
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

$sql = "insert into [tbl_iiserrors] (errstamp, method, msg) values (@stmp , @method, @msg)  "

. . .

error i get:

& : The term 'sqlinsert.ps1' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At C:\Temp\ob\iislog\tst_tail.ps1:3 char:95
... Mdd}" -f (get-date)).txt" -tail 1 -wait | & "sqlinsert.ps1" -stmp $ ...
                                              ~~~~~~~~~~~~~~~
CategoryInfo : ObjectNotFound: (sqlinsert.ps1:String) [], CommandNotFoundException

FullyQualifiedErrorId : CommandNotFoundException
Suggestion [3,General]: The command sqlinsert.ps1 was not found, but does exist in the current location. Windows PowerShell does not load commands from the current location by default. If you trust this command, instead type: ".\sqlinsert.ps1". See "get-help about_Command_Precedence" for more details.

The sqlinsert.ps1 works when i run it from powershell command : PS c:\temp> .\sqlinsert -stmp 2020-11-20 00:00:00 -method 'eek' -msg 'uh hello'

The error says that it cannot find the file in the current directory. Try with the full path: `& 'C:\temp\sqlinsert.ps1'` — Mathias R. Jessen, Nov 20 '20 at 21:53
after the pipe, i changed it to .\sqlinsert.ps1 -stmp $("{0:yyyy-MM-dd hh:mm:ss.fff}" -f (get-date)) -method "Error" -msg $_ It looks like its running but it doesn't look like its passing through correctly? i don't see any insertions. — phill, Nov 20 '20 at 22:02

score 0 · Answer 1 · answered Nov 20 '20 at 22:05

0

In order to bind pipeline input to a parameter, you need to decorate it with a [Parameter] attribute and specify that it accepts pipeline input, like this:

param (
  [string]$stmp,
  [string]$method, 
  [Parameter(ValueFromPipeline = $true)]
  [string]$msg
)

See the about_Functions_Advanced_Parameters help file for more details about how to modify the behavior of parameters

answered Nov 20 '20 at 22:05

Mathias R. Jessen

157,619
12
148
206

C:\sqlinsert.ps1 : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input. + ... 1 -wait | .\sqlinsert.ps1 -stmp '2020-11-20 00:00:00' -method 'Erro ... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (2020-11-20 16:2...s 200 text/html:PSObject) [sqlinsert.ps1], Parameter BindingException + FullyQualifiedErrorId : InputObjectNotBound,sqlinsert.ps1 – phill Nov 20 '20 at 22:28

mklement0 · Answer 2 · 2020-11-21T03:12:17.957

By design, for security reasons, PowerShell requires you to signal the intent to execute a script located in the current directory explicitly, using a path - .\sqlinsert.ps1 - rather than a mere file name - sqlinsert.ps1; that is what the suggestion following the error message is trying to tell you.
- Note that you only need &, the call operator, if the script path is quoted and/or contains variable references - and .\sqlinsert.ps1 doesn't require quoting.
You can only use the automatic $_ variable, which represents the current input object from the pipeline inside a script block ({ ... }), such as one passed to the ForEach-Object cmdlet, which invokes that block for each object received via the pipeline.
Re the content of your script: Inside expandable strings ("..."), you cannot use @ to refer to variables to be expanded (interpolated); use regular, $-prefixed variable references or $(...), the subexpression operator to embed expressions; also, it looks like you're inserting string values into the SQL table, so you'll have to enclose the expanded variable values in embedded '...'

$startTime = get-date
Get-Content "\\iisserver\logs\Logs-$("{0:yyyyMMdd}" -f (get-date)).txt" -Tail 1 -Wait |  
 ForEach-Object {
   .\sqlinsert.ps1 -stmp ("{0:yyyy-MM-dd hh:mm:ss.fff}" -f (get-date)) -method "Error" -msg $_
 }

The alternative to using a ForEach-Object call is to modify your script to directly receive its -msg argument from the pipeline, as shown in Mathias' answer, in which case you must omit the -msg $_ argument from your script call:

Get-Content ... |
  .\sqlinsert.ps1 -stmp ("{0:yyyy-MM-dd hh:mm:ss.fff}" -f (get-date)) -method "Error"

powershell : pipe get-content to ps1 file with parameters

2 Answers2