PAGE HEADER: defined

Showing posts with label defined. Show all posts

Wednesday, March 21, 2012

Problem with SET QUOTED_IDENTIFIER ON

Hi,
I am creating User defined function with
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS OFF.
What is wrong.
ThanksHi,
look here, Iposted that some time ago:
http://forums.microsoft.com/MSDN/Sh...228076&SiteID=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||How do you know the settings are OFF? What version of SQL Server? The
following works for me under SQL 2000:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN @.Parameter1
END
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am creating User defined function with
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> But function is created with QUOTED_IDENTIFIER OFF and SET ANSI_NULLS
> OFF.
> What is wrong.
> Thanks
>|||I'm working with sql 2000 and result of
SELECT
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
GO
is null for myUdf.
Result of
select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
is 1.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
> How do you know the settings are OFF? What version of SQL Server? The
> following works for me under SQL 2000:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.TestFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN @.Parameter1
> END
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.TestFunction'), 'ExecIsQuotedIdentOn')
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:urdlGKmTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>|||The 'sticky' SET options for table valued functions are apparently not
reported correctly in SQL 2000 SP4. The create-time settings are used for
execution though. No problem in SQL 2005.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO
CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
RETURNS @.MyTable TABLE (Col1 int)
AS
BEGIN
RETURN
END
GO
CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
RETURNS int
AS
BEGIN
RETURN 1
END
GO
SELECT
OBJECTPROPERTY(id, 'IsInLineFunction'),
OBJECTPROPERTY(id, 'IsScalarFunction'),
OBJECTPROPERTY(id, 'IsTableFunction'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
FROM sysobjects
WHERE id IN
(
OBJECT_ID('dbo.myTableFunction'),
OBJECT_ID('dbo.myInLineFunction'),
OBJECT_ID('dbo.myScalarFunction')
)
Hope this helps.
Dan Guzman
SQL Server MVP
"AMiha" <amiha@.hotmail.com.false> wrote in message
news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
> I'm working with sql 2000 and result of
> SELECT
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsAnsiNullsOn'),
> OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'ExecIsQuotedIdentOn')
> GO
> is null for myUdf.
> Result of
> select OBJECTPROPERTY(OBJECT_ID('dbo.myUdf'), 'IsTableFunction')
> is 1.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e7DFkFnTGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thank you Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e4CxrBoTGHA.196@.TK2MSFTNGP10.phx.gbl...
> The 'sticky' SET options for table valued functions are apparently not
> reported correctly in SQL 2000 SP4. The create-time settings are used for
> execution though. No problem in SQL 2005.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE FUNCTION dbo.myTableFunction(@.Parameter1 int)
> RETURNS TABLE
> AS
> RETURN (SELECT 1 AS test)
> GO
> CREATE FUNCTION dbo.myInLineFunction(@.Parameter1 int)
> RETURNS @.MyTable TABLE (Col1 int)
> AS
> BEGIN
> RETURN
> END
> GO
> CREATE FUNCTION dbo.myScalarFunction(@.Parameter1 int)
> RETURNS int
> AS
> BEGIN
> RETURN 1
> END
> GO
> SELECT
> OBJECTPROPERTY(id, 'IsInLineFunction'),
> OBJECTPROPERTY(id, 'IsScalarFunction'),
> OBJECTPROPERTY(id, 'IsTableFunction'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn'),
> OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn')
> FROM sysobjects
> WHERE id IN
> (
> OBJECT_ID('dbo.myTableFunction'),
> OBJECT_ID('dbo.myInLineFunction'),
> OBJECT_ID('dbo.myScalarFunction')
> )
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AMiha" <amiha@.hotmail.com.false> wrote in message
> news:umq0ocnTGHA.4452@.TK2MSFTNGP12.phx.gbl...
>

Tuesday, March 20, 2012

Problem with sending mail with Database Mail

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

2) "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."

The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to the server?

please help me.

POUYAN

I am moving this to the SQL Server Database Engine forum.

Problem with sending mail with Database Mail

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

please help me.

POUYAN

This article in the forums seems to refer to your problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=718220&SiteID=1

It think it implies that the machine you are connecting to as an SMTP server is not actually running an SMTP service. You need to check that the machine you have set as the mail server is correct and that SMTP is running on it (on the standard port). There might also be a firewall issue between you and that machine.

I think if it was an authorisation issue with the SMTP server you would get a different error.

|||

Any way thank u.

I'll give it a try

|||

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings (ports,... ) between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||Database Mail uses SMTP, unlike the old SQL Mail that used Exchange by piggybacking off Outlook/MAPI. In other words, if you're trying to point it to your organization's Exchange server, it won't work unless Exchange is accepting SMTP connections. (I know only slightly less than jack squat about Exchange, so I have no idea how to enable it, or if it's enabled by default.)

If you want to test connectivity, use telnet from the command prompt:

telnet server-address 25

If you see some prompts from the server, or a blank screen with the cursor flashing, then the connection is probably fine. (Hit Ctrl+] and then type quit to exit telnet.) If you receive a message stating that the connection failed, then the issue is most likely that the machine you're pointing at isn't running SMTP. If you can get through with telnet, but DB Mail is still puking, then make sure whatever mail server you're pointing at isn't disallowing your database server to relay mail through it.|||

After doing some efforts now every thing looks to be ok.

in the log :

"Message was sent succesfully "

this shows that the mail was transfered from database mail to SMTP server.

but still the mail is not sent to the email address.

any idea?

|||

After doing some efforts now every thing looks to be ok.

in the log :

"Message was sent succesfully "

this shows that the mail was transfered from database mail to SMTP server.

but still the mail is not sent to the email address.

any idea?

|||In my experience, it's normally been one of two things.

1. Spam filtering. Pretty self-explanatory.

2. The SMTP server you're directly sending to is misconfigured, or otherwise unable to connect to any other mail servers. If you're running IIS SMTP, you can check C:\Inetpub\mailroot\badmail (or whichever directory you've moved Inetpub or badmail to) and see if there are any messages in there that it's given up on trying to deliver. Also, I believe the messages will sit in \Inetpub\mailroot\queue for some time before being thrown in badmail.|||

U where right the problem was spam filtering

Thnks a lot for ur help.

POUYAN.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

The problem has nothing to do with Service Broker. Your mailer process gets the mail to send and attempt to deliver, but ot cannot. The problems seems to be with yoyr @.mailserver_name or @.port parameters passed to sysmail_add_account_sp.

HTH,
~ Remus

|||

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)
"

and when I execute sysmail_help_queue_sp this would be the resuls:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

I just today installed SMTP services!!!!

and the type of error changed to this form:

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

This may also be happeneing because of any antivirus installed on your machine. BCoz Antivirus softwares ususally block the ports. For example, Macfee blocks the port no 25 which is used by SQL server to send mails.

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-04-09T15:24:10). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for abc@.abc.com

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:
Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

HTH,
~ Remus

|||

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

and when I execute sysmail_help_queue_sp this would be the resuls:

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

I just today installed SMTP services!!!!

and the type of error changed to this form:

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:
Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

HTH,
~ Remus

|||

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

and when I execute sysmail_help_queue_sp this would be the resuls:

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

I just today installed SMTP services!!!!

and the type of error changed to this form:

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

HTH,
~ Remus

|||

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

and when I execute sysmail_help_queue_sp this would be the resuls:

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

I just today installed SMTP services!!!!

and the type of error changed to this form:

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Wednesday, March 21, 2012

Problem with SET QUOTED_IDENTIFIER ON

Tuesday, March 20, 2012

Problem with sending mail with Database Mail

Problem with sending mail with Database Mail

Problem with sending mail via Database Mail

Problem with sending mail via Database Mail

Problem with sending mail via Database Mail

Problem with sending mail via Database Mail

PAGE HEADER

Blog Archive

About Me