Syphirint Syphirint - 3 months ago 7
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(
[Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties);
$PropertySet.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;

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

#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
INSERT
adds
?
in front of the string that is the message. Tried to format the string before doing the
INSERT
but it didn't help. Can't find any hidden characters like
\n
,
\r
,
\t
or
\s
.
Only the string that I get from the body of the email produces this result.

Answer

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.