Inserted Tables With 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

SQL Server Development (2000)

Inserted Tables With XP_Sendmail

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-31 : 22:41:00

Raj writes "Hi

Is it possible to query an inserted table from the xp_sendmail extended stored procedure? For example,

xp_sendmail 'rajantonyv', @message='Testing',
@Subject='Testing',
@query='Select * from tempdb.dbo.inserted'

It hasn't worked for me and I am thinking I must have missed something. Thanks and lookiing forward to your reply.

Raj Antony V"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-31 : 23:27:59

nope - it's not available in another batch let alone connection.

you can create a global temp table

begin tran
select * from restbl (tablockx,holdlock)
select * into ##tbl from inserted
exec master..xp_sendmail ... from ##inserted
commit tran

note the resource lock to single thread use of table.

better to have a perm table for the trigger

delete mytbl where spid = @@spid
insert mytbl @@spid, * from inserted
xp_sendmail ....... from mytbl where spid = @@spid
delete mytbl where spid = @@spid

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources