Solved

Using AspEmail and XUpload to send email with remote attchaments

Posted on 2006-11-08
19
473 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help in Copy Database Wizard From One SQL Server to Other SQL Server 10 33
SQL Server - SQL field is defined as text 3 30
TSQL - IF ELSE? 3 29
SQL VIEW 7 23
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

896 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

16 Experts available now in Live!

Get 1:1 Help Now