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'
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 6 (of 6 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