xp_smtp_sendmail error

Question

xp_smtp_sendmail error

gvaldridge

SSC-Addicted

Points: 478
More actions
December 29, 2006 at 1:05 pm

#170895

I decided to test out xp_smtp_sendmail on a test server. I can get it to email just fine from Query Analyzer as long as I don't make any references to any files. When I do, I get an error saying that the file does not exist even though it does.
I'm using the following code sample to get the error. It also produces the same error if I use @attachment with one file specified, and/or @messagefile.
exec master.dbo.xp_smtp_sendmail
    @FROM       = N'email@server.com',
    @FROM_NAME = N'Giselle Valenzuela',
    @TO         = N'email@server.com',
    @priority   = N'HIGH',
    @subject    = N'Hello SQL Server SMTP Mail',
    @message    = N'Goodbye MAPI, goodbye Outlook',
    @type       = N'text/plain',
    @attachments = N'c:\Test2.txt;c:\Test.txt',
    @server     = N'email.server.COM'
go
Here's the error I get:
Error: @attachments file "c:\Test2.txt" does not exist

Viewing 12 posts - 1 through 11 (of 11 total)

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

john-132685 SSCertifiable Points: 6084 More actions · Answer 1

Is that file in the C:\ directory of the server and not the client? It needs to be or you need to use a UNC to have the server go to your client to get the attachment (\\myclientpc\filename--and the server would have to have permissions to access this directory)

gvaldridge SSC-Addicted Points: 478 More actions · Answer 2

Both files exist on the test server I'm using. The SQL I posted (modified with more useful values of course), was run in a SQL Query Analyzer session on the server in question.

Should there be any reason why running this from Query Analyzer confuses the issue?

mrpolecat SSCertifiable Points: 6964 More actions · Answer 3

mrpolecat

SSCertifiable

Points: 6964

December 29, 2006 at 1:12 pm

#680591

it is case sensitive. try c:\test2.txt

mrpolecat SSCertifiable Points: 6964 More actions · Answer 4

the stored procedure is looking for it on the server's c drive when running through query analyzer.

gvaldridge SSC-Addicted Points: 478 More actions · Answer 5

The exact files' names are Test2.txt and Test.txt. So changing the case of Test2.txt to test2.txt will not help if it's case sensitive.

Both these files are located in the C:\ directory of the server that I'm running the script on.

mrpolecat SSCertifiable Points: 6964 More actions · Answer 6

when I tested it on my server I got the same error but when I changed it to c:\test2.txt it gave me an error on the file Test.txt. I changed that one as well and it worked just fine. Maybe you could try changing the name of the file on the server to be all lower case as well.

john-132685 SSCertifiable Points: 6084 More actions · Answer 7

just to be certain try

master..xp_cmdshell "dir c:\test2.txt"

to see if your sqlserver session really does see/have permissions to the directory.

If it does... Idunno. It's a 3rd party thing... mayby try full UNC path anyway.

mrpolecat SSCertifiable Points: 6964 More actions · Answer 8

I now have to recant my post about the sp being case sensitive. I tested file location (server vs. client) at the same time and that was the piece that caused it to fail. Sorry for the red herring.

gvaldridge SSC-Addicted Points: 478 More actions · Answer 9

When I run master..xp_cmdshell "dir c:\Test.txt"

I get the following recordset:

Volume in drive C has no label.

Volume Serial Number is 48C9-F18C

NULL

Directory of c:

NULL

File Not Found

NULL

I tried the xp_cmdshell function above on a different server. It brought back information for the file. I'm going to try adding attachments on that server.

mrpolecat SSCertifiable Points: 6964 More actions · Answer 10

If the admin share is there and you have access to it it would be \\server\c$ otherwise you will need to create a share to access. Security concious admins remove the c$ share. I used c:\test.txt and it worked ok for me. Since John's suggestion also shows no file it does seem like a permission error.

john-132685 SSCertifiable Points: 6084 More actions · Answer 11

If it didn't give you an error on "dir c:\..." just file not found, then the file isnt there or it is hidden. The path is there.

Make sure what server you are running on, triple check that the file you're talking about is where you think it is, the DIR command says c:\ is there but the file isn't.