| Author |
 Topic  |
|
|
igorblackbelt
Constraint Violating Yak Guru
USA
407 Posts |
 Posted - 03/27/2008 : 16:33:50
|
The scenario:
An SP that will create a temp table (#table) with users with access to an specific app.
One of the last steps on the SP is: SELECT * FROM #Results
Is it possible to use that output from the SP and send that in a .csv file to a list of people using xp_sendmail?
I was told that modifying the SP is not an option, otherwise I could add the xp_sendmail at the end of the procedure. |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 03/27/2008 : 16:39:26
|
create table #Temp_Results (columns of the same type as those that SP returns)
insert into #Temp_Results
exec yourSP
do what you want with data from #Temp_Results here
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/27/2008 : 16:50:03
|
Can't you specify EXEC StoredProcName for @query in xp_sendmail? I seem to recall using stored procedures with SQL Mail, but it's been a while as we're on SQL 2005 and using Database Mail instead.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/ |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
USA
407 Posts |
Posted - 03/27/2008 : 17:01:02
|
Tara,
Yes I can, that's a good thing that I wasn't aware of, I thought you could only pass select results to the @query parameter.
But it didn't really work for my purpose, the SProc has tons of print statements and all of that is getting appended to my .csv file, which I can't have, I only need the results from the #Resutls table which is created and populated towards the end of the SP. I'm going to have to either modify the proc or create a whole new one. |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
igorblackbelt
Constraint Violating Yak Guru
USA
407 Posts |
Posted - 03/27/2008 : 17:14:36
|
| No, it didn't work because of the tons of print statements that I have on the SP, it would work perfectly if I didn't have those print statements... Makes sense? |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 03/27/2008 : 17:33:43
|
print statements are irrelevant:
create proc spTest
as
print 'begin proc'
select 1 as c1, 2 as c2, 'ffdsgds' as c3
print 'end proc'
go
exec spTest
create table #temp(c1 int, c2 int, c3 varchar(10))
insert into #temp
exec spTest
-- you put this query in your xp_sendmail
select * from #temp
drop table #temp
drop proc spTest
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
USA
407 Posts |
Posted - 03/31/2008 : 14:23:47
|
I created a new SP, changed the #table to be an ##table and used the table on the xp_sendmail.
Spirit,
I made a note of your solution, which I'll try next time. |
|
|
| |
Topic |
|
sending output of a proc using xp_sendmail
