sending output of a proc using xp_sendmail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sending output of a proc using xp_sendmail

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-03-27 : 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

11752 Posts

Posted - 2008-03-27 : 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

38200 Posts

Posted - 2008-03-27 : 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

407 Posts

Posted - 2008-03-27 : 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

11752 Posts

Posted - 2008-03-27 : 17:08:17

and my suggestion doesn't work for you why?

_______________________________________________
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

407 Posts

Posted - 2008-03-27 : 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

11752 Posts

Posted - 2008-03-27 : 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

407 Posts

Posted - 2008-03-31 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources