SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
create function for dbmail in MSSQL

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
create function for dbmail in MSSQL
Author Message
Anthony Smith



Joined: 30 Aug 2005
Posts: 6

Post create function for dbmail in MSSQL Reply with quote

I have tried to create a UD function as per the help files and also have tried as per an earlier posting on this forum
However I get an error invalid use of the insert within a functions.

Basically I am trying to use a simple select statemet to pull email addresses out or a table and then email them.

Any help on a basic script would be useful

Thanks

Tue Aug 30, 2005 9:19 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: create function for dbmail in MSSQL Reply with quote

Please specify your database system type and version and post your UDF script. I can then help you to make the code working.

: I have tried to create a UD function as per the help files and also have
: tried as per an earlier posting on this forum
: However I get an error invalid use of the insert within a functions.

: Basically I am trying to use a simple select statemet to pull email addresses
: out or a table and then email them.

: Any help on a basic script would be useful

: Thanks

Tue Aug 30, 2005 10:51 am View user's profile Send private message
Anthony Smith



Joined: 30 Aug 2005
Posts: 6

Post Re: create function for dbmail in MSSQL Reply with quote

: Please specify your database system type and version and post your UDF
: script. I can then help you to make the code working.

Hi I am using MS SQL server on win 2003 and have been trying to run this through query analizer using the the dbase Londonolink which is where the user function is saved

Here is the fundction that has been created
CREATE FUNCTION mySendMail (@recipient VARCHAR (40),
@subject VARCHAR (255),
@message VARCHAR (8000) )

RETURNS INT
AS
BEGIN
DECLARE @ret INT
EXEC @ret = master.dbmail.SendMAil @recipient, @subject, @message, 'accounts@londonolink.net'
RETURN @ret
END

This is a the script I am trying to use where billing_email is a fiedl that contans an email address

select dbo.mySendMail
(billing_email,
'New invoice',
'You have a new invoice')
From customer

--Where billing_email NULL

When the above line is not commeted out I get no error but no email is sent

However when I comment out I get the error below

Server: Msg 557, Level 16, State 2, Procedure mySendMail, Line 9
Only functions and extended stored procedures can be executed from within a function.

Hope this helps


Wed Aug 31, 2005 3:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: create function for dbmail in MSSQL Reply with quote

In this version of SQL Server as the error says "Only system functions and extended stored procedures can be executed from within a function."

As a workaround you can use an INSERT to insert directly into dbmail.pipe table

INSERT INTO master.dbmail.[pipe] (recipients, subject, message)
SELECT billing_email, 'New invoice', 'You have a new invoice')
FROM customer

Hope this helps

: Hi I am using MS SQL server on win 2003 and have been trying to run this
: through query analizer using the the dbase Londonolink which is where the
: user function is saved

: Here is the fundction that has been created
: CREATE FUNCTION mySendMail (@recipient VARCHAR (40),
: @subject VARCHAR (255),
: @message VARCHAR (8000) )

: RETURNS INT
: AS
: BEGIN
: DECLARE @ret INT
: EXEC @ret = master.dbmail.SendMAil @recipient, @subject, @message,
: 'accounts@londonolink.net'
: RETURN @ret
: END

: This is a the script I am trying to use where billing_email is a fiedl that
: contans an email address

: select dbo.mySendMail
: (billing_email,
: 'New invoice',
: 'You have a new invoice')
: From customer

: --Where billing_email NULL

: When the above line is not commeted out I get no error but no email is sent

: However when I comment out I get the error below

: Server: Msg 557, Level 16, State 2, Procedure mySendMail, Line 9
: Only functions and extended stored procedures can be executed from within a
: function.

: Hope this helps

Wed Aug 31, 2005 10:59 am View user's profile Send private message
Anthony Smith



Joined: 30 Aug 2005
Posts: 6

Post Re: create function for dbmail in MSSQL Reply with quote

: In this version of SQL Server as the error says "Only system functions
: and extended stored procedures can be executed from within a
: function."

: As a workaround you can use an INSERT to insert directly into dbmail.pipe
: table

: INSERT INTO master.dbmail.[pipe] (recipients, subject, message)
: SELECT billing_email, 'New invoice', 'You have a new invoice')
: FROM customer

: Hope this helps

Can I still select other fields from the customer dbase ( as per your example in your manaul)

ie SELECT billing_email, 'New Invoice', 'Please login with your user id + customer_id and password + password'

Would this work or am I missing something

Wed Aug 31, 2005 11:35 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: create function for dbmail in MSSQL Reply with quote

Sure, you can use any valid expressions and fields.

: Can I still select other fields from the customer dbase ( as per your example
: in your manaul)

: ie SELECT billing_email, 'New Invoice', 'Please login with your user id +
: customer_id and password + password'

: Would this work or am I missing something

Wed Aug 31, 2005 12:02 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: create function for dbmail in MSSQL Reply with quote

Replace

'Please login with your user id + customer_id and password + password'

with

'Please login with your user id ' + customer_id + ' and password ' + password

: Sure, you can use any valid expressions and fields.

Wed Aug 31, 2005 12:04 pm View user's profile Send private message
Anthony Smith



Joined: 30 Aug 2005
Posts: 6

Post Re: create function for dbmail in MSSQL Reply with quote

: Replace

: 'Please login with your user id + customer_id and password + password'

: with

: 'Please login with your user id ' + customer_id + ' and password ' + password

I must be missing somethng on the syntax here as ms sql query analizer returns an error using the script above
syntax error converting the varchar value 'Please login with your user id ' to a column of data typ int

I have no problem sending mail using the earlier script

Thu Sep 01, 2005 3:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: create function for dbmail in MSSQL Reply with quote

if customer_id is a numeric field then you need to convert it to a varchar first, for example

'Please login with your user id ' + convert(varchar, customer_id) + ' and password ' + password

PS. Your first script doesn't concatenate values, it sends the same hard-coded string to all customers that including words "customer_id" and "password" as part of the message text, instead of their actual values

: I must be missing somethng on the syntax here as ms sql query analizer
: returns an error using the script above
: syntax error converting the varchar value 'Please login with your user id '
: to a column of data typ int

: I have no problem sending mail using the earlier script

Thu Sep 01, 2005 7:42 am View user's profile Send private message
Anthony Smith



Joined: 30 Aug 2005
Posts: 6

Post Re: create function for dbmail in MSSQL Reply with quote

: if customer_id is a numeric field then you need to convert it to a varchar
: first, for example

: 'Please login with your user id ' + convert(varchar, customer_id) + ' and
: password ' + password

: PS. Your first script doesn't concatenate values, it sends the same
: hard-coded string to all customers that including words
: "customer_id" and "password" as part of the message
: text, instead of their actual values

Thanks working fine now

However I seen to be limited on the message size how do I increase it.

Can I use
Insert into master.dbmail.[pipe] (recipients, subject, message varchar (1000), reply_to)

Thu Sep 01, 2005 12:26 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: create function for dbmail in MSSQL Reply with quote

In SQL Server message size is limited to 8K (max size for varchar column type). If this is not enough please see the docs on how to use "Message" atatchment to send unlimited emails (actually limited by your email server settings for the max message size.

: Thanks working fine now

: However I seen to be limited on the message size how do I increase it.

: Can I use
: Insert into master.dbmail.[pipe] (recipients, subject, message varchar
: (1000), reply_to)

Thu Sep 01, 2005 3:08 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.