troubleshooting Question

How to retrieve IMAGE (binary?) data type from SQL and convert to usable text string in ASP/VBScript

Avatar of worthyking1
worthyking1Flag for United States of America asked on
Microsoft SQL ServerVisual Basic ClassicASPVB ScriptSQL
6 Comments1 Solution121 ViewsLast Modified:
We have a SQL Server 2008 that houses our emails for our CRM (Goldmine) and a legacy business processes system built in VBScript.  It's pretty straightforward but having trouble with one thing.

The email message body is stored in the SQL DB as an IMAGE datatype (the column name is 'RFC822'). I am writing a very simple script to retrieve emails from a certain mailbox and check the message body for a certain string.

Problem is that my script cannot see the value of the RFC822 column.

I've tried casting it and converting it and I don't get any errors, but I just keep getting a null value on my variable MsgBody. What am I doing wrong here?

DebugCodeOn = True

SQL = 	"SELECT   ACCOUNTNO, MAILREF, CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(MAX)) AS MsgBody " & _
		"FROM         MAILBOX WHERE     (USERID = 'SALES') "
Set rsEmails = con.Execute(SQL)

' Other variations of Select Statement tried as well:
' 		"SELECT   ACCOUNTNO, MAILREF, RFC822 AS MsgBody "
'		"SELECT   ACCOUNTNO, MAILREF,  CONVERT(varchar(MAX), CONVERT(varbinary(MAX), RFC822)) AS MsgBody "


'Loop through the emails
While NOT rsEmails.EOF

	' set vars
	AcctNo = rsEmails("ACCOUNTNO")
	MailRef = rsEmails("MAILREF")
	MsgBody = rsEmails("MsgBody ")     ' <-- THIS IS ALWAYS NULL/EMPTY NO MATTER WHAT SELECT STATEMENT USED :(

	If DebugCodeOn then Response.Write "<p>This email ref: [" & MailRef & "]"
	If DebugCodeOn then Response.Write "<p>" & MsgBody   
	
	Set objRegEx = CreateObject("VBScript.RegExp")
	objRegEx.Global = True
	objRegEx.Pattern = "\d{3}-\d{7}-\d{7}"
	Set colMatches = objRegEx.Execute(MsgBody)  

	If colMatches.Count > 0 Then

	   For Each strMatch in colMatches
		If DebugCodeOn then Response.Write "<p>References order # " & strMatch.Value
	   Next

	End If

		If DebugCodeOn then Response.Write "##############################################################################"

  rsEmails.MoveNext()
Wend
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros