December 12, 2018 at 7:01 am
Hi All,
I am wondering if anyone can help me. I have a stored procedure that executes the sp_send_dbmail. I am wanting this to run some sql code and send the output as a file.
I can get this to work with a simple 'SET @querytext = 'SELECT * From xxx.dbo.vw_xxx' and pass @querytext into the sp's @Query parameter.
However, if I try a more complex string like 'SET @querytext = stuff((SELECT SelectString2 + '' FROM xxx.dbo.vw_xxx FOR XML PATH ('')),1,0,'')' it fails to send the email.
The string runs in sql and does return what I want, it just wont run in the sp_send_dbmail.
Any advice?
December 12, 2018 at 7:15 am
i.campbell 84312 - Wednesday, December 12, 2018 7:01 AMHi All,
I am wondering if anyone can help me. I have a stored procedure that executes the sp_send_dbmail. I am wanting this to run some sql code and send the output as a file.
I can get this to work with a simple 'SET @querytext = 'SELECT * From xxx.dbo.vw_xxx' and pass @querytext into the sp's @Query parameter.
However, if I try a more complex string like 'SET @querytext = stuff((SELECT SelectString2 + '' FROM xxx.dbo.vw_xxx FOR XML PATH ('')),1,0,'')' it fails to send the email.
The string runs in sql and does return what I want, it just wont run in the sp_send_dbmail.Any advice?
What does this print in SSMS?declare @querytext nvarchar(MAX)
SET @querytext = stuff((SELECT SelectString2 + '' FROM xxx.dbo.vw_xxx FOR XML PATH ('')),1,0,'')
print @querytext
Does it print a SQL statement you can run in SSMS?
December 12, 2018 at 8:15 am
the whole string is as follows:
set @querytext = replace('BEGIN:VCALENDAR~~VERSION:2.0~~METHOD: PUBLISH~~' + stuff((SELECT SelectString2 + '' FROM triage.dbo.vw_ExportLists_ics_2 FOR XML PATH('')),1,0,'') + 'END:VCALENDAR','~~', char(13));
this prints in SSMS as:
BEGIN:VCALENDAR
VERSION:2.0
METHOD: PUBLISH
BEGIN:VEVENT
CLASS: PUBLIC
DESCRIPTION:Imported from x
SUMMARY:x Lists
DTSTART:20181214T084500
DTEND:20181214T123000
LOCATION:xxx
END:VEVENT
BEGIN:VEVENT
CLASS: PUBLIC
DESCRIPTION:Imported from x
SUMMARY:y Lists
DTSTART:20181219T084500
DTEND:20181219T123000
LOCATION:yyy
END:VEVENT
END:VCALENDAR
which is what I need to save to a .ics file to import into outlook. if I copy the print into notepad and save as .ics, the file imports to outlook as expected.
December 12, 2018 at 8:28 am
i.campbell 84312 - Wednesday, December 12, 2018 8:15 AMthe whole string is as follows:
set @querytext = replace('BEGIN:VCALENDAR~~VERSION:2.0~~METHOD: PUBLISH~~' + stuff((SELECT SelectString2 + '' FROM triage.dbo.vw_ExportLists_ics_2 FOR XML PATH('')),1,0,'') + 'END:VCALENDAR','~~', char(13));
this prints in SSMS as:
BEGIN:VCALENDAR
VERSION:2.0
METHOD: PUBLISH
BEGIN:VEVENT
CLASS: PUBLIC
DESCRIPTION:Imported from x
SUMMARY:x Lists
DTSTART:20181214T084500
DTEND:20181214T123000
LOCATION:xxx
END:VEVENT
BEGIN:VEVENT
CLASS: PUBLIC
DESCRIPTION:Imported from x
SUMMARY:y Lists
DTSTART:20181219T084500
DTEND:20181219T123000
LOCATION:yyy
END:VEVENT
END:VCALENDARwhich is what I need to save to a .ics file to import into outlook. if I copy the print into notepad and save as .ics, the file imports to outlook as expected.
You need to put a valid SQL statement in @query. That's why your initial SELECT statement works ok.
If you try to run:replace('BEGIN:VCALENDAR~~VERSION:2.0~~METHOD: PUBLISH~~' + stuff((SELECT SelectString2 + '' FROM triage.dbo.vw_ExportLists_ics_2 FOR XML PATH('')),1,0,'') + 'END:VCALENDAR','~~', char(13));
in SSMS it will raise an error, so also raise an error if you set the @query to this.
I think you will need to replace it with this:set @querytext = 'SELECT replace(''BEGIN:VCALENDAR~~VERSION:2.0~~METHOD: PUBLISH~~''
+ stuff((SELECT SelectString2
+ '''' FROM triage.dbo.vw_ExportLists_ics_2 FOR XML PATH('''')),1,0,'''')
+ ''END:VCALENDAR'',''~~'', char(13));'
i.e. you need a SELECT and you need to double up all the quotes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply