| Author |
Topic |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2008-09-11 : 13:42:43
|
| I'm trying to use sp_send_dbmail and attach a couple files. The files are dynamically named within the SP that calls sp_send_dbmail. I put the file names and paths into a variable called @fileattach. In sp_send_dbmail I'm using @file_attachments = @fileattach to tell it the filenames to attach. However only the first one gets attached (they are in the same directory). If I hardcode the two file names into @file_attachments it works fine but setting it equal to a variable doesn't. I then tried using dynamic sql to do it.select @sql = ' EXEC msdb..sp_send_dbmail @recipients ='+''''+ @recip+''', @subject = ''My emal subject'', @file_attachments = '+''''+@fileattach+'''' exec(@sql) select @sqlThis too only attached one file but when I use the actual SQL for @sql (via the "select @sql" I put in) and run it it works fine and attaches both files. So exec(@sql) didn't work, but running the SQL from @sql manually does. Any ideas?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-11 : 13:45:04
|
| People prefer to receive multiple files in an email via one compressed file, so how about zipping the files together. You can do it programmatically via the command line for most compression utilities. We use pkzip, WinRAR, and Winzip.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-09-11 : 13:50:40
|
| That would be a good idea and something I would do if allowed but I know the answer will be no. I'm really stumped on this one. I can't even hardcode the file names in to make it work since they are created dynamically. I'd really like to figure it out or know what the issue is because it doesn't work.Another question. Is there a way to have a SendMail task in SSIS use a table to get the email list from rather than coding them in the TO section? |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-09-11 : 14:28:54
|
| Experts only please... |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-09-11 : 15:10:32
|
| Resolved. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-11 : 16:58:50
|
>>Care to share it?can't - that would violate the "experts only" clause  Be One with the OptimizerTG |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-09-11 : 17:33:58
|
| LOL. I'll try to talk like an expert at least. The issue was that I was doing everything inside of a WHILE loop and my rowcount was set to 1. Not entirely sure why that would cause it to only pick up one file instead of two but it did. I set the rowcount to 0 inside the loop and everything was fine. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-09-11 : 17:47:09
|
| Basically it's like this:--this will only attach one fileset rowcount 1 EXEC msdb..sp_send_dbmail @recipients = 'youremail@yourdomain.com', @subject = 'test', @file_attachments = '\\path_to_file\test1.txt;\\path_to_file\test2.txt'--this will attach both filesset rowcount 0 EXEC msdb..sp_send_dbmail @recipients = 'youremail@yourdomain.com', @subject = 'test', @file_attachments = '\\path_to_file\test1.txt;\\path_to_file\test2.txt'Not sure why rowcount affects sp_send_dbmail's ablility to attach files but it does. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-09-11 : 19:43:09
|
| Of course, Why wouldn't you want it to behave that way?CODO ERGO SUM |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-09-12 : 09:52:25
|
quote:
Originally posted by Michael Valentine Jones
Of course, Why wouldn't you want it to behave that way?CODO ERGO SUM
Is that a joke? Why would I expect rowcount to affect sp_send_dbmail and it's option @file_attachments = '\\path_to_file\test1.txt;\\path_to_file\test2.txt'? I still think it's a little bit of a bug in that it shouldn't make it attach only one file from the string of two file names I passed it. I wouldn't expect rowcount to affect how sp_send_dbmail works at all. I mean the same isn't true for @recip. If I have more than one recipient in the @recip variable and pass it to @recipients it will still send it to all the recipients even if the rowcount is 1. But if the rowcount is 1 and I pass it more than one file to attach then it only attaches one file. Seems like a bug to me but at least I figured out a way around it. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-09-12 : 09:57:04
|
quote:
Originally posted by Van...Is that a joke?...
Yes, it was a joke. Guess I should have make it funnier.  CODO ERGO SUM |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-09-12 : 10:00:03
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by Van...Is that a joke?...
Yes, it was a joke. Guess I should have make it funnier. CODO ERGO SUM
I figured so. No worries. I just wanted to explain it further... |
|
|
|
|
Possible SQL bug