XP_Sendmail

Question

XP_Sendmail

davidb-575125

SSC Enthusiast

Points: 100
More actions
December 7, 2007 at 11:37 am

#181953

eg.
__SERVERNAME_Company_SMART Output Files_Gamestop FTP_GamestopBAI.zip.fbs
I can't find if it is outlook that is adding the "_" where "\" are supposed to be of if it is SQL. Doesn't really matter, all I want as the attachment if the name of the attachment GamestopBAI.zip.fbs. Something is modifying the name. Any help would be appreciated. BTW I am our sysadmin, not the SQL programmer so I will try to answer questions as best as possible.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Sleet SSC Veteran Points: 218 More actions · Answer 1

I'm having the same problem. I can add that if I use sqlmail to send the file to an internal user on our exchange server the attachment comes out fine. If I send to any outside (internet) address then the __ path problem occurs.

A recent issue I had that might be related:

SQL2000 - SP4. The SQL2005 analyzer was installed and caused an issue with the semmap.dll file. It had to be copied and renamed to semmap90.dll in two locations or else the sqlmail task could not even be inserted into the DTS package (an error relating to this file occured). Did you have this problem and solution? It might be related.

davidb-575125 SSC Enthusiast Points: 100 More actions · Answer 2

Yes, Internal emails don't add the strange path, you simply get the attachment name. It seems to only be happening on external emails. We did find that if we put the files on one of the SQL local drives and attached them, it works properly. But if the files are stored on a network share or different machine and sent via administrative share then the problem occurs. So basically we think that if you attach files from somewhere other than local drives and send attachments to external email addresses you are going to get that path as part of the attachment name.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

I have seen similar issues and do not have a true solution, but a possible work around would be using xp_cmdshell with the script that is sending the mail to copy the file to the local harddrive and then delete it again when it is not needed there.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Mark Sleet SSC Veteran Points: 218 More actions · Answer 4

davidb (12/14/2007)
Yes, Internal emails don't add the strange path, you simply get the attachment name. It seems to only be happening on external emails. We did find that if we put the files on one of the SQL local drives and attached them, it works properly. But if the files are stored on a network share or different machine and sent via administrative share then the problem occurs. So basically we think that if you attach files from somewhere other than local drives and send attachments to external email addresses you are going to get that path as part of the attachment name.

I'm even getting it off of a local drive!

Edit:

I've tried using the root of the c and d drives on the sql server with no success.

Where did you put the files on the local drive? Perhaps there is a default directory that doesn't add the path.

I've tried a couple workarounds using Exchange forwarding.

Creating a contact with an external email address did not affect the problem.

Creating a user and setting the Exchange General-Delivery Options to Forward the email to the outside address. I had to create a contact with the outside address and then forward the new internal user to the outside contact.

These both failed with the same results.

Sending email to the new address (not using SQLMAIL) or the contract directly with an attachment worked fine.

Mark Sleet SSC Veteran Points: 218 More actions · Answer 5

Here is the solution I'm using that does the trick. Similar to Tim's suggestion I think.

master.dbo.xp_sendmail

@recipients='outsiderecipient@email.com'

, @subject='sent via sql server directly'

, @message='Here is your file'

, @attachments='d:\data\testfile.zip'

I put this in an "Execute SQL Task" instead of using the SQL mail procedure.

There are more parameters but this is the basic solution that works for me. I'm not sure if I still need the MAPI client on the SQL server but I do see that the email originates from the SQL Server service user. However it does not remain in the sent items of the Exchange server (A good thing).