February 20, 2004 at 2:09 pm
I've been knocking my head about this for the last couple of days--
Lets say I have a table with sql in one of the columns, so that I use a cursor and get the equivalent of
Declare @sql nvarchar 200
Declare @parmindex nvarchar(20)
set @sql='select cast(Client as char(30)) from Clients where storeid=@sid'
set @parmindex='@sid int'
execute sp_executesql @sql, @parmindex, @sid=34
Assume that @sql will returns 0 or more rows of varchar datatype. I need to take the output of sql and put it into a table so I can use sqlmail to send the completed message; but
execute @result=sp_executesql -- Won't work; @result=0 on success
execute @result=@sql -- Won't work. This form *requires* @sql be a sproc
I guess I could create an sproc on-the-fly and delete it later, but that sounds silly to me. I'm just about to give up and do it in VC# using sp_cmdshell, and have the VC# program do the emailing, but I'd much rather keep it all in the database. Unfortunately, I can't just use xp_sendmail's query feature because I have to strap on headers and footers (or use CR b/c the email must be in plain text)...
TIA,
Thor Johnson
February 20, 2004 at 2:56 pm
You can use xp_sendmail to send mail that inclues the results of a query. ie:
Declare @sql nvarchar(200)
Declare @parmindex nvarchar(20)
Declare @sid varchar(3)
set @sid = 34
set @sql='select cast(Client as char(30)) from Clients where storeid=' + @sid
EXEC xp_sendmail @recipients = 'robertk',
@query = @sql,
@subject = 'SQL Server Report',
@message = 'The results of my query:',
@attach_results = 'TRUE', @width = 250
Francis
February 23, 2004 at 9:45 am
Yes, but I need to add a footer to the message, as well as customize the subject (Attn: Mr Brown). I'm trying to generalize it so I won't have to force the template too much.
February 23, 2004 at 9:52 am
Use a table to store the message and then include that? Could us a
create table msg (spid int, mymsg varchar( 100))
and put a row in for each line of the message. Then insert your template and include the select mymsg from msg where spid = @@spid in xp_sendmail.
February 23, 2004 at 11:37 am
Use a table to store the message and then include that? Could us a
create table msg (spid int, mymsg varchar( 100))
Actually, that's what I'm doing, but I need to do something like (pardon my semi-sql):
Open Cursor on template_table
while GetRows into statement, issql
BEGIN
If issql
insert into msg_table EXEC(statement) -- Was a query. Put contents in
else
insert into msg_table statement -- Just want the template text
END
Close cursor, yadda yadda
xp_sendmail select * from msg_table
truncate msg_table.
I'm having problems with the insert into msg table Exec(statement) part. Statement is usually something like "select name from contacts where contactid=?" (and I was planning to pass in the ContactID using xp_executesql, but...), or something like "select alarmtime, message from master_alarms where siteid=?".
February 24, 2004 at 12:01 pm
Ok. I think I muddled through it. This doesn't sound optimal, or nice, but it works.
I was trying to do exec @result = sp_executesql N'exec xp_cmdshell ''dir'''
But that would always return 0 (i.e. "It worked"). Evidently you must use temporary tables (since MSSQL2k doesn't do sprocs into table vars), like so:
create table #tmp (ret as varchar(5000))
insert into #tmp exec sp_executesql N'select getdate()' -- put into #tmp
-- If I *need* it to be in a variable, I just need to do a select
select @frip=ret from #tmp.
Bleh. Looks like an awful lot of messing around just to get "1 or more string results" from "an arbitrary sql statement," and I thought it would be detrimental to have all these temporary tables flying about. But it does work.
Does anyone see anything amiss or that I could do better?
TIA,
Thor Johnson
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply