Problem with LIKE %

I have a stored procedure:
CREATE PROCEDURE SearchHistoryClient
@.TextContain nvarchar(1000),
@.Email nvarchar(512),
@.Operator_URI nvarchar(512),
@.DateFrom DateTime,
@.DateTo DateTime,
@.Company_ID int
AS
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL = '
SELECT
tblTemp.ChatTime,
tblTemp.Client_ID,
tblTemp.Client_SIPURI,
tblOperator.Operator_ID,
tblOperator.Operator_SIPURI,
tblOperator.Operator_Alias,
tblTemp.ChatTranscript
FROM
(SELECT * FROM tblSupportClient INNER JOIN tblClient ON
(tblSupportClient.ClientID = tblClient.Client_ID))
AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
tblOperator.Operator_ID
WHERE
(tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
(tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
''' + cast(@.DateTo as nvarchar) + ''')
'
SELECT @.TextContain = '%'+ @.TextContain + '%';
-- Check if keyword is specified
IF (@.TextContain <> "")
BEGIN
SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
cast(@.TextContain as nvarchar) + ''') '
END
...
EXEC(@.SQL)
It always return 0 rows if @.TextContain contains more than a word.
e.g. if you call the procedure and the input string for @.TextContain is
"Hello"
then it returns some rows if they contains "Hello" string. But when you
specify "Hello John" it won't work correctly, though in your database the
string Hello John... exists.
Can anyone help me?Can you do a PRINT of @.SQL before executing, and see how the string is
formed? Btw, why are you using dynamic SQL?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John C#" <John C#@.discussions.microsoft.com> wrote in message
news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
> I have a stored procedure:
> CREATE PROCEDURE SearchHistoryClient
> @.TextContain nvarchar(1000),
> @.Email nvarchar(512),
> @.Operator_URI nvarchar(512),
> @.DateFrom DateTime,
> @.DateTo DateTime,
> @.Company_ID int
> AS
> DECLARE @.SQL nvarchar(4000)
> SELECT @.SQL = '
> SELECT
> tblTemp.ChatTime,
> tblTemp.Client_ID,
> tblTemp.Client_SIPURI,
> tblOperator.Operator_ID,
> tblOperator.Operator_SIPURI,
> tblOperator.Operator_Alias,
> tblTemp.ChatTranscript
> FROM
> (SELECT * FROM tblSupportClient INNER JOIN tblClient ON
> (tblSupportClient.ClientID = tblClient.Client_ID))
> AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
> tblOperator.Operator_ID
> WHERE
> (tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
> (tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
> ''' + cast(@.DateTo as nvarchar) + ''')
> '
> SELECT @.TextContain = '%'+ @.TextContain + '%';
> -- Check if keyword is specified
> IF (@.TextContain <> "")
> BEGIN
> SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
> cast(@.TextContain as nvarchar) + ''') '
> END
> ...
> EXEC(@.SQL)
> --
> It always return 0 rows if @.TextContain contains more than a word.
> e.g. if you call the procedure and the input string for @.TextContain is
> "Hello"
> then it returns some rows if they contains "Hello" string. But when you
> specify "Hello John" it won't work correctly, though in your database the
> string Hello John... exists.
> Can anyone help me?|||Hi,
I tried to create a new table with a field of ntext type and then I use SQL
command to select, it works. However, it still does not work with fields
marked <long text>. Is it about type error? What is the problem here?
"Narayana Vyas Kondreddi" wrote:

> Can you do a PRINT of @.SQL before executing, and see how the string is
> formed? Btw, why are you using dynamic SQL?
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John C#" <John C#@.discussions.microsoft.com> wrote in message
> news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
>
>

Problem with HTML slightly garbled sending email as web archive

I'm experiencing a problem with HTML code being slightly messed up when
sending a subscription via email in web archive format.
The report displays fine in the Report Manager. However, went emailed some
whitespace is seemingly randomly added to the HTML page. Depending on where
that whitespace is, this might not be a problem. But if the whitespace is
inserted in some places within an HTML tag, then the tag isn't rendered
properly.
Example: a space is added following the "<" in "</DIV>", thus giving "<
/DIV>". Since this is invalid HTML, it displays as text in the report.
Example: a cell in a table is defined as right-aligned and in a specific
font. All 25 rows in the table (x 3 columns = 75 cells) all display
correctly:
<P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
face="Eras Medium ITC" color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
Medium ITC'">
*EXCEPT* one cell, where the following HTML is used:
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
Why did RS render this cell differently?This might be an issue with the local SMTP. Are you using the pickup
directory to send email?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> I'm experiencing a problem with HTML code being slightly messed up when
> sending a subscription via email in web archive format.
> The report displays fine in the Report Manager. However, went emailed
> some
> whitespace is seemingly randomly added to the HTML page. Depending on
> where
> that whitespace is, this might not be a problem. But if the whitespace is
> inserted in some places within an HTML tag, then the tag isn't rendered
> properly.
> Example: a space is added following the "<" in "</DIV>", thus giving "<
> /DIV>". Since this is invalid HTML, it displays as text in the report.
> Example: a cell in a table is defined as right-aligned and in a specific
> font. All 25 rows in the table (x 3 columns = 75 cells) all display
> correctly:
> <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> face="Eras Medium ITC" color=black size=2><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> Medium ITC'">
> *EXCEPT* one cell, where the following HTML is used:
> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> style="FONT-SIZE: 12pt">
> Why did RS render this cell differently?|||Thanks for your reply, Daniel.
I've tried this with two SMTP mail servers, both with the same result. I
changed the definition via the RSReportServer.config file.
Neither SMTP server is on my local machine.
One resides on a Win2003 box and is a MS mail server; the other resides on a
Win2000 Server box and is a product called Merak Mail Server. Ultimately
they send via the Columbia University mail server, which is sendmail, I
believe.
(I'm not a systems guy so sorry if my answers aren't dead-on what you asked
for.)
Please also see my comments on another post title "Web archive subrsciption
gets messed up" (note misspelling of 'subscription'). I ran into an
identical problem like this when sending email from an Oracle PL/SQL program.
The resolution is to intersprese CRLF into the msg at least every 990 bytes.
Thanks Daniel. This is the one problem that's keeping me from implementing
RS and beginning to retire Crystal Enterprise.
"Daniel Reib [MSFT]" wrote:
> This might be an issue with the local SMTP. Are you using the pickup
> directory to send email?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> > I'm experiencing a problem with HTML code being slightly messed up when
> > sending a subscription via email in web archive format.
> >
> > The report displays fine in the Report Manager. However, went emailed
> > some
> > whitespace is seemingly randomly added to the HTML page. Depending on
> > where
> > that whitespace is, this might not be a problem. But if the whitespace is
> > inserted in some places within an HTML tag, then the tag isn't rendered
> > properly.
> >
> > Example: a space is added following the "<" in "</DIV>", thus giving "<
> > /DIV>". Since this is invalid HTML, it displays as text in the report.
> >
> > Example: a cell in a table is defined as right-aligned and in a specific
> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
> > correctly:
> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> > face="Eras Medium ITC" color=black size=2><SPAN
> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> > Medium ITC'">
> > *EXCEPT* one cell, where the following HTML is used:
> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> > style="FONT-SIZE: 12pt">
> > Why did RS render this cell differently?
>
>|||I haven't done this yet but what was suggested to me (I too am having
problems and am using an external smtp server too) was to use Windows 2003
smtp service and have it forward/route (not sure the term) to the external
smtp server. The Windows 2003 smtp server is configured to be looking at a
directory and forwarding things on from there. Then you have your report
saved to the pickup directory. Note that I haven't done this so I am a
little vague on how it all works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Columbia Kai" <ColumbiaKai@.discussions.microsoft.com> wrote in message
news:8995F878-CB68-4766-BB00-654D7580BAF1@.microsoft.com...
> Thanks for your reply, Daniel.
> I've tried this with two SMTP mail servers, both with the same result. I
> changed the definition via the RSReportServer.config file.
> Neither SMTP server is on my local machine.
> One resides on a Win2003 box and is a MS mail server; the other resides on
> a
> Win2000 Server box and is a product called Merak Mail Server. Ultimately
> they send via the Columbia University mail server, which is sendmail, I
> believe.
> (I'm not a systems guy so sorry if my answers aren't dead-on what you
> asked
> for.)
> Please also see my comments on another post title "Web archive
> subrsciption
> gets messed up" (note misspelling of 'subscription'). I ran into an
> identical problem like this when sending email from an Oracle PL/SQL
> program.
> The resolution is to intersprese CRLF into the msg at least every 990
> bytes.
> Thanks Daniel. This is the one problem that's keeping me from
> implementing
> RS and beginning to retire Crystal Enterprise.
>
> "Daniel Reib [MSFT]" wrote:
>> This might be an issue with the local SMTP. Are you using the pickup
>> directory to send email?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
>> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
>> > I'm experiencing a problem with HTML code being slightly messed up when
>> > sending a subscription via email in web archive format.
>> >
>> > The report displays fine in the Report Manager. However, went emailed
>> > some
>> > whitespace is seemingly randomly added to the HTML page. Depending on
>> > where
>> > that whitespace is, this might not be a problem. But if the whitespace
>> > is
>> > inserted in some places within an HTML tag, then the tag isn't rendered
>> > properly.
>> >
>> > Example: a space is added following the "<" in "</DIV>", thus giving "<
>> > /DIV>". Since this is invalid HTML, it displays as text in the report.
>> >
>> > Example: a cell in a table is defined as right-aligned and in a
>> > specific
>> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
>> > correctly:
>> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
>> > face="Eras Medium ITC" color=black size=2><SPAN
>> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY:
>> > 'Eras
>> > Medium ITC'">
>> > *EXCEPT* one cell, where the following HTML is used:
>> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
>> > style="FONT-SIZE: 12pt">
>> > Why did RS render this cell differently?
>>sql

Problem with DatabaseMail (SQL2K5)

Hi,
I use my gmail account to send email from outlook express. But when I try
this account from DatabaseMail, it fails with the following error:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
Message: Could not connect to mail server. (An established connection was
aborted by the software in your host machine)
Any help would be greatly appreciated,
Leila
You cannot use your gmail account to send database mail. You need the name of
your exchange server as database mail communicates with the exchange mail
server to send mail.
Suresh
"Leila" wrote:

> Hi,
> I use my gmail account to send email from outlook express. But when I try
> this account from DatabaseMail, it fails with the following error:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
> Message: Could not connect to mail server. (An established connection was
> aborted by the software in your host machine)
> Any help would be greatly appreciated,
> Leila
>
>
|||Thanks Suresh,
Do you mean DatabaseMail needs MAPI client like SQL Mail in SQL2K?
"Suresh" <Suresh@.discussions.microsoft.com> wrote in message
news:5F7C9C76-A4F7-42A9-A50A-C3CB4286E63A@.microsoft.com...[vbcol=seagreen]
> You cannot use your gmail account to send database mail. You need the name
> of
> your exchange server as database mail communicates with the exchange mail
> server to send mail.
> Suresh
> "Leila" wrote:
|||> You cannot use your gmail account to send database mail.
Yes, this is true.

> You need the name of
> your exchange server as database mail communicates with the exchange mail
> server to send mail.
No, this is not true. You can use the address of any SMTP server within
your network (or accessible from your network, but this is neither typical
nor advisable). You do not need Exchange or MAPI like you did with SQL
Mail.
A
|||> I use my gmail account to send email from outlook express. But when I try
> this account from DatabaseMail, it fails with the following error:
> The mail could not be sent to the recipients because of the mail server
> failure.
You cannot use gmail. You need to set up a profile within Database Mail and
have it connect directly to an SMTP server. Typically this is a server
inside your own network, and no you don't need Exchange (e.g. you can use
the SMTP service that ships with IIS in Windows). The SMTP server of course
needs to be set up to allow relaying from the database server IP.
|||Database mail needs an SMTP server, which could or could not be Exchange.
The problem you are having is related to spam-prevention. GMail is not
allowing you to use their SMTP server to send mail, otherwise, anyone could
send email out willy-nilly and spam the world. For the record, I did test
this just to confirm my statement here.
Options:
1. If you have a local Exchange server, use it as the SMTP server.
2. If your SQL Server is at a hosting provider, they should provide you with
an SMTP server.
3. If you are hosting and don't have Exchange, you can get an inexpensive or
free SMTP server, install it, and use that.
If you go with option 3, you will need to do the following:
1. Secure the hell out of it.
** Do not allow relaying, except from the machine where SQL Server
resides.
** Require authentication. If the SMTP server doesn't support
Windows Crednetials, require SSL for the "Basic Authentication".
** Do not allow anonymous authentication!!!!!
** Make sure no one can access it from the outside (get a *good*
hardware firewall from SonicWall, Zyxel, Cisco, and so on).
2. Setup Reverse-DNS for your mail server, usually done via your ISP. If you
don't, your SMTP server may get black-listed as a spam server.
Did I mention that you need to secure the hell out of the SMTP server?
--Peter
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OYnNruBjHHA.4132@.TK2MSFTNGP05.phx.gbl...
> Thanks Suresh,
> Do you mean DatabaseMail needs MAPI client like SQL Mail in SQL2K?
>
> "Suresh" <Suresh@.discussions.microsoft.com> wrote in message
> news:5F7C9C76-A4F7-42A9-A50A-C3CB4286E63A@.microsoft.com...
>
|||Arron,
Sorry. You are right. Any SMTP server within the network can be used. I
think i used the wrong word here.
Suresh
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, this is true.
>
> No, this is not true. You can use the address of any SMTP server within
> your network (or accessible from your network, but this is neither typical
> nor advisable). You do not need Exchange or MAPI like you did with SQL
> Mail.
> A
>
>
|||Thanks everybody!
McAfee antivirus was checking port 25. I could send email via outlook but it
seemed McAfee considers DatabaseMail as kind of mass mail worm and was
protecting that! After a lot of work, I found an option which allowed the
following processes to use port 25 (in McAfee):
amgrsrvc.exe,tomcat.exe,outlook.exe,msimn.exe,agen t.exe,eudora.exe,nlnotes.exe,mozilla.exe,netscp.ex e,opera.exe,winpm-32.exe,pine.exe,poco.exe,thebat.exe,thunderbird.ex e,ntaskldr.exe,inetinfo.exe,nsmtp.exe,nrouter.exe, tomcat5.exe,tomcat5w.exe,ebs.exe,FireSvc.exe,modul ewrapper.exe,MSKSrvr.exe,MSKDetct.exe,mapisp32.exe
I disabled this protection and DatabaseMail worked! I think adding
DatabaseMail90.exe to this list will have the same result but haven't tried
yet.
"Peter W. DeBetta" <debettap@.hotmail.com> wrote in message
news:%23D5mnACjHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Database mail needs an SMTP server, which could or could not be Exchange.
> The problem you are having is related to spam-prevention. GMail is not
> allowing you to use their SMTP server to send mail, otherwise, anyone
> could send email out willy-nilly and spam the world. For the record, I did
> test this just to confirm my statement here.
> Options:
> 1. If you have a local Exchange server, use it as the SMTP server.
> 2. If your SQL Server is at a hosting provider, they should provide you
> with an SMTP server.
> 3. If you are hosting and don't have Exchange, you can get an inexpensive
> or free SMTP server, install it, and use that.
> If you go with option 3, you will need to do the following:
> 1. Secure the hell out of it.
> ** Do not allow relaying, except from the machine where SQL Server
> resides.
> ** Require authentication. If the SMTP server doesn't support
> Windows Crednetials, require SSL for the "Basic Authentication".
> ** Do not allow anonymous authentication!!!!!
> ** Make sure no one can access it from the outside (get a *good*
> hardware firewall from SonicWall, Zyxel, Cisco, and so on).
> 2. Setup Reverse-DNS for your mail server, usually done via your ISP. If
> you don't, your SMTP server may get black-listed as a spam server.
> Did I mention that you need to secure the hell out of the SMTP server?
> --Peter
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OYnNruBjHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Problem with DatabaseMail (SQL2K5)

Hi,
I use my gmail account to send email from outlook express. But when I try
this account from DatabaseMail, it fails with the following error:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
Message: Could not connect to mail server. (An established connection was
aborted by the software in your host machine)
Any help would be greatly appreciated,
LeilaYou cannot use your gmail account to send database mail. You need the name o
f
your exchange server as database mail communicates with the exchange mail
server to send mail.
Suresh
"Leila" wrote:

> Hi,
> I use my gmail account to send email from outlook express. But when I try
> this account from DatabaseMail, it fails with the following error:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
> Message: Could not connect to mail server. (An established connection was
> aborted by the software in your host machine)
> Any help would be greatly appreciated,
> Leila
>
>|||Thanks Suresh,
Do you mean DatabaseMail needs MAPI client like SQL Mail in SQL2K?
"Suresh" <Suresh@.discussions.microsoft.com> wrote in message
news:5F7C9C76-A4F7-42A9-A50A-C3CB4286E63A@.microsoft.com...[vbcol=seagreen]
> You cannot use your gmail account to send database mail. You need the name
> of
> your exchange server as database mail communicates with the exchange mail
> server to send mail.
> Suresh
> "Leila" wrote:
>|||> You cannot use your gmail account to send database mail.
Yes, this is true.

> You need the name of
> your exchange server as database mail communicates with the exchange mail
> server to send mail.
No, this is not true. You can use the address of any SMTP server within
your network (or accessible from your network, but this is neither typical
nor advisable). You do not need Exchange or MAPI like you did with SQL
Mail.
A|||> I use my gmail account to send email from outlook express. But when I try
> this account from DatabaseMail, it fails with the following error:
> The mail could not be sent to the recipients because of the mail server
> failure.
You cannot use gmail. You need to set up a profile within Database Mail and
have it connect directly to an SMTP server. Typically this is a server
inside your own network, and no you don't need Exchange (e.g. you can use
the SMTP service that ships with IIS in Windows). The SMTP server of course
needs to be set up to allow relaying from the database server IP.|||Database mail needs an SMTP server, which could or could not be Exchange.
The problem you are having is related to spam-prevention. GMail is not
allowing you to use their SMTP server to send mail, otherwise, anyone could
send email out willy-nilly and spam the world. For the record, I did test
this just to confirm my statement here.
Options:
1. If you have a local Exchange server, use it as the SMTP server.
2. If your SQL Server is at a hosting provider, they should provide you with
an SMTP server.
3. If you are hosting and don't have Exchange, you can get an inexpensive or
free SMTP server, install it, and use that.
If you go with option 3, you will need to do the following:
1. Secure the hell out of it.
** Do not allow relaying, except from the machine where SQL Server
resides.
** Require authentication. If the SMTP server doesn't support
Windows Crednetials, require SSL for the "Basic Authentication".
** Do not allow anonymous authentication!!!!!
** Make sure no one can access it from the outside (get a *good*
hardware firewall from SonicWall, Zyxel, Cisco, and so on).
2. Setup Reverse-DNS for your mail server, usually done via your ISP. If you
don't, your SMTP server may get black-listed as a spam server.
Did I mention that you need to secure the hell out of the SMTP server?
--Peter
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:OYnNruBjHHA.4132@.TK2MSFTNGP05.phx.gbl...
> Thanks Suresh,
> Do you mean DatabaseMail needs MAPI client like SQL Mail in SQL2K?
>
> "Suresh" <Suresh@.discussions.microsoft.com> wrote in message
> news:5F7C9C76-A4F7-42A9-A50A-C3CB4286E63A@.microsoft.com...
>|||Arron,
Sorry. You are right. Any SMTP server within the network can be used. I
think i used the wrong word here.
Suresh
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, this is true.
>
> No, this is not true. You can use the address of any SMTP server within
> your network (or accessible from your network, but this is neither typical
> nor advisable). You do not need Exchange or MAPI like you did with SQL
> Mail.
> A
>
>|||Thanks everybody!
McAfee antivirus was checking port 25. I could send email via outlook but it
seemed McAfee considers DatabaseMail as kind of mass mail worm and was
protecting that! After a lot of work, I found an option which allowed the
following processes to use port 25 (in McAfee):
amgrsrvc.exe,tomcat.exe,outlook.exe,msimn.exe,agent.exe,eudora.exe,nlnotes.e
xe,mozilla.exe,netscp.exe,opera.exe,winpm-32.exe,pine.exe,poco.exe,thebat.ex
e,thunderbird.exe,ntaskldr.exe,inetinfo.exe,nsmtp.exe,nrouter.exe,tomcat5.ex
e,tomcat5w.exe,ebs.exe,Fire
Svc.exe,modulewrapper.exe,MSKSrvr.exe,MSKDetct.exe,mapisp32.exe
I disabled this protection and DatabaseMail worked! I think adding
DatabaseMail90.exe to this list will have the same result but haven't tried
yet.
"Peter W. DeBetta" <debettap@.hotmail.com> wrote in message
news:%23D5mnACjHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Database mail needs an SMTP server, which could or could not be Exchange.
> The problem you are having is related to spam-prevention. GMail is not
> allowing you to use their SMTP server to send mail, otherwise, anyone
> could send email out willy-nilly and spam the world. For the record, I did
> test this just to confirm my statement here.
> Options:
> 1. If you have a local Exchange server, use it as the SMTP server.
> 2. If your SQL Server is at a hosting provider, they should provide you
> with an SMTP server.
> 3. If you are hosting and don't have Exchange, you can get an inexpensive
> or free SMTP server, install it, and use that.
> If you go with option 3, you will need to do the following:
> 1. Secure the hell out of it.
> ** Do not allow relaying, except from the machine where SQL Server
> resides.
> ** Require authentication. If the SMTP server doesn't support
> Windows Crednetials, require SSL for the "Basic Authentication".
> ** Do not allow anonymous authentication!!!!!
> ** Make sure no one can access it from the outside (get a *good*
> hardware firewall from SonicWall, Zyxel, Cisco, and so on).
> 2. Setup Reverse-DNS for your mail server, usually done via your ISP. If
> you don't, your SMTP server may get black-listed as a spam server.
> Did I mention that you need to secure the hell out of the SMTP server?
> --Peter
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OYnNruBjHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Problem with DatabaseMail (SQL2K5)

Hi,
I use my gmail account to send email from outlook express. But when I try
this account from DatabaseMail, it fails with the following error:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
Message: Could not connect to mail server. (An established connection was
aborted by the software in your host machine)
Any help would be greatly appreciated,
LeilaYou cannot use your gmail account to send database mail. You need the name of
your exchange server as database mail communicates with the exchange mail
server to send mail.
Suresh
"Leila" wrote:
> Hi,
> I use my gmail account to send email from outlook express. But when I try
> this account from DatabaseMail, it fails with the following error:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
> Message: Could not connect to mail server. (An established connection was
> aborted by the software in your host machine)
> Any help would be greatly appreciated,
> Leila
>
>|||Thanks Suresh,
Do you mean DatabaseMail needs MAPI client like SQL Mail in SQL2K?
"Suresh" <Suresh@.discussions.microsoft.com> wrote in message
news:5F7C9C76-A4F7-42A9-A50A-C3CB4286E63A@.microsoft.com...
> You cannot use your gmail account to send database mail. You need the name
> of
> your exchange server as database mail communicates with the exchange mail
> server to send mail.
> Suresh
> "Leila" wrote:
>> Hi,
>> I use my gmail account to send email from outlook express. But when I try
>> this account from DatabaseMail, it fails with the following error:
>> The mail could not be sent to the recipients because of the mail server
>> failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
>> Message: Could not connect to mail server. (An established connection was
>> aborted by the software in your host machine)
>> Any help would be greatly appreciated,
>> Leila
>>|||> You cannot use your gmail account to send database mail.
Yes, this is true.
> You need the name of
> your exchange server as database mail communicates with the exchange mail
> server to send mail.
No, this is not true. You can use the address of any SMTP server within
your network (or accessible from your network, but this is neither typical
nor advisable). You do not need Exchange or MAPI like you did with SQL
Mail.
A|||> I use my gmail account to send email from outlook express. But when I try
> this account from DatabaseMail, it fails with the following error:
> The mail could not be sent to the recipients because of the mail server
> failure.
You cannot use gmail. You need to set up a profile within Database Mail and
have it connect directly to an SMTP server. Typically this is a server
inside your own network, and no you don't need Exchange (e.g. you can use
the SMTP service that ships with IIS in Windows). The SMTP server of course
needs to be set up to allow relaying from the database server IP.|||Database mail needs an SMTP server, which could or could not be Exchange.
The problem you are having is related to spam-prevention. GMail is not
allowing you to use their SMTP server to send mail, otherwise, anyone could
send email out willy-nilly and spam the world. For the record, I did test
this just to confirm my statement here.
Options:
1. If you have a local Exchange server, use it as the SMTP server.
2. If your SQL Server is at a hosting provider, they should provide you with
an SMTP server.
3. If you are hosting and don't have Exchange, you can get an inexpensive or
free SMTP server, install it, and use that.
If you go with option 3, you will need to do the following:
1. Secure the hell out of it.
** Do not allow relaying, except from the machine where SQL Server
resides.
** Require authentication. If the SMTP server doesn't support
Windows Crednetials, require SSL for the "Basic Authentication".
** Do not allow anonymous authentication!!!!!
** Make sure no one can access it from the outside (get a *good*
hardware firewall from SonicWall, Zyxel, Cisco, and so on).
2. Setup Reverse-DNS for your mail server, usually done via your ISP. If you
don't, your SMTP server may get black-listed as a spam server.
Did I mention that you need to secure the hell out of the SMTP server?
--Peter
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:OYnNruBjHHA.4132@.TK2MSFTNGP05.phx.gbl...
> Thanks Suresh,
> Do you mean DatabaseMail needs MAPI client like SQL Mail in SQL2K?
>
> "Suresh" <Suresh@.discussions.microsoft.com> wrote in message
> news:5F7C9C76-A4F7-42A9-A50A-C3CB4286E63A@.microsoft.com...
>> You cannot use your gmail account to send database mail. You need the
>> name of
>> your exchange server as database mail communicates with the exchange mail
>> server to send mail.
>> Suresh
>> "Leila" wrote:
>> Hi,
>> I use my gmail account to send email from outlook express. But when I
>> try
>> this account from DatabaseMail, it fails with the following error:
>> The mail could not be sent to the recipients because of the mail server
>> failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception
>> Message: Could not connect to mail server. (An established connection
>> was
>> aborted by the software in your host machine)
>> Any help would be greatly appreciated,
>> Leila
>>
>|||Arron,
Sorry. You are right. Any SMTP server within the network can be used. I
think i used the wrong word here.
Suresh
"Aaron Bertrand [SQL Server MVP]" wrote:
> > You cannot use your gmail account to send database mail.
> Yes, this is true.
> > You need the name of
> > your exchange server as database mail communicates with the exchange mail
> > server to send mail.
> No, this is not true. You can use the address of any SMTP server within
> your network (or accessible from your network, but this is neither typical
> nor advisable). You do not need Exchange or MAPI like you did with SQL
> Mail.
> A
>
>|||Thanks everybody!
McAfee antivirus was checking port 25. I could send email via outlook but it
seemed McAfee considers DatabaseMail as kind of mass mail worm and was
protecting that! After a lot of work, I found an option which allowed the
following processes to use port 25 (in McAfee):
amgrsrvc.exe,tomcat.exe,outlook.exe,msimn.exe,agent.exe,eudora.exe,nlnotes.exe,mozilla.exe,netscp.exe,opera.exe,winpm-32.exe,pine.exe,poco.exe,thebat.exe,thunderbird.exe,ntaskldr.exe,inetinfo.exe,nsmtp.exe,nrouter.exe,tomcat5.exe,tomcat5w.exe,ebs.exe,FireSvc.exe,modulewrapper.exe,MSKSrvr.exe,MSKDetct.exe,mapisp32.exe
I disabled this protection and DatabaseMail worked! I think adding
DatabaseMail90.exe to this list will have the same result but haven't tried
yet.
"Peter W. DeBetta" <debettap@.hotmail.com> wrote in message
news:%23D5mnACjHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Database mail needs an SMTP server, which could or could not be Exchange.
> The problem you are having is related to spam-prevention. GMail is not
> allowing you to use their SMTP server to send mail, otherwise, anyone
> could send email out willy-nilly and spam the world. For the record, I did
> test this just to confirm my statement here.
> Options:
> 1. If you have a local Exchange server, use it as the SMTP server.
> 2. If your SQL Server is at a hosting provider, they should provide you
> with an SMTP server.
> 3. If you are hosting and don't have Exchange, you can get an inexpensive
> or free SMTP server, install it, and use that.
> If you go with option 3, you will need to do the following:
> 1. Secure the hell out of it.
> ** Do not allow relaying, except from the machine where SQL Server
> resides.
> ** Require authentication. If the SMTP server doesn't support
> Windows Crednetials, require SSL for the "Basic Authentication".
> ** Do not allow anonymous authentication!!!!!
> ** Make sure no one can access it from the outside (get a *good*
> hardware firewall from SonicWall, Zyxel, Cisco, and so on).
> 2. Setup Reverse-DNS for your mail server, usually done via your ISP. If
> you don't, your SMTP server may get black-listed as a spam server.
> Did I mention that you need to secure the hell out of the SMTP server?
> --Peter
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OYnNruBjHHA.4132@.TK2MSFTNGP05.phx.gbl...
>> Thanks Suresh,
>> Do you mean DatabaseMail needs MAPI client like SQL Mail in SQL2K?
>>
>> "Suresh" <Suresh@.discussions.microsoft.com> wrote in message
>> news:5F7C9C76-A4F7-42A9-A50A-C3CB4286E63A@.microsoft.com...
>> You cannot use your gmail account to send database mail. You need the
>> name of
>> your exchange server as database mail communicates with the exchange
>> mail
>> server to send mail.
>> Suresh
>> "Leila" wrote:
>> Hi,
>> I use my gmail account to send email from outlook express. But when I
>> try
>> this account from DatabaseMail, it fails with the following error:
>> The mail could not be sent to the recipients because of the mail server
>> failure. (Sending Mail using Account 15 (2007-05-01T20:46:41).
>> Exception
>> Message: Could not connect to mail server. (An established connection
>> was
>> aborted by the software in your host machine)
>> Any help would be greatly appreciated,
>> Leila
>>
>>
>