sp_send_dbmail Dynamic load of 'File Attachments' errors with "Attachment File is invalid"

• lyle.nielsen 31819

  SSC Veteran

  Points: 234

  More actions

  May 17, 2017 at 8:11 am

  #402069

  I need to dynamically search a folder on a local drive and load the file names into a string and then have the sp_send_dbmail set those attached files out in an email.

  If I create a cursor  and go through each file name and load that file_name into a string and do the sp_send_dbmail, the process fails with the error:
  Msg 22051, Level 16, State 1, Line 0
  Attachment file 'S:\Reports\List_Remote_Databases_V_05-16-2017.xls is invalid.

  But can take that exact string and run sp_send_dbmail manually and it works just fine.
  Here is what I have that works.
  @file_attachments='S:\Reports\List_Remote_Databases_V_05-16-2017.xls;S:\Reports\MatrixTbl_V1_05-16-2017.xls;S:\Reports\Sum_Server_Inst_V_05-16-2017.xls;S:\Reports\Sum_Srv_Inst_DB_Clust_TBL_V_05-16-2017.xls;S:\Reports\Sum_Srv_Space_Used_V_05-16-2017.xls;S:\Reports\Sum_Version_TBL_V_05-16-2017.xls'

  I took the file list from the string that is created dynamically.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  May 17, 2017 at 3:20 pm

  #1943009

  Is S: a mapped drive or a local drive? Is this run as a SQL Agent Job? If so, does the account it runs under have permissions on that path? How are you getting the file list?

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 17, 2017 at 4:25 pm

  #1943017

  I see you've found the "feature" where you can't have a space after your semicolon-delimited list of filenames, so that's good.

  Joe's probably right - it looks like a permissions problem.  If it runs for you, then you know the code's solid.  Take a look at the permissions (including file system permissions) that it runs as when it fails.  The answer is probably there.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• lyle.nielsen 31819

  SSC Veteran

  Points: 234

  More actions

  May 18, 2017 at 7:24 am

  #1943090

  Joe Torre - Wednesday, May 17, 2017 3:19 PM

  Is S: a mapped drive or a local drive? Is this run as a SQL Agent Job? If so, does the account it runs under have permissions on that path? How are you getting the file list?

  The S: is a local drive.  I ran both of these manually. I have not yet set up the job to run from SQL Agent.  I am getting the file list as this:
  declare @files table (ID int IDENTITY, FileName varchar(100))
  DECLARE @id_hold nvarchar(250),
     @id_max nvarchar(250),
     @file_names nvarchar(2000),
     @file_dir nvarchar(250),
     @first_file nvarchar(250)

  select @file_dir = 'S:\Reports\'

  insert into @files execute xp_cmdshell 'dir S:\Reports /b'
  insert into [dbo].[SUM_MAIL_REPORTS]
  ([FILE_ID],[FILE_NAME],[CRDate])
  select ID, FileName ,getdate() from @files

  delete [dbo].[SUM_MAIL_REPORTS]
  WHERE FILE_NAME = 'Backups'
    OR FILE_NAME is null

  select * from [dbo].[SUM_MAIL_REPORTS]

  Select @id_hold = min(FILE_NAME), @id_max = max(FILE_NAME)
    from [dbo].[SUM_MAIL_REPORTS]
    WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)

  select @first_file = 'YES'

  DECLARE db_cursor CURSOR FOR
   SELECT FILE_NAME
   FROM [MonitorDb].[dbo].[SUM_MAIL_REPORTS]
   WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)
   ORDER BY FILE_NAME

  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @id_hold

  WHILE @@FETCH_STATUS = 0
   BEGIN
    IF @first_file = 'YES'
       BEGIN
     select @file_names = ''''+@file_dir+@id_hold
           SELECT @first_file='NO'
       END
    ELSE
     select @file_names = @file_names + ';'+@file_dir+@id_hold
    select @file_names
      FETCH NEXT FROM db_cursor INTO @id_hold
   END
  -- finish the string

  CLOSE db_cursor
  DEALLOCATE db_cursor

  select @file_names = @file_names + ''''

  --select @file_names

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMailProfile'
    , @recipients = 'lyle.nielsen@hsc.utah.edu'
    , @subject  = 'test attachment'
    , @body   = 'An attachment'
    , @file_attachments=@file_names

  select @file_names

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 18, 2017 at 8:38 am

  #1943118

  lyle.nielsen 31819 - Thursday, May 18, 2017 7:24 AM

  Joe Torre - Wednesday, May 17, 2017 3:19 PM

  Is S: a mapped drive or a local drive? Is this run as a SQL Agent Job? If so, does the account it runs under have permissions on that path? How are you getting the file list?

  The S: is a local drive.  I ran both of these manually. I have not yet set up the job to run from SQL Agent.  I am getting the file list as this:
  declare @files table (ID int IDENTITY, FileName varchar(100))
  DECLARE @id_hold nvarchar(250),
     @id_max nvarchar(250),
     @file_names nvarchar(2000),
     @file_dir nvarchar(250),
     @first_file nvarchar(250)

  select @file_dir = 'S:\Reports\'

  insert into @files execute xp_cmdshell 'dir S:\Reports /b'
  insert into [dbo].[SUM_MAIL_REPORTS]
  ([FILE_ID],[FILE_NAME],[CRDate])
  select ID, FileName ,getdate() from @files

  delete [dbo].[SUM_MAIL_REPORTS]
  WHERE FILE_NAME = 'Backups'
    OR FILE_NAME is null

  select * from [dbo].[SUM_MAIL_REPORTS]

  Select @id_hold = min(FILE_NAME), @id_max = max(FILE_NAME)
    from [dbo].[SUM_MAIL_REPORTS]
    WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)

  select @first_file = 'YES'

  DECLARE db_cursor CURSOR FOR
   SELECT FILE_NAME
   FROM [MonitorDb].[dbo].[SUM_MAIL_REPORTS]
   WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)
   ORDER BY FILE_NAME

  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @id_hold

  WHILE @@FETCH_STATUS = 0
   BEGIN
    IF @first_file = 'YES'
       BEGIN
     select @file_names = ''''+@file_dir+@id_hold
           SELECT @first_file='NO'
       END
    ELSE
     select @file_names = @file_names + ';'+@file_dir+@id_hold
    select @file_names
      FETCH NEXT FROM db_cursor INTO @id_hold
   END
  -- finish the string

  CLOSE db_cursor
  DEALLOCATE db_cursor

  select @file_names = @file_names + ''''

  --select @file_names

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMailProfile'
    , @recipients = 'lyle.nielsen@hsc.utah.edu'
    , @subject  = 'test attachment'
    , @body   = 'An attachment'
    , @file_attachments=@file_names

  select @file_names

  Would something like this work for you?  It would be a whole lot simpler to maintain and troubleshoot.

  DECLARE @files TABLE (
ID Integer IDENTITY,
Filename Varchar(100));

  DECLARE @file_names Varchar(MAX) = '';

  INSERT INTO @files(Filename)
EXECUTE xp_cmdshell 'dir S:\Reports /b';

  SELECT @file_names = @file_names + ';S:\Reports\' + Filename
FROM @files
WHERE NOT Filename = 'BACKUP'
    AND Filename IS NOT NULL;

  SELECT @file_names = STUFF(@file_names, 1, 1, '');

  The only thing you would have left to do is populate your dbo.SUM_MAIL_REPORTS table.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• lyle.nielsen 31819

  SSC Veteran

  Points: 234

  More actions

  May 18, 2017 at 9:09 am

  #1943128

  Ed Wagner - Thursday, May 18, 2017 8:38 AM

  lyle.nielsen 31819 - Thursday, May 18, 2017 7:24 AM

  Joe Torre - Wednesday, May 17, 2017 3:19 PM

  Is S: a mapped drive or a local drive? Is this run as a SQL Agent Job? If so, does the account it runs under have permissions on that path? How are you getting the file list?

  The S: is a local drive.  I ran both of these manually. I have not yet set up the job to run from SQL Agent.  I am getting the file list as this:
  declare @files table (ID int IDENTITY, FileName varchar(100))
  DECLARE @id_hold nvarchar(250),
     @id_max nvarchar(250),
     @file_names nvarchar(2000),
     @file_dir nvarchar(250),
     @first_file nvarchar(250)

  select @file_dir = 'S:\Reports\'

  insert into @files execute xp_cmdshell 'dir S:\Reports /b'
  insert into [dbo].[SUM_MAIL_REPORTS]
  ([FILE_ID],[FILE_NAME],[CRDate])
  select ID, FileName ,getdate() from @files

  delete [dbo].[SUM_MAIL_REPORTS]
  WHERE FILE_NAME = 'Backups'
    OR FILE_NAME is null

  select * from [dbo].[SUM_MAIL_REPORTS]

  Select @id_hold = min(FILE_NAME), @id_max = max(FILE_NAME)
    from [dbo].[SUM_MAIL_REPORTS]
    WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)

  select @first_file = 'YES'

  DECLARE db_cursor CURSOR FOR
   SELECT FILE_NAME
   FROM [MonitorDb].[dbo].[SUM_MAIL_REPORTS]
   WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)
   ORDER BY FILE_NAME

  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @id_hold

  WHILE @@FETCH_STATUS = 0
   BEGIN
    IF @first_file = 'YES'
       BEGIN
     select @file_names = ''''+@file_dir+@id_hold
           SELECT @first_file='NO'
       END
    ELSE
     select @file_names = @file_names + ';'+@file_dir+@id_hold
    select @file_names
      FETCH NEXT FROM db_cursor INTO @id_hold
   END
  -- finish the string

  CLOSE db_cursor
  DEALLOCATE db_cursor

  select @file_names = @file_names + ''''

  --select @file_names

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMailProfile'
    , @recipients = 'lyle.nielsen@hsc.utah.edu'
    , @subject  = 'test attachment'
    , @body   = 'An attachment'
    , @file_attachments=@file_names

  select @file_names

  Would something like this work for you?  It would be a whole lot simpler to maintain and troubleshoot.

  DECLARE @files TABLE (
ID Integer IDENTITY,
Filename Varchar(100));

  DECLARE @file_names Varchar(MAX) = '';

  INSERT INTO @files(Filename)
EXECUTE xp_cmdshell 'dir S:\Reports /b';

  SELECT @file_names = @file_names + ';S:\Reports\' + Filename
FROM @files
WHERE NOT Filename = 'BACKUP'
    AND Filename IS NOT NULL;

  SELECT @file_names = STUFF(@file_names, 1, 1, '');

  The only thing you would have left to do is populate your dbo.SUM_MAIL_REPORTS table.

  that works great!!!!! Thank You!!

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 18, 2017 at 1:53 pm

  #1943203

  lyle.nielsen 31819 - Thursday, May 18, 2017 9:09 AM

  Ed Wagner - Thursday, May 18, 2017 8:38 AM

  lyle.nielsen 31819 - Thursday, May 18, 2017 7:24 AM

  Joe Torre - Wednesday, May 17, 2017 3:19 PM

  Is S: a mapped drive or a local drive? Is this run as a SQL Agent Job? If so, does the account it runs under have permissions on that path? How are you getting the file list?

  The S: is a local drive.  I ran both of these manually. I have not yet set up the job to run from SQL Agent.  I am getting the file list as this:
  declare @files table (ID int IDENTITY, FileName varchar(100))
  DECLARE @id_hold nvarchar(250),
     @id_max nvarchar(250),
     @file_names nvarchar(2000),
     @file_dir nvarchar(250),
     @first_file nvarchar(250)

  select @file_dir = 'S:\Reports\'

  insert into @files execute xp_cmdshell 'dir S:\Reports /b'
  insert into [dbo].[SUM_MAIL_REPORTS]
  ([FILE_ID],[FILE_NAME],[CRDate])
  select ID, FileName ,getdate() from @files

  delete [dbo].[SUM_MAIL_REPORTS]
  WHERE FILE_NAME = 'Backups'
    OR FILE_NAME is null

  select * from [dbo].[SUM_MAIL_REPORTS]

  Select @id_hold = min(FILE_NAME), @id_max = max(FILE_NAME)
    from [dbo].[SUM_MAIL_REPORTS]
    WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)

  select @first_file = 'YES'

  DECLARE db_cursor CURSOR FOR
   SELECT FILE_NAME
   FROM [MonitorDb].[dbo].[SUM_MAIL_REPORTS]
   WHERE CAST(CRDate AS DATE) > CAST((GETDATE()-2) AS DATE)
   ORDER BY FILE_NAME

  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @id_hold

  WHILE @@FETCH_STATUS = 0
   BEGIN
    IF @first_file = 'YES'
       BEGIN
     select @file_names = ''''+@file_dir+@id_hold
           SELECT @first_file='NO'
       END
    ELSE
     select @file_names = @file_names + ';'+@file_dir+@id_hold
    select @file_names
      FETCH NEXT FROM db_cursor INTO @id_hold
   END
  -- finish the string

  CLOSE db_cursor
  DEALLOCATE db_cursor

  select @file_names = @file_names + ''''

  --select @file_names

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMailProfile'
    , @recipients = 'lyle.nielsen@hsc.utah.edu'
    , @subject  = 'test attachment'
    , @body   = 'An attachment'
    , @file_attachments=@file_names

  select @file_names

  Would something like this work for you?  It would be a whole lot simpler to maintain and troubleshoot.

  DECLARE @files TABLE (
ID Integer IDENTITY,
Filename Varchar(100));

  DECLARE @file_names Varchar(MAX) = '';

  INSERT INTO @files(Filename)
EXECUTE xp_cmdshell 'dir S:\Reports /b';

  SELECT @file_names = @file_names + ';S:\Reports\' + Filename
FROM @files
WHERE NOT Filename = 'BACKUP'
    AND Filename IS NOT NULL;

  SELECT @file_names = STUFF(@file_names, 1, 1, '');

  The only thing you would have left to do is populate your dbo.SUM_MAIL_REPORTS table.

  that works great!!!!! Thank You!!

  You're most welcome.  Thanks for the feedback.

  Shifting back to your original problem for a moment, does the statement work now?  If not, did you check the file system permissions?

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions