April 11, 2019 at 6:39 pm
I’m trying to attach a file that is generated from an SSIS package and when created has the current date added to it.
Example: BH_2019-04-11.xlsx
I keep getting a syntax error when I add in the code to account for the date stamp.
Example: BH_' + 'CONVERT(VARCHAR(12), GETDATE(), 107' + '.xlsx
Error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517
Query execution failed: Msg 102, Level 15, State 1, Server HIDSCMSQL003, Line 1
Incorrect syntax near 'files'.
This code attaches files without the time stamp so I’m hoping someone can help me figure out how to get this to work.
Full code:
DECLARE @body VARCHAR(1000);
DECLARE @bodyQuery VARCHAR(1000);
DECLARE @file_attachments VARCHAR(MAX);
DECLARE @Attachments VARCHAR(MAX);
/* The table name needs to be fully qualified */
SET @bodyQuery = 'See attached files'
SET @Attachments = 'E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_' + 'CONVERT(VARCHAR(12), GETDATE(), 107' + '.xlsx) '
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'QM_Mail',
@recipients = '##########',
@from_address= '##########',
--@copy_recipients ='##########',
@subject = 'Provider Notification - Files past initial credentialing',
@body = '',
@query = @bodyQuery,
@execute_query_database ='msdb',
@file_attachments = @Attachments
Thanks
April 11, 2019 at 6:45 pm
Well what happens when you run this,
DECLARE @Attachments VARCHAR(MAX);
SET @Attachments = 'E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_' + 'CONVERT(VARCHAR(12), GETDATE(), 107' + '.xlsx) '
SELECT @Attachments
April 11, 2019 at 7:44 pm
It's reading the code as code not executing the date stamp.
(No column name)
E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_CONVERT(VARCHAR(12), GETDATE(), 107.xlsx)
April 11, 2019 at 7:54 pm
When I run this code
DECLARE @Attachments VARCHAR(MAX);
SET @Attachments = 'E:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Scripts\QMfiles\MedQuest\BH_' + CONVERT(VARCHAR(12), GETDATE(), 107 + '.xlsx)'
SELECT @Attachments
I get a syntax error at the select statement.
Msg 156, Level 15, State 1, Procedure MSOW_AttachEmail_TEST, Line 8
Incorrect syntax near the keyword 'SELECT'.
April 11, 2019 at 8:04 pm
It's just missing the closing parenthesis after 107 on formatting the date - should be GETDATE(), 107) + '.xlsx)'
Sue
Viewing 5 posts - 1 through 5 (of 5 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