April 2, 2019 at 10:39 am
Hi, I'm hoping someone can help me with this, it's very shonky but it's what I've got currently. I'm on version 4 with 2014 r1
So I'm calling an API through Powershell. If I run the script directly in Powershell it takes less than a second, as soon as I try and run it from XP_CMD_Shell it's taking 7 seconds plus (with quite a bit of variance).
I've noticed that when I'm echoing the variables to screen (which is for testing only) Powershell is passing the output to SQL (SQL doesn't need the output) when I remove these variables it's much quicker.
But for whatever reason I can't turn off the echo from the ConvertTo-Json command below. I've tried piping it to null but that just makes it null. Any ideas?
$body = @{
"checkbox_stdemailtosender"=$data.checkbox_stdemailtosender
"checkbox_stdemailtoreceiver"=$data.checkbox_stdemailtoreceiver
"parcel_select_handtekeningvo"=$data.parcel_select_handtekeningvo
"parcel_input_aantalpakketten"=$data.parcel_input_aantalpakketten
"parcel_input_gewicht"=$data.parcel_input_gewicht
"parcel_input_waarde"=$data.parcel_input_waarde
"parcel_input_afmetingl"=$data.parcel_input_afmetingl
"parcel_input_afmetingb"=$data.parcel_input_afmetingb
"parcel_input_afmetingh"=$data.parcel_input_afmetingh
"parcel_select_nabb"=$data.parcel_select_nabb
"parcel_input_bedrijfsnaam"=$data.parcel_input_bedrijfsnaam
"parcel_input_afdeling"=$data.parcel_input_afdeling
"parcel_input_voornaam"=$data.parcel_input_voornaam
"parcel_input_achternaam"=$data.parcel_input_achternaam
"parcel_input_email"=$data.parcel_input_email
"parcel_input_inhoud"=$data.parcel_input_inhoud
"parcel_input_inhoudopetiket"=$data.parcel_input_inhoudopetiket
"parcel_input_straat"=$data.parcel_input_straat
"parcel_input_straat2"=$data.parcel_input_straat2
"parcel_input_huisnummer"=$data.parcel_input_huisnummer
"parcel_input_plaats"=$data.checkbox_stdemailtosender
"parcel_select_land"=$data.parcel_select_land
"parcel_input_telefoonnummer"=$data.parcel_input_telefoonnummer
"parcel_input_referentie"=$data.parcel_input_referentie
"parcel_select_vervoerder"=$data.parcel_select_vervoerder
"parcel_select_vervoerderoptie"=$data.parcel_select_vervoerderoptie
"parcel_input_postcode"=$data.parcel_input_postcode
}
ConvertTo-Json $body
Aside from the obvious concerns about using xp_cmdshell (which I share, but we are where we are) any general advice about speeding this up would be greatly appreciated.
Thanks
April 2, 2019 at 1:39 pm
With this command you are not actually doing anything with it....which is why it sends the data back to SQL and the screen. Normally you would put this into a variable such as:
$jsonBodyVar = ConvertTo-Json $body
This puts the value into the variable and nothing is returned to SQL or the screen. You could then write this to a log file (for testing) or whatever else you want to do with it.
April 2, 2019 at 2:19 pm
Thanks Garry,
It does indeed not do anything. Who knew? 😉
That's a lot quicker, at least, thanks!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply