Solved

Using AspEmail and XUpload to send email with remote attchaments

Posted on 2006-11-08
19
482 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 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