May 17, 2017 at 8:11 am
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.
May 17, 2017 at 3:20 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?
May 17, 2017 at 4:25 pm
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.
May 18, 2017 at 7:24 am
Joe Torre - Wednesday, May 17, 2017 3:19 PMIs 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
May 18, 2017 at 8:38 am
lyle.nielsen 31819 - Thursday, May 18, 2017 7:24 AMJoe Torre - Wednesday, May 17, 2017 3:19 PMIs 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 @filesdelete [dbo].[SUM_MAIL_REPORTS]
WHERE FILE_NAME = 'Backups'
OR FILE_NAME is nullselect * 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_NAMEOPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id_holdWHILE @@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 stringCLOSE db_cursor
DEALLOCATE db_cursorselect @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_namesselect @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.
May 18, 2017 at 9:09 am
Ed Wagner - Thursday, May 18, 2017 8:38 AMlyle.nielsen 31819 - Thursday, May 18, 2017 7:24 AMJoe Torre - Wednesday, May 17, 2017 3:19 PMIs 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 @filesdelete [dbo].[SUM_MAIL_REPORTS]
WHERE FILE_NAME = 'Backups'
OR FILE_NAME is nullselect * 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_NAMEOPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id_holdWHILE @@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 stringCLOSE db_cursor
DEALLOCATE db_cursorselect @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_namesselect @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!!
May 18, 2017 at 1:53 pm
lyle.nielsen 31819 - Thursday, May 18, 2017 9:09 AMEd Wagner - Thursday, May 18, 2017 8:38 AMlyle.nielsen 31819 - Thursday, May 18, 2017 7:24 AMJoe Torre - Wednesday, May 17, 2017 3:19 PMIs 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 @filesdelete [dbo].[SUM_MAIL_REPORTS]
WHERE FILE_NAME = 'Backups'
OR FILE_NAME is nullselect * 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_NAMEOPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id_holdWHILE @@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 stringCLOSE db_cursor
DEALLOCATE db_cursorselect @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_namesselect @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?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy