Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using AspEmail and XUpload to send email with remote attchaments

Posted on 2006-11-08
19
Medium Priority
?
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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
 

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

660 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