Executing an sp on another server

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)

Executing an sp on another server

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-03-25 : 03:26:57

I want a trigger on one server to execute a stored procedure on another server. How do I do this?
MS SQL 2000.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-25 : 06:42:42

define the remote server as a linked server or remote server (enterprise manager - security or sp_addlinkedserver/sp_addremoteserver) and call it using the the fully qualified name

exec svrname.dbname.owner.spname

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

Scott
Posting Yak Master

145 Posts

Posted - 2002-03-25 : 07:56:47

I am trying to create the following trigger which passes parameters to the created sp_SMTPMail (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=7824 - Article: Sending SMTP Mail using a Stored Procedure)

Obviously the code below does not work, any ideas on how to fix?

CREATE TRIGGER TriggerName ON [dbo].[UserTable]
FOR INSERT
AS
exec ServerName.DatabaseName.dbo.sp_SMTPMail
@SenderAddress='info@Domain',
@RecipientAddress = dbo.UserTable.email,
@Subject='Your WebApp login details',
@body='Your WebApp account has been created.' + 'Your username is: ' + dbo.UserTable.Username + ' and your password is: ' + dbo.UserTable.[Password] + 'Please note the following office hours support number: ****'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-25 : 08:02:33

You need to have a SELECT statement in there somewhere:

CREATE TRIGGER TriggerName ON [dbo].[UserTable]
FOR INSERT
AS
declare @name varchar(50), @password varchar(50)
SELECT @name=UserName, @password=Password FROM inserted
WHERE UserID=@@IDENTITY
exec ServerName.DatabaseName.dbo.sp_SMTPMail
@SenderAddress='info@Domain',
@RecipientAddress = dbo.UserTable.email,
@Subject='Your WebApp login details',
@body='Your WebApp account has been created.' + 'Your username is: ' + @name + ' and your password is: ' + @password + 'Please note the following office hours support number: ****'

I'm assuming an identity column named UserID is your primary key column, you need to modify the WHERE clause to fit. You can remove the WHERE clause, but this trigger won't work correctly if more than one row is inserted.

Look in Books Online for more info on the inserted and deleted pseudo-tables.

Scott
Posting Yak Master

145 Posts

Posted - 2002-03-25 : 08:59:13

Thanks, also it is interesting to note that you can't concatenate the body parameter had to do this to get it to work.

CREATE TRIGGER TriggerName ON [dbo].[UserTable]
FOR INSERT
AS
declare @name varchar(50), @password varchar(50), @email varchar(50), @Bodytxt varchar(255)
SELECT @name=UserName, @password=Password, @email=email FROM inserted
WHERE UserID=@@IDENTITY
select @Bodytxt='Your WebApp account has been created.' + 'Your username is: ' + @name + ' and your password is: ' + @password + 'Please note the following office hours support number: ****'
exec ServerName.DatabaseName.dbo.sp_SMTPMail
@SenderAddress='info@Domain',
@RecipientAddress = dbo.UserTable.email,
@Subject='Your WebApp login details',
@body=@Bodytxt

2k2er
Starting Member

20 Posts

Posted - 2011-07-18 : 12:04:36

Hi Team,

I have also tried using the sp_SMTPMail stored procedure which i got from net.

This is the content.

/*
--=============================================================================================
--== Name - sp_SMTPMail
--== Purpose - Send an email using an SMTP Mailer COM Component
--== Input Parameters - @SenderName, @SenderAddress, @RecipientName, @RecipientAddress
--== @Subject, @Body, @MailServer
--==
--==
--== Usage - exec sp_SMTPMail @SenderName='Damian', @SenderAddress='damian@bdussy.com',
--== @RecipientName = 'Someone', @RecipientAddress = 'someone@someplace.com',
--== @Subject='SQL Test', @body='Hello, this is a test email from SQL Server'
--==
--== Created Date - Thursday, July 19, 2001
--== Created by - Damian Maclennan
--== merkin@sqlteam.com
--== www.bdussy.com/damian
--==
--== Disclaimer - This procedure has been created for demonstration purposes. It has not
--== been created for production purposes and I hold no liability for any
--== unwanted effects of running it.
--=============================================================================================
*/
Create Procedure sp_SMTPMail

@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = 'mySMTPserver'

AS

SET nocount on

declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress

EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

EXEC sp_OADestroy @oMail
END

SET nocount off

GO

This got created successfully on my master database of SQL 2000 DB.

I then tried to execute it for testing purpose..

exec sp_SMTPMail @SenderName='2k2er', @SenderAddress='2k2er@mycompany.com',
@RecipientName = 'SME', @RecipientAddress = '2k2er@mycompany.com',
@Subject='SQL Test', @body='Hello, this is a test email from SQL Server'

When i execute this, i got command run successfully but i am not getting any emails. It is confirmed that the SMTP server i am using as a parameter is correct.

Can someone help me in this?

2k2er

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-18 : 14:26:34

9 years, 4 months. New record?

Make sure SMTP is configured to allow relay from your server. Check the drop and badmail folders.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-18 : 15:18:00