Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

Using AspEmail and XUpload to send email with remote attchaments

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
mfkaminski
Asked:
mfkaminski
  • 9
  • 7
1 Solution
 
kevp75Commented:
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
 
mfkaminskiAuthor Commented:
SQL Mail is a monster, and besides, we need to send to remote server
0
 
kevp75Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
mfkaminskiAuthor Commented:
Yes, it is possible.  I have it working. I just cannot attach a remote file. I can attach a local one.
0
 
kevp75Commented:
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
 
mfkaminskiAuthor Commented:
If you read the question title, you will see that I am using both AspEmail AND XUpload.
0
 
mfkaminskiAuthor Commented:
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
 
kevp75Commented:
>>
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
 
mfkaminskiAuthor Commented:
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
 
kevp75Commented:
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
 
mfkaminskiAuthor Commented:
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
 
kevp75Commented:
I'm confused.  You just said it was working?

0
 
kevp75Commented:
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
 
mfkaminskiAuthor Commented:
Your conversation has grown tiresome
0
 
kevp75Commented:
if you don't want help don't ask questions.  You asked a question, I answered it
0
 
kevp75Commented:
I am interested in the outcome
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now