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.
| Author |
Topic |
|
rtumati
Starting Member
2 Posts |
 Posted - 2009-03-09 : 10:02:27
|
| We have a SQL agent job that does some processing and mails a log file as an attachment at the end using sp_send_dbmail. The job uses BCP to create the log file and it writes it to a shared location before mailing it as an attachment. Here's the code snippet that handles this logic:DECLARE @sql VARCHAR(1000)SET @sql = 'BCP "SELECT Line FROM ##EDI_Error_Log ORDER BY Line_Id " QUERYOUT \\Log_Files\ErrorLog.txt -T -c -S ' + @@SERVERNAMEEXEC master.dbo.xp_CmdShell @sql EXEC @return_code = msdb.dbo.sp_send_dbmail @profile_name = 'OEMail', @recipients = 'youremail@company.com', @subject = 'EDI Error Listing. ', @body = 'EDI Error Listing. ', @file_attachments ='\\Log_Files\ErrorLog.txt'We are running 64-bit SQL Server 2005 SP2 and sometimes we are seeing this error. When I check the share I see the log file there but not sure what was causing this error."Attachment file \\Log_files\ErrorLog.txt is invalid."The mail works most of the time and we get this error every now so permissions may not be the issue.Any ideas on what might be causing this intermittent error?Thanks,Ramesh |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-09 : 10:22:25
|
| Is that file particularly large when it fails? I'm wondering if it's still being locked by the write process when it's being attached. What happens if you add a waitfor statement to delay 30 seconds between your BCP and your mail?Mike"oh, that monkey is going to pay" |
 |
|
|
rtumati
Starting Member
2 Posts |
Posted - 2009-03-09 : 10:51:35
|
| Thanks for your input Mike!The file size is around 4KB. As you have mentioned, locking might be the one causing this issue. I'll add some delay to the code and let you know how it works out. The problem is that the issue is random, I'll need to watch the process closely for the next couple of days to make sure that the fix is working.Couple of questions:1) Did you ever run into any locking issues with BCP before? Doesn't the execution wait until xp_CmdShell completes writing to the file.2) Do you recommend using some other approach to accomplish the same logic if locking will be an issue with BCP.Thanks,Ramesh |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-09 : 11:05:10
|
| >>Do you recommend using some other approach to accomplish the same logic if locking will be an issue with BCPHave you considered using the parameters: (@query and @attach_query_result_as_file)?Be One with the OptimizerTG |
|
|
|
|
|
|
sp_db_sendmail (DB Mail) - Attachment file is inva