Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sp_send_dbmail problem

Author  Topic 

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-26 : 08:43:36
I am trying to use the sp_send_dbmail sproc but I get the following error:

Msg 22051, Level 16, State 1, Line 0
Attachment file C:\test.txt is invalid.


My code:


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Server Mail Profile blah blah',
@recipients = 'myEmail@somewhere.com,
@body = 'Hello World',
@subject = 'Hello World',
@file_attachments = N'C:\test.txt'


why would that be? The test.txt file does exist on my drive!

Please advise.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 09:06:20
That is not the point.
You must realize that all file paths are relative to the SQL Server you are running the code for.

@file_attachments = N'\\MyLocalMachineIsNotTheSameAsMyServer\c$\test.txt'




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-26 : 09:48:17
Hi Peso, I do realiset that now :)

but when I try a full path name like :

"\\tfsvr\Company\Database Development\TestReports\"SupressedRecords-20080226.txt

I still get the same error message.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 09:56:01
@file_attachments = N'\\tfsvr\Company\Database Development\TestReports\SupressedRecords-20080226.txt'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-26 : 10:02:52
Hi Peso, I tried it without the double quotes but still the same error! I don't want to waste your time on this so thanks for your help but the funny thing that's the same path I used for my sqlcmd comand which creates the .txt file.

Something strange happening here.
Go to Top of Page

talleyrand
Starting Member

35 Posts

Posted - 2008-02-26 : 11:57:34
Did you use single quotes as Peso suggested or no quotes and did the single quotes encompass the entirety of the fully qualified file name, not just the path?
Go to Top of Page

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-26 : 12:14:04
Hi all,

I have finally managed to get it to work. The double quotes were no longer necessary as Peso suggested. The problem was with the SQL Service account not having the correct access to the shared drive. The file now attached to my email perfectly!

Thanks all for your help.
Go to Top of Page

am3ba
Starting Member

2 Posts

Posted - 2008-08-29 : 02:42:08
Hi, I'm having some trouble with this parameter too. I try to attach a file from the local disk, I've tried with both

@file_attachments = 'C:\file.bmp'

and

@file_attachments = N'\\C:\file.bmp'

but I still don't get it. Can you help me with this? Apart from that, what is the "N" for?
Go to Top of Page

am3ba
Starting Member

2 Posts

Posted - 2008-09-03 : 07:39:38
Hi, Alu.

I believe the problem is SQL Server doesn't manage external files. I mean, whatever file you want to send, must be in the same machine in which the SQL Server is.

So the solution is to previosly copy your file test.txt to a folder in your server, and then you can send it without problems (that's what I've done).
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 09:01:27
No, you can use UNC paths for files. The files can be on another machine.
See previous responses on this topic.

Pay special attention to post made 02/26/2008 : 12:14:04


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

- Advertisement -