view raw
Syphirint Syphirint - 9 months ago 34
PowerShell Question

String is inserted with additional '?' in SQL Server

I'm using Exchange Web Services to get the text from an email and add it to a database. So I start like this:

$PropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet(
$PropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;

$email_obj = [Microsoft.Exchange.WebServices.Data.EmailMessage]::Bind($service,$itemID);

#getting rid of replies
$Message = $email_obj.Body.Text -replace '\s+', ' '
if($Message -like "* From: *"){
$Message = ($Message -split " From: ")[0]
if($Message.IndexOf('_') -ne (-1)){
$Message = $Message.Remove($Message.IndexOf('_'))

Then, I insert the email body in SQL Server:

$query = "INSERT INTO DB.Table ([ID],[Message],[Sender],[Recipient],[TimeStamp])
VALUES ($ID,'$Message','$Sender','$Recipient',{0})" -f (Get-TimeStamp)
Invoke-Sqlcmd -Query $query -ServerInstance $server

But the
in front of the string that is the message. Tried to format the string before doing the
but it didn't help. Can't find any hidden characters like
Only the string that I get from the body of the email produces this result.


The solution has to do with the data format, like everyone said. The column is nvarchar and don't know exactly what data format was the string that I was trying to insert, but it had 1 or more unknown characters sometimes. To solve this, just added N prefix while doing INSERT in the table, like this:

$query = "INSERT INTO DB.Table ([ID],[Message],[Sender],[Recipient],[TimeStamp])
                  VALUES ($ID,N'$Message','$Sender','$Recipient',{0})" -f (Get-TimeStamp)
Invoke-Sqlcmd -Query $query -ServerInstance $server

This article from @AaronBertrand explains data formats in SQL.