Solved

Using AspEmail and XUpload to send email with remote attchaments

Posted on 2006-11-08
19
472 Views
Last Modified: 2008-01-16
I am using AspEmail through a stored procedure to send an email. I have the procedure working, and emails are received. I can even send attachments that are on the SQL server. The problem now is that I need to attach files to the email that are on a remote server. Reason being our ASP pages along with the attachments reside on server A and the database resides on server B. These servers are not on the same network (so mounting drives, etc is out). I have AspEmail and XUpload installed on server B. Server B runs the stored procedure below:

PROCEDURE [dbo].[sp_SendEmail]
      @Sent bit = 0 output,
      @Response varchar(255) = '' output,
      @FromName varchar(255) = null, @FromAddress varchar(255),
      @ToName text = null, @ToAddress text,
      @Subject varchar(255), @BodyText text,
      @Cc text = null,
      @Bcc text = null,
      @Attachment text = null, -- separate attachments with ";"
      @TestMode bit = 0, -- 1=Don't deliver mails, instead send mail to the user using net send.
      @Server varchar(100) = null,
      @LocalFile varchar(200) = null
AS
BEGIN

/* Example of testing:
DECLARE @Sent bit, @Response varchar(255)
EXEC sp_SendEmail @Sent output, @Response output,
      @FromName = 'MFK',
      @FromAddress = 'email@addres.com',
      @ToName = '',
      @ToAddress = 'email@address1.com,email@address2.com',
      @Subject = 'This is an Apollo SQL mail test with remote attachment',
      @BodyText = 'Test of AspEmail w/o BS',
      @Attachment = '/blahblah/filename.doc',
      @LocalFile = 'c:\temp\filename.doc',
      @Server = 'www.serverA.com'
SELECT @Response AS Response
*/

DECLARE @sntp int, @hr int, @Source varchar(255),  @upload int

SELECT @sntp = 0, @Sent = 0, @Response = ''

EXEC sp_OACreate 'Persits.MailSender', @sntp OUTPUT

EXEC sp_OASetProperty @sntp, 'Host', '00.00.00.00'

EXEC sp_OASetProperty @sntp, 'Queue', True

EXEC sp_OASetProperty @sntp, 'Subject', @Subject

EXEC sp_OASetProperty @sntp, 'Body', @BodyText

EXEC sp_OASetProperty @sntp, 'FromName', @FromName

EXEC sp_OASetProperty @sntp, 'From', @FromAddress

-- Add Recipients
Chunk of code that loops through receipients

-- Add Attachments
IF DATALENGTH(@Attachment) > 0
BEGIN
      SELECT @upload = 0
      EXEC sp_OACreate 'Persits.XUpload', @upload OUTPUT
      EXEC sp_OASetProperty @upload, 'Server', @Server
      EXEC sp_OASetProperty @upload, 'Script', @Attachment
        --I have commented out the piece that loops through the attachments as there may be more than one
      --declare @Attachment1 varchar(255)
      --declare list cursor for
      --select element from master.dbo.fn_SplitList(@Attachment,';') where len(element)>0
      --open list
      --fetch next from list into @Attachment1
      --while @@fetch_status = 0
      --begin
            EXEC sp_OAMethod @upload, 'MakeHttpRequest', 'GET', '', @LocalFile
            EXEC sp_OAMethod @sntp, 'AddAttachment', NULL,  @LocalFile
            EXEC sp_OAGetErrorInfo @upload, @Source OUTPUT, @Response OUTPUT

            --fetch next from list into @Attachment1
      --end
      --close list
      --deallocate list
END      
*/

IF @sntp = 0
BEGIN
      EXEC sp_OAGetErrorInfo @sntp, @Source OUTPUT, @Response OUTPUT
      RETURN
END

EXEC sp_OAMethod @sntp, 'Send', @Sent OUTPUT

EXEC sp_OADestroy @sntp
EXEC sp_OADestroy @upload

IF @Response = '' SELECT @Response = 'Sent successfully...'
END PROCEDURE

As you can see, I will eventually need to add mulitple attachments, but have commented that out to just get one working.

I am not that familar with Persits programs and their manuals are a little sparce. I have tried searching on the internet but have found no help. Hopefully someone knows their software and can help.

Thank you.
0
Comment
Question by:mfkaminski
  • 9
  • 7
19 Comments
 
LVL 25

Expert Comment

by:kevp75
ID: 17907855
curiousity grips me, although I do not have a solution for you.

I am wondering,however, why are you trying to use an ASP/VB component inside a SP?   Isn't there something called SQL Mail that can be run directly in SQL Server?
0
 

Author Comment

by:mfkaminski
ID: 17907914
SQL Mail is a monster, and besides, we need to send to remote server
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17908010
I've never seen someone try to send an email through a SP before.

I do in fact believe that it is not possible to do, especially with an ASP component
0
 

Author Comment

by:mfkaminski
ID: 17908030
Yes, it is possible.  I have it working. I just cannot attach a remote file. I can attach a local one.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17908233
is the remote file in your network?

as a guess, I would imagine that a remote file could not be sent.  Imagine if you would, you have outlook open, and try attaching a file that exists somewhere other than your PC....won't work...

I guess what would probably have to happen is a file upload process would need to be built in addition to your emailer, in order to make it a local file
0
 

Author Comment

by:mfkaminski
ID: 17908244
If you read the question title, you will see that I am using both AspEmail AND XUpload.
0
 

Author Comment

by:mfkaminski
ID: 17908468
I have resolved this myself, as I received no helpful responses. I am sending the remote file over first in ASP, then running the procedure which loops through and attaches the file(s).
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17908682
>>
I have resolved this myself, as I received no helpful responses. I am sending the remote file over first in ASP, then running the procedure which loops through and attaches the file(s).
<<

this is exactly what I was recommending in my last post....
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:mfkaminski
ID: 17908751
Yes, I was already working on this solution when I posted. But for various reasons, it was not the solution I was looking for. Again, if you look at my code above, you will see the upload process called from within the stored procedure:
IF DATALENGTH(@Attachment) > 0
BEGIN
     SELECT @upload = 0
     EXEC sp_OACreate 'Persits.XUpload', @upload OUTPUT
     EXEC sp_OASetProperty @upload, 'Server', @Server
     EXEC sp_OASetProperty @upload, 'Script', @Attachment
        blah blah
          EXEC sp_OAMethod @upload, 'MakeHttpRequest', 'GET', '', @LocalFile
          EXEC sp_OAMethod @sntp, 'AddAttachment', NULL,  @LocalFile
          EXEC sp_OAGetErrorInfo @upload, @Source OUTPUT, @Response OUTPUT
blah blah
END    
XUpload is another component from Persits. You can use them in stored proceudres.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17909095
ok, well basically what it boils down to is (as I said in my second to last post), you cannot send a remote file.  You would need to upload the file first (which is what you are doing with that code), thus making it a local file.

which....answers your question.....
0
 

Author Comment

by:mfkaminski
ID: 17918782
The solution I wanted was to do it ALL in ONE stored procedure, as in my code above. The solution I am using now, splits its out. This is not ideal for my environment. So, even though, I have something working, it is not an answer to my question.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17919418
I'm confused.  You just said it was working?

0
 
LVL 25

Accepted Solution

by:
kevp75 earned 500 total points
ID: 17919426
I'm actually kind of curious, why do this direct in the db?  Couldn't you use a vbscript to do this for you.

Ultimately what it boils down to is you CANNOT attach a file that you do not have, in other words, you CANNOT attach a remote file.  You need to be able to get the file from the remote location, and then attach it from your local machine

(as I've already said...)
0
 

Author Comment

by:mfkaminski
ID: 17923522
Your conversation has grown tiresome
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17923901
if you don't want help don't ask questions.  You asked a question, I answered it
0
 
LVL 25

Expert Comment

by:kevp75
ID: 18262883
I am interested in the outcome
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now