xp_sendmail question - SQL Server Forums

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)

xp_sendmail question

Author

Topic

kmistic
Starting Member

40 Posts

Posted - 2004-09-22 : 14:58:22

I need one email to include two queries. Is this possible?

Here is my code, of course it won't work, just trying to show what i am trying to accomplish...

EXEC master.dbo.xp_sendmail @recipients = 'kmistic',
@query = 'SELECT * from fimast.dbo.Deals',
@query = 'SELECT * from fimast.dbo.Units',
@subject = 'Deals not in accounting',
@message = 'This is an automatic message from our reporting system.
Do not reply to this email address.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 15:06:21

Just put the two queries in a stored procedure and execute the stored procedure in @query.

Tara

kmistic
Starting Member

40 Posts

Posted - 2004-09-22 : 15:23:23

Im sorry, the two objects Deals and Units are actually VIEWS. Is this going to make a difference?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 15:25:55

No it does not. Just put them in a stored procedure.

CREATE PROC usp_SomeProc
AS

SELECT Column1, Column2
FROM Table1

SELECT Column1, Column2
FROM Table2

SELECT Column1, Column2
FROM View1

GO

Then @query = 'EXEC dbo.usp_SomeProc'

Tara

kmistic
Starting Member

40 Posts

Posted - 2004-09-22 : 15:46:58

That works great. thanks

next question. When i run proc in QA everything is nice and lined up, but when i run the sendmail proc the email looks like this:

BMW 0 1 1 0 2 1 1 0 6
Chevrolet Brandon 0 17 4 4 10 3 3 12 53
Chevrolet Tarpon Springs 0 5 4 3 9 4 0 5 31
New Port Richey 0 3 5 2 4 0 0 0 14
Nissan 0 8 6 1 6 1 4 5 31
Florida 0 5 8 3 9 1 4 13 43
Kennedy
0 0

Why is the data going all over the place?

kmistic
Starting Member

40 Posts

Posted - 2004-09-22 : 15:48:06

the board must align things to the left, the above data should be randomly scattered all over the place... :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 15:50:15

You can try formatting your data using @width and @separator in xp_sendmail.

Tara

kmistic
Starting Member

40 Posts

Posted - 2004-09-22 : 16:31:24

tried that, not working :( Is there a way to format the actual column size? I can't seem to find one.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-22 : 16:32:43

No there isn't. You'll need to use a different e-mail solution if you require this kind of formatting. SQL Mail is rather limited. We use it for database administration tasks only like e-mailing the DBAs of failed jobs.

Tara

kmistic
Starting Member

40 Posts

Posted - 2004-09-22 : 16:33:55

oh well, Thanks so much for a your help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources