Carlo Montemayor
asked on
Send Invitation email using Sql Server
Hello guys,
I am having a problem to get this issue.
I would like to add the @location variable on the query of ics using the sql server.
here is my code:
Thank you!
I am having a problem to get this issue.
I would like to add the @location variable on the query of ics using the sql server.
here is my code:
Thank you!
DECLARE @location NVARCHAR(MAX)
SET @location = 'Philippines'
EXEC msdb.dbo.sp_send_dbmail
@recipients ='sample@sample.com'
,@profile_name='Golegalcase'
,@subject = 'invite'
,@body = 'invitation from Me'
, @query =
'
--
SET NOCOUNT ON;
--
SELECT ''BEGIN:VCALENDAR'' + char(13) + char(10)
+ ''PRODID:SomeIDDoesitmatter'' + char(13) + char(10)
+ ''VERSION:2.0'' + char(13) + char(10)
+ ''CALSCALE:GREGORIAN'' + char(13) + char(10)
+ ''METHOD:REQUEST'' + char(13) + char(10)
+ ''BEGIN:VEVENT'' + char(13) + char(10)
+ ''DTSTART:20170419T040000Z'' + char(13) + char(10)
+ ''DTEND:20170419T050000Z''+ char(13) + char(10)
+ ''DTSTAMP:20170419T155247Z'' + char(13) + char(10)
+ ''ORGANIZER:MAILTO:sample@sample.com'' + char(13) + char(10)
+ ''UID:ABCD1234'' + char(13) + char(10)
+ ''ATTENDEE;CUTYPE=INDIVIDUAL;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=TRUE;CN=sample@sample.com;X-NUM-GUESTS=0:mailto:sample@sample.com'' + char(13) + char(10)
+ ''CREATED:20170419T023104Z'' + char(13) + char(10)
+ ''DESCRIPTION:This is a description''+ char(13) + char(10)
+ ''TRANSP:OPAQUE'' + char(13) + char(10)
+ ''LOCATION: @Location'' -- This is the query that I'd like to pass the variable @Location and also, the DTSTART and DTEND above.
+ char(13) + char(10)
+ ''SEQUENCE:0'' + char(13) + char(10)
+ ''STATUS:CONFIRMED'' + char(13) + char(10)
+ ''SUMMARY:test'' + char(13) + char(10)
+ ''END:VEVENT'' + char(13) + char(10)
+ ''END:VCALENDAR''
'
, @attach_query_result_as_file = 1
, @query_result_header = 0
, @query_result_separator = 'char(13) + char(10)'
, @exclude_query_output = 1
, @query_attachment_filename = 'invite.ics'
ASKER
Well, you need to get the vCalendar formatting right.
Maybe the problem is that there are two location entries (haven't seen that while writing it, maybe the leading spaces are a problem.
Maybe the problem is that there are two location entries (haven't seen that while writing it, maybe the leading spaces are a problem.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Then: clean up your code, T-SQL allows the usage of multi-line text.
And insert your location line with a placeholder for the actual value. E.g.
Open in new window