I've been searching for a new ERP solution for my company for a while and I
think I've finally found the best one for us. But now I'm being cautioned
about some things so I need some good arguments about how I should setup the
database server.
The application is written in .net it is a N tiered application (I guess
that's what it's called) and it also has a tightly integrated commerce site.
The system was designed to work best with the app db and commerce db on the
same sql server. Desktop clients can connect by internet access if that
server isn't on the LAN and the website can even connect to the LAN but this
obviously wouldn't work because we're only using DSL here.
I've been cautioned that I shouldn't have internet access on my database
server. I somewhat understand the reasoning for this but to me it seems most
data is about as secure as the applications that access them in the first
place. So even if I had a state of the art network in place, a security flaw
in the app could screw me over regardless.
What is good business security practice for handling/storing sensitive
customer information on the internet and what is overkill/paranoia? This is
a serious questions so I'd appreciate not to be mocked.
I'm not a DBA so maybe I'm a little off on my terms, feel free to correct me
where I'm wrong. But it seems to me that if didn't use the same db server
for both the website and main system I'd have a lot of extra work like
replicating web data to web enabled sql server in our office and from the
"live server" on our LAN and then back again.
Thanks in advance for your advice/criticism/suggestions.
RobertForgot to mention. I was assuming to set it up like this
[dedicated sql server] - the only port enabled on this box is for mssql
would probably use a non default port
[dedicated web/email/application server] ports, email and web/ssl
(25/80/110/443)|||Normally what you do is expose the middle-tier (or even yet another system)
to the Internet, and then you only allow that system to connect to your
database server. Yes, if that system is compromised, there can also be
issues, but if you code correctly the only things exposed would be those tha
t
stored procedures or view expose. There are several good books on designing
secure web applications, so I suggest you check out your local bookstore for
one that fits your style.
"Rob" wrote:
> I've been searching for a new ERP solution for my company for a while and
I
> think I've finally found the best one for us. But now I'm being cautioned
> about some things so I need some good arguments about how I should setup t
he
> database server.
> The application is written in .net it is a N tiered application (I guess
> that's what it's called) and it also has a tightly integrated commerce sit
e.
> The system was designed to work best with the app db and commerce db on th
e
> same sql server. Desktop clients can connect by internet access if that
> server isn't on the LAN and the website can even connect to the LAN but th
is
> obviously wouldn't work because we're only using DSL here.
> I've been cautioned that I shouldn't have internet access on my database
> server. I somewhat understand the reasoning for this but to me it seems mo
st
> data is about as secure as the applications that access them in the first
> place. So even if I had a state of the art network in place, a security fl
aw
> in the app could screw me over regardless.
> What is good business security practice for handling/storing sensitive
> customer information on the internet and what is overkill/paranoia? This i
s
> a serious questions so I'd appreciate not to be mocked.
> I'm not a DBA so maybe I'm a little off on my terms, feel free to correct
me
> where I'm wrong. But it seems to me that if didn't use the same db server
> for both the website and main system I'd have a lot of extra work like
> replicating web data to web enabled sql server in our office and from the
> "live server" on our LAN and then back again.
> Thanks in advance for your advice/criticism/suggestions.
> Robert
>
>
Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts
Wednesday, March 7, 2012
my Synchronization Scenario
Hi, I want to know the a solution for my Synchronization Scenario
I have a several client databses which are SQL Server 2005 Express and i have a master database which is SQL Server 2000 containing all the individual Client databases. All the individual client databases are kept seperately at the master location. I need to Synchronization the client database with its copy at the master database (something like Merge replication). Both the Client Copy as well as Master Copy could be Publishers & Subscribers.
Now the problem is Because of security & firewall issues, only the Client should have the ability to schedule & initiate the synchronization process with the master copy. Unfortunately SQL Server 2005 Express has only subscriber agent and not a publisher agent.
Any help on how to achieve this would be appreciated . Thank Youno idea, but you implimented BETA technology. Any chance you can get everything on MSDE?
I have a several client databses which are SQL Server 2005 Express and i have a master database which is SQL Server 2000 containing all the individual Client databases. All the individual client databases are kept seperately at the master location. I need to Synchronization the client database with its copy at the master database (something like Merge replication). Both the Client Copy as well as Master Copy could be Publishers & Subscribers.
Now the problem is Because of security & firewall issues, only the Client should have the ability to schedule & initiate the synchronization process with the master copy. Unfortunately SQL Server 2005 Express has only subscriber agent and not a publisher agent.
Any help on how to achieve this would be appreciated . Thank Youno idea, but you implimented BETA technology. Any chance you can get everything on MSDE?
My sqlcache doesn't work consistently under ASP.NET 2.0, any suggestion?
I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.
The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.
I am using 2.0 + SQL 2005.
Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.
The following is the code I use to handle the cache :
string cacheKey = LinkSites.GetMappedKey(virtualPath, fileid.ToString()); // this will return a key from virtualPath
if (!String.IsNullOrEmpty(cacheKey)) frd = (FileRecordData)HttpContext.Current.Cache[cacheKey];
if (frd == null)
{
int siteid = 0;
SqlCacheDependency scd = null;
lock (_connection)
{
try
{
SqlCommand sqlcmd = new SqlCommand("select ownerid,id,uniqueid,parentid,category,name,content,dated=isnull(updated,created),created,updated,isdirectory from dbo.link_sourcestore where id=@.id", Connection);
sqlcmd.CommandType = CommandType.Text;
SqlParameter sqlparam;
sqlparam = sqlcmd.Parameters.Add("@.id", SqlDbType.Int);
sqlparam.Value = fileid;
scd = new SqlCacheDependency(sqlcmd);
using (SqlDataReader reader = sqlcmd.ExecuteReader())
{
if (!reader.HasRows) return null;
reader.Read();
siteid = LinkRoutine.Convert(reader["ownerid"], 0);
frd = GetRecordData(reader);
}
}
catch (Exception e)
{
ErrorHandler.Report("GetCachedFileRecord 2 [" + realVirtualPath + "," + virtualPath + "]", e);
return null;
}
}
if (scd != null)
{
frd.CacheKey = cacheKey;
frd.CacheDependency = scd;
HttpRuntime.Cache.Insert(cacheKey, frd, scd, Cache.NoAbsoluteExpiration, new TimeSpan(24, 0, 0), CacheItemPriority.NotRemovable, new CacheItemRemovedCallback(LinkCacheHandler.RemovedCallback));
}
}
It just read the record and add into cache, when cache item removed, call the static method RemovedCallback in LinkCacheHandler, LinkCacheHandler is posted below. After I restart IIS, it will work for a while, 5, 10 or more minutes, but after a while, even I set breakpoint in RemovedCallback, I don't get anything when I change the record. (when I call my clear cache method, which will remove all records from cache, it runs to the breakpoint. So the callback is fine)
public class LinkCacheHandler
{
public static void RemovedCallback(string k, object v, CacheItemRemovedReason r)
{
if (!k.Contains("system/cache.ascx"))
{
LinkSites._cacheLog += "RemovedCallback[" + DateTime.Now.ToString() + "]<br/> " + k + ((v is FileRecordData)?(" : " + ((FileRecordData)v).CacheKey) : "") + " " + r.ToString() + "\n<br/>";
LinkSites.NotifyCacheObject(k);
}
}
}
It is me again.
I found a hotfix for this : http://support.microsoft.com/kb/913364/en-us#appliesto
I asked this question if not for a year, I will say at least more than half year. Noone from microsoft response. And today I finally find the hotfix. You bet it is not easy to find it, they seems try to hide it.
It is a big problem! it happens to many users!
My sqlcache doesn't work consistently under ASP.NET 2.0, any suggestion?
I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.
The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.
I am using 2.0 + SQL 2005.
Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.
The following is the code I use to handle the cache :
string cacheKey = LinkSites.GetMappedKey(virtualPath, fileid.ToString()); // this will return a key from virtualPath
if (!String.IsNullOrEmpty(cacheKey)) frd = (FileRecordData)HttpContext.Current.Cache[cacheKey];
if (frd == null)
{
int siteid = 0;
SqlCacheDependency scd = null;
lock (_connection)
{
try
{
SqlCommand sqlcmd = new SqlCommand("select ownerid,id,uniqueid,parentid,category,name,content,dated=isnull(updated,created),created,updated,isdirectory from dbo.link_sourcestore where id=@.id", Connection);
sqlcmd.CommandType = CommandType.Text;
SqlParameter sqlparam;
sqlparam = sqlcmd.Parameters.Add("@.id", SqlDbType.Int);
sqlparam.Value = fileid;
scd = new SqlCacheDependency(sqlcmd);
using (SqlDataReader reader = sqlcmd.ExecuteReader())
{
if (!reader.HasRows) return null;
reader.Read();
siteid = LinkRoutine.Convert(reader["ownerid"], 0);
frd = GetRecordData(reader);
}
}
catch (Exception e)
{
ErrorHandler.Report("GetCachedFileRecord 2 [" + realVirtualPath + "," + virtualPath + "]", e);
return null;
}
}
if (scd != null)
{
frd.CacheKey = cacheKey;
frd.CacheDependency = scd;
HttpRuntime.Cache.Insert(cacheKey, frd, scd, Cache.NoAbsoluteExpiration, new TimeSpan(24, 0, 0), CacheItemPriority.NotRemovable, new CacheItemRemovedCallback(LinkCacheHandler.RemovedCallback));
}
}
It just read the record and add into cache, when cache item removed, call the static method RemovedCallback in LinkCacheHandler, LinkCacheHandler is posted below. After I restart IIS, it will work for a while, 5, 10 or more minutes, but after a while, even I set breakpoint in RemovedCallback, I don't get anything when I change the record. (when I call my clear cache method, which will remove all records from cache, it runs to the breakpoint. So the callback is fine)
public class LinkCacheHandler
{
public static void RemovedCallback(string k, object v, CacheItemRemovedReason r)
{
if (!k.Contains("system/cache.ascx"))
{
LinkSites._cacheLog += "RemovedCallback[" + DateTime.Now.ToString() + "]<br/> " + k + ((v is FileRecordData)?(" : " + ((FileRecordData)v).CacheKey) : "") + " " + r.ToString() + "\n<br/>";
LinkSites.NotifyCacheObject(k);
}
}
}
It is me again.
I found a hotfix for this : http://support.microsoft.com/kb/913364/en-us#appliesto
I asked this question if not for a year, I will say at least more than half year. Noone from microsoft response. And today I finally find the hotfix. You bet it is not easy to find it, they seems try to hide it.
It is a big problem! it happens to many users!
Saturday, February 25, 2012
My Solution for SQL Mail on SBS
It seems that most of the SBS admins have had the same problem with SQL Mail
because Outlook cannot be installed when Exchange is present (usually the
case with SBS) and therefore no MAPI profile is available for SQL Mail.
I use the following stored procedure. It creates a CDO object (sorry,
redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
application.
Feel free to comment and improve if you want. One thing I cannot do is
change the importance (low, normal, high) of the email. I'd like to know how
to do this... It's actually harder than it seems.
-- Switch CREATE to ALTER if needed
CREATE PROCEDURE dbo.sp_sendSMTPmail
(
@.To varchar(8000) = null,
@.Subject varchar(255) = null,
@.Body text = null,
@.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
@.Cc varchar(8000) = null,
@.Bcc varchar(8000) = null,
@.Attachments varchar(8000) = null, -- delimeter is ;
@.HTMLFormat int = 0,
@.From varchar(255) = null
)
AS
-- Declare
DECLARE @.message int
DECLARE @.config int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
object
EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
configuration object
-- Configuration Object
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)', 'localhost' --
SMTP Server
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
Server SMTP Port
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @.config, 'Fields.Update'
-- Message Object
EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
message.configuration = config
EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
IF (@.From Is Not Null) AND (@.From Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
END
ELSE BEGIN
-- Modify the following to fit your needs
EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
END
IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
END
IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
END
IF (@.HTMLFormat = 1)
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
END
ELSE BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
END
-- The full path to the attachments has to be provided: 'c:/path/myfile.txt'
IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
END
EXEC sp_OAMethod @.message, 'Send()'
-- Destroys the objects
EXEC @.hr = sp_OADestroy @.message
EXEC @.hr = sp_OADestroy @.config
-- Errorhandler
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
GO
/* Test below - Replace @.email addresses with real addresses to test
EXEC dbo.sp_sendSMTPmail
@.To='recipient@.email.com',
@.Subject='This is a test',
@.Body='HTML Body',
@.Cc='',
@.Bcc='',
@.Attachments='',
@.HTMLFormat=1,
@.From='sender@.email.com'
*/
Not to step on your solution, but I have found that xpsmtp from
www.sqldev.net works very well as a smtp connector for SQL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314abac$0$32207$39cecf19@.news.twtelecom.net. ..
> It seems that most of the SBS admins have had the same problem with SQL
> Mail because Outlook cannot be installed when Exchange is present (usually
> the case with SBS) and therefore no MAPI profile is available for SQL
> Mail.
> I use the following stored procedure. It creates a CDO object (sorry,
> redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
> application.
> Feel free to comment and improve if you want. One thing I cannot do is
> change the importance (low, normal, high) of the email. I'd like to know
> how to do this... It's actually harder than it seems.
> -- Switch CREATE to ALTER if needed
> CREATE PROCEDURE dbo.sp_sendSMTPmail
> (
> @.To varchar(8000) = null,
> @.Subject varchar(255) = null,
> @.Body text = null,
> @.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
> @.Cc varchar(8000) = null,
> @.Bcc varchar(8000) = null,
> @.Attachments varchar(8000) = null, -- delimeter is ;
> @.HTMLFormat int = 0,
> @.From varchar(255) = null
> )
> AS
> -- Declare
> DECLARE @.message int
> DECLARE @.config int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
> object
> EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
> configuration object
> -- Configuration Object
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
> 'cdoSendUsingPort' -- Send the message using the network
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)',
> 'localhost' -- SMTP Server
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
> Server SMTP Port
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
> 'cdoAnonymous' -- Anonymous SMTP Authenticate
> EXEC sp_OAMethod @.config, 'Fields.Update'
>
> -- Message Object
> EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
> message.configuration = config
> EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
> EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
>
> IF (@.From Is Not Null) AND (@.From Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
> END
> ELSE BEGIN
> -- Modify the following to fit your needs
> EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
> END
> IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
> END
> IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
> END
> IF (@.HTMLFormat = 1)
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
> END
> ELSE BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
> END
> -- The full path to the attachments has to be provided:
> 'c:/path/myfile.txt'
> IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
> END
>
> EXEC sp_OAMethod @.message, 'Send()'
> -- Destroys the objects
> EXEC @.hr = sp_OADestroy @.message
> EXEC @.hr = sp_OADestroy @.config
> -- Errorhandler
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> GO
>
> /* Test below - Replace @.email addresses with real addresses to test
> EXEC dbo.sp_sendSMTPmail
> @.To='recipient@.email.com',
> @.Subject='This is a test',
> @.Body='HTML Body',
> @.Cc='',
> @.Bcc='',
> @.Attachments='',
> @.HTMLFormat=1,
> @.From='sender@.email.com'
> */
>
|||It uses xp_smtp_sendmail wich is not available without a MAPI profile.
Your "solution" is not really a solution, it is simply a development over
SQL Mail which, in my case, and that of others, does not work.
|||You are incorrect. xp_smtp_sendmail does not use a MAPI profile, nor does
it require that Outlook be installed. If you read teh documentation on
www.sqldev.net you will see that while it is not a complete, drop-in
replacement, it does allow an outbound email path without using Outlook. I
have used this tool for several years now and have had zero problems. I
also do not have Outlook installed on any server where I use this add-on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314bda0$0$32203$39cecf19@.news.twtelecom.net. ..
> It uses xp_smtp_sendmail wich is not available without a MAPI profile.
> Your "solution" is not really a solution, it is simply a development over
> SQL Mail which, in my case, and that of others, does not work.
>
|||My solution is specifically when xp_smtp_sendmail does not work.
Is xp_smtp_sendmail does not work then the solution you recommend does not
work, because it is simply a development of SQL Mail.
If I used your solution it would fail, because xp_smtp_sendmail does not
work.
|||Nicolas Verhaeghe wrote:
>My solution is specifically when xp_smtp_sendmail does not work.
>Is xp_smtp_sendmail does not work then the solution you recommend does not
>work, because it is simply a development of SQL Mail.
>If I used your solution it would fail, because xp_smtp_sendmail does not
>work.
You are confusing xp_sendmail, and xp_smtp_sendmail, I think.
xp_sendmail relies on SQL Mail.
xp_smtp_sendmail is a 3rd-party alternative to xp_sendmail and does *not*
rely on either SQL Mail or MAPI.
Steve Foster [SBS MVP]
MVPs do not work for Microsoft. Please reply only to the newsgroups.
|||Then I am confusing and I apologize. I guess two solutions are better than
one.
because Outlook cannot be installed when Exchange is present (usually the
case with SBS) and therefore no MAPI profile is available for SQL Mail.
I use the following stored procedure. It creates a CDO object (sorry,
redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
application.
Feel free to comment and improve if you want. One thing I cannot do is
change the importance (low, normal, high) of the email. I'd like to know how
to do this... It's actually harder than it seems.
-- Switch CREATE to ALTER if needed
CREATE PROCEDURE dbo.sp_sendSMTPmail
(
@.To varchar(8000) = null,
@.Subject varchar(255) = null,
@.Body text = null,
@.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
@.Cc varchar(8000) = null,
@.Bcc varchar(8000) = null,
@.Attachments varchar(8000) = null, -- delimeter is ;
@.HTMLFormat int = 0,
@.From varchar(255) = null
)
AS
-- Declare
DECLARE @.message int
DECLARE @.config int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
object
EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
configuration object
-- Configuration Object
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)', 'localhost' --
SMTP Server
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
Server SMTP Port
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @.config, 'Fields.Update'
-- Message Object
EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
message.configuration = config
EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
IF (@.From Is Not Null) AND (@.From Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
END
ELSE BEGIN
-- Modify the following to fit your needs
EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
END
IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
END
IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
END
IF (@.HTMLFormat = 1)
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
END
ELSE BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
END
-- The full path to the attachments has to be provided: 'c:/path/myfile.txt'
IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
END
EXEC sp_OAMethod @.message, 'Send()'
-- Destroys the objects
EXEC @.hr = sp_OADestroy @.message
EXEC @.hr = sp_OADestroy @.config
-- Errorhandler
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
GO
/* Test below - Replace @.email addresses with real addresses to test
EXEC dbo.sp_sendSMTPmail
@.To='recipient@.email.com',
@.Subject='This is a test',
@.Body='HTML Body',
@.Cc='',
@.Bcc='',
@.Attachments='',
@.HTMLFormat=1,
@.From='sender@.email.com'
*/
Not to step on your solution, but I have found that xpsmtp from
www.sqldev.net works very well as a smtp connector for SQL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314abac$0$32207$39cecf19@.news.twtelecom.net. ..
> It seems that most of the SBS admins have had the same problem with SQL
> Mail because Outlook cannot be installed when Exchange is present (usually
> the case with SBS) and therefore no MAPI profile is available for SQL
> Mail.
> I use the following stored procedure. It creates a CDO object (sorry,
> redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
> application.
> Feel free to comment and improve if you want. One thing I cannot do is
> change the importance (low, normal, high) of the email. I'd like to know
> how to do this... It's actually harder than it seems.
> -- Switch CREATE to ALTER if needed
> CREATE PROCEDURE dbo.sp_sendSMTPmail
> (
> @.To varchar(8000) = null,
> @.Subject varchar(255) = null,
> @.Body text = null,
> @.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
> @.Cc varchar(8000) = null,
> @.Bcc varchar(8000) = null,
> @.Attachments varchar(8000) = null, -- delimeter is ;
> @.HTMLFormat int = 0,
> @.From varchar(255) = null
> )
> AS
> -- Declare
> DECLARE @.message int
> DECLARE @.config int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
> object
> EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
> configuration object
> -- Configuration Object
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
> 'cdoSendUsingPort' -- Send the message using the network
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)',
> 'localhost' -- SMTP Server
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
> Server SMTP Port
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
> 'cdoAnonymous' -- Anonymous SMTP Authenticate
> EXEC sp_OAMethod @.config, 'Fields.Update'
>
> -- Message Object
> EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
> message.configuration = config
> EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
> EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
>
> IF (@.From Is Not Null) AND (@.From Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
> END
> ELSE BEGIN
> -- Modify the following to fit your needs
> EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
> END
> IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
> END
> IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
> END
> IF (@.HTMLFormat = 1)
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
> END
> ELSE BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
> END
> -- The full path to the attachments has to be provided:
> 'c:/path/myfile.txt'
> IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
> END
>
> EXEC sp_OAMethod @.message, 'Send()'
> -- Destroys the objects
> EXEC @.hr = sp_OADestroy @.message
> EXEC @.hr = sp_OADestroy @.config
> -- Errorhandler
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> GO
>
> /* Test below - Replace @.email addresses with real addresses to test
> EXEC dbo.sp_sendSMTPmail
> @.To='recipient@.email.com',
> @.Subject='This is a test',
> @.Body='HTML Body',
> @.Cc='',
> @.Bcc='',
> @.Attachments='',
> @.HTMLFormat=1,
> @.From='sender@.email.com'
> */
>
|||It uses xp_smtp_sendmail wich is not available without a MAPI profile.
Your "solution" is not really a solution, it is simply a development over
SQL Mail which, in my case, and that of others, does not work.
|||You are incorrect. xp_smtp_sendmail does not use a MAPI profile, nor does
it require that Outlook be installed. If you read teh documentation on
www.sqldev.net you will see that while it is not a complete, drop-in
replacement, it does allow an outbound email path without using Outlook. I
have used this tool for several years now and have had zero problems. I
also do not have Outlook installed on any server where I use this add-on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314bda0$0$32203$39cecf19@.news.twtelecom.net. ..
> It uses xp_smtp_sendmail wich is not available without a MAPI profile.
> Your "solution" is not really a solution, it is simply a development over
> SQL Mail which, in my case, and that of others, does not work.
>
|||My solution is specifically when xp_smtp_sendmail does not work.
Is xp_smtp_sendmail does not work then the solution you recommend does not
work, because it is simply a development of SQL Mail.
If I used your solution it would fail, because xp_smtp_sendmail does not
work.
|||Nicolas Verhaeghe wrote:
>My solution is specifically when xp_smtp_sendmail does not work.
>Is xp_smtp_sendmail does not work then the solution you recommend does not
>work, because it is simply a development of SQL Mail.
>If I used your solution it would fail, because xp_smtp_sendmail does not
>work.
You are confusing xp_sendmail, and xp_smtp_sendmail, I think.
xp_sendmail relies on SQL Mail.
xp_smtp_sendmail is a 3rd-party alternative to xp_sendmail and does *not*
rely on either SQL Mail or MAPI.
Steve Foster [SBS MVP]
MVPs do not work for Microsoft. Please reply only to the newsgroups.
|||Then I am confusing and I apologize. I guess two solutions are better than
one.
My Solution for SQL Mail on SBS
It seems that most of the SBS admins have had the same problem with SQL Mail
because Outlook cannot be installed when Exchange is present (usually the
case with SBS) and therefore no MAPI profile is available for SQL Mail.
I use the following stored procedure. It creates a CDO object (sorry,
redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
application.
Feel free to comment and improve if you want. One thing I cannot do is
change the importance (low, normal, high) of the email. I'd like to know how
to do this... It's actually harder than it seems.
-- Switch CREATE to ALTER if needed
CREATE PROCEDURE dbo.sp_sendSMTPmail
(
@.To varchar(8000) = null,
@.Subject varchar(255) = null,
@.Body text = null,
@.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
@.Cc varchar(8000) = null,
@.Bcc varchar(8000) = null,
@.Attachments varchar(8000) = null, -- delimeter is ;
@.HTMLFormat int = 0,
@.From varchar(255) = null
)
AS
-- Declare
DECLARE @.message int
DECLARE @.config int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
object
EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
configuration object
-- Configuration Object
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)', 'localhost' --
SMTP Server
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
Server SMTP Port
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @.config, 'Fields.Update'
-- Message Object
EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
message.configuration = config
EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
IF (@.From Is Not Null) AND (@.From Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
END
ELSE BEGIN
-- Modify the following to fit your needs
EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
END
IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
END
IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
END
IF (@.HTMLFormat = 1)
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
END
ELSE BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
END
-- The full path to the attachments has to be provided: 'c:/path/myfile.txt'
IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
END
EXEC sp_OAMethod @.message, 'Send()'
-- Destroys the objects
EXEC @.hr = sp_OADestroy @.message
EXEC @.hr = sp_OADestroy @.config
-- Errorhandler
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
GO
/* Test below - Replace @.email addresses with real addresses to test
EXEC dbo.sp_sendSMTPmail
@.To='recipient@.email.com',
@.Subject='This is a test',
@.Body='HTML Body',
@.Cc='',
@.Bcc='',
@.Attachments='',
@.HTMLFormat=1,
@.From='sender@.email.com'
*/Not to step on your solution, but I have found that xpsmtp from
www.sqldev.net works very well as a smtp connector for SQL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas XXXXXXXXX" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314abac$0$32207$39cecf19@.news.twtelecom.net...
> It seems that most of the SBS admins have had the same problem with SQL
> Mail because Outlook cannot be installed when Exchange is present (usually
> the case with SBS) and therefore no MAPI profile is available for SQL
> Mail.
> I use the following stored procedure. It creates a CDO object (sorry,
> redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
> application.
> Feel free to comment and improve if you want. One thing I cannot do is
> change the importance (low, normal, high) of the email. I'd like to know
> how to do this... It's actually harder than it seems.
> -- Switch CREATE to ALTER if needed
> CREATE PROCEDURE dbo.sp_sendSMTPmail
> (
> @.To varchar(8000) = null,
> @.Subject varchar(255) = null,
> @.Body text = null,
> @.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
> @.Cc varchar(8000) = null,
> @.Bcc varchar(8000) = null,
> @.Attachments varchar(8000) = null, -- delimeter is ;
> @.HTMLFormat int = 0,
> @.From varchar(255) = null
> )
> AS
> -- Declare
> DECLARE @.message int
> DECLARE @.config int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
> object
> EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
> configuration object
> -- Configuration Object
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
> 'cdoSendUsingPort' -- Send the message using the network
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)',
> 'localhost' -- SMTP Server
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
> Server SMTP Port
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
> 'cdoAnonymous' -- Anonymous SMTP Authenticate
> EXEC sp_OAMethod @.config, 'Fields.Update'
>
> -- Message Object
> EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
> message.configuration = config
> EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
> EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
>
> IF (@.From Is Not Null) AND (@.From Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
> END
> ELSE BEGIN
> -- Modify the following to fit your needs
> EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
> END
> IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
> END
> IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
> END
> IF (@.HTMLFormat = 1)
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
> END
> ELSE BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
> END
> -- The full path to the attachments has to be provided:
> 'c:/path/myfile.txt'
> IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
> END
>
> EXEC sp_OAMethod @.message, 'Send()'
> -- Destroys the objects
> EXEC @.hr = sp_OADestroy @.message
> EXEC @.hr = sp_OADestroy @.config
> -- Errorhandler
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> GO
>
> /* Test below - Replace @.email addresses with real addresses to test
> EXEC dbo.sp_sendSMTPmail
> @.To='recipient@.email.com',
> @.Subject='This is a test',
> @.Body='HTML Body',
> @.Cc='',
> @.Bcc='',
> @.Attachments='',
> @.HTMLFormat=1,
> @.From='sender@.email.com'
> */
>|||It uses xp_smtp_sendmail wich is not available without a MAPI profile.
Your "solution" is not really a solution, it is simply a development over
SQL Mail which, in my case, and that of others, does not work.|||You are incorrect. xp_smtp_sendmail does not use a MAPI profile, nor does
it require that Outlook be installed. If you read teh documentation on
www.sqldev.net you will see that while it is not a complete, drop-in
replacement, it does allow an outbound email path without using Outlook. I
have used this tool for several years now and have had zero problems. I
also do not have Outlook installed on any server where I use this add-on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas XXXXXXXXX" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314bda0$0$32203$39cecf19@.news.twtelecom.net...
> It uses xp_smtp_sendmail wich is not available without a MAPI profile.
> Your "solution" is not really a solution, it is simply a development over
> SQL Mail which, in my case, and that of others, does not work.
>|||My solution is specifically when xp_smtp_sendmail does not work.
Is xp_smtp_sendmail does not work then the solution you recommend does not
work, because it is simply a development of SQL Mail.
If I used your solution it would fail, because xp_smtp_sendmail does not
work.|||Nicolas XXXXXXXXX wrote:
>My solution is specifically when xp_smtp_sendmail does not work.
>Is xp_smtp_sendmail does not work then the solution you recommend does not
>work, because it is simply a development of SQL Mail.
>If I used your solution it would fail, because xp_smtp_sendmail does not
>work.
You are confusing xp_sendmail, and xp_smtp_sendmail, I think.
xp_sendmail relies on SQL Mail.
xp_smtp_sendmail is a 3rd-party alternative to xp_sendmail and does *not*
rely on either SQL Mail or MAPI.
Steve Foster [SBS MVP]
---
MVPs do not work for Microsoft. Please reply only to the newsgroups.|||Then I am confusing and I apologize. I guess two solutions are better than
one.
because Outlook cannot be installed when Exchange is present (usually the
case with SBS) and therefore no MAPI profile is available for SQL Mail.
I use the following stored procedure. It creates a CDO object (sorry,
redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
application.
Feel free to comment and improve if you want. One thing I cannot do is
change the importance (low, normal, high) of the email. I'd like to know how
to do this... It's actually harder than it seems.
-- Switch CREATE to ALTER if needed
CREATE PROCEDURE dbo.sp_sendSMTPmail
(
@.To varchar(8000) = null,
@.Subject varchar(255) = null,
@.Body text = null,
@.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
@.Cc varchar(8000) = null,
@.Bcc varchar(8000) = null,
@.Attachments varchar(8000) = null, -- delimeter is ;
@.HTMLFormat int = 0,
@.From varchar(255) = null
)
AS
-- Declare
DECLARE @.message int
DECLARE @.config int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
object
EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
configuration object
-- Configuration Object
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)', 'localhost' --
SMTP Server
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
Server SMTP Port
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @.config, 'Fields.Update'
-- Message Object
EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
message.configuration = config
EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
IF (@.From Is Not Null) AND (@.From Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
END
ELSE BEGIN
-- Modify the following to fit your needs
EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
END
IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
END
IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
END
IF (@.HTMLFormat = 1)
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
END
ELSE BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
END
-- The full path to the attachments has to be provided: 'c:/path/myfile.txt'
IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
END
EXEC sp_OAMethod @.message, 'Send()'
-- Destroys the objects
EXEC @.hr = sp_OADestroy @.message
EXEC @.hr = sp_OADestroy @.config
-- Errorhandler
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
GO
/* Test below - Replace @.email addresses with real addresses to test
EXEC dbo.sp_sendSMTPmail
@.To='recipient@.email.com',
@.Subject='This is a test',
@.Body='HTML Body',
@.Cc='',
@.Bcc='',
@.Attachments='',
@.HTMLFormat=1,
@.From='sender@.email.com'
*/Not to step on your solution, but I have found that xpsmtp from
www.sqldev.net works very well as a smtp connector for SQL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas XXXXXXXXX" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314abac$0$32207$39cecf19@.news.twtelecom.net...
> It seems that most of the SBS admins have had the same problem with SQL
> Mail because Outlook cannot be installed when Exchange is present (usually
> the case with SBS) and therefore no MAPI profile is available for SQL
> Mail.
> I use the following stored procedure. It creates a CDO object (sorry,
> redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
> application.
> Feel free to comment and improve if you want. One thing I cannot do is
> change the importance (low, normal, high) of the email. I'd like to know
> how to do this... It's actually harder than it seems.
> -- Switch CREATE to ALTER if needed
> CREATE PROCEDURE dbo.sp_sendSMTPmail
> (
> @.To varchar(8000) = null,
> @.Subject varchar(255) = null,
> @.Body text = null,
> @.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
> @.Cc varchar(8000) = null,
> @.Bcc varchar(8000) = null,
> @.Attachments varchar(8000) = null, -- delimeter is ;
> @.HTMLFormat int = 0,
> @.From varchar(255) = null
> )
> AS
> -- Declare
> DECLARE @.message int
> DECLARE @.config int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
> object
> EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
> configuration object
> -- Configuration Object
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
> 'cdoSendUsingPort' -- Send the message using the network
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)',
> 'localhost' -- SMTP Server
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
> Server SMTP Port
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
> 'cdoAnonymous' -- Anonymous SMTP Authenticate
> EXEC sp_OAMethod @.config, 'Fields.Update'
>
> -- Message Object
> EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
> message.configuration = config
> EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
> EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
>
> IF (@.From Is Not Null) AND (@.From Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
> END
> ELSE BEGIN
> -- Modify the following to fit your needs
> EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
> END
> IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
> END
> IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
> END
> IF (@.HTMLFormat = 1)
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
> END
> ELSE BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
> END
> -- The full path to the attachments has to be provided:
> 'c:/path/myfile.txt'
> IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
> END
>
> EXEC sp_OAMethod @.message, 'Send()'
> -- Destroys the objects
> EXEC @.hr = sp_OADestroy @.message
> EXEC @.hr = sp_OADestroy @.config
> -- Errorhandler
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> GO
>
> /* Test below - Replace @.email addresses with real addresses to test
> EXEC dbo.sp_sendSMTPmail
> @.To='recipient@.email.com',
> @.Subject='This is a test',
> @.Body='HTML Body',
> @.Cc='',
> @.Bcc='',
> @.Attachments='',
> @.HTMLFormat=1,
> @.From='sender@.email.com'
> */
>|||It uses xp_smtp_sendmail wich is not available without a MAPI profile.
Your "solution" is not really a solution, it is simply a development over
SQL Mail which, in my case, and that of others, does not work.|||You are incorrect. xp_smtp_sendmail does not use a MAPI profile, nor does
it require that Outlook be installed. If you read teh documentation on
www.sqldev.net you will see that while it is not a complete, drop-in
replacement, it does allow an outbound email path without using Outlook. I
have used this tool for several years now and have had zero problems. I
also do not have Outlook installed on any server where I use this add-on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas XXXXXXXXX" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314bda0$0$32203$39cecf19@.news.twtelecom.net...
> It uses xp_smtp_sendmail wich is not available without a MAPI profile.
> Your "solution" is not really a solution, it is simply a development over
> SQL Mail which, in my case, and that of others, does not work.
>|||My solution is specifically when xp_smtp_sendmail does not work.
Is xp_smtp_sendmail does not work then the solution you recommend does not
work, because it is simply a development of SQL Mail.
If I used your solution it would fail, because xp_smtp_sendmail does not
work.|||Nicolas XXXXXXXXX wrote:
>My solution is specifically when xp_smtp_sendmail does not work.
>Is xp_smtp_sendmail does not work then the solution you recommend does not
>work, because it is simply a development of SQL Mail.
>If I used your solution it would fail, because xp_smtp_sendmail does not
>work.
You are confusing xp_sendmail, and xp_smtp_sendmail, I think.
xp_sendmail relies on SQL Mail.
xp_smtp_sendmail is a 3rd-party alternative to xp_sendmail and does *not*
rely on either SQL Mail or MAPI.
Steve Foster [SBS MVP]
---
MVPs do not work for Microsoft. Please reply only to the newsgroups.|||Then I am confusing and I apologize. I guess two solutions are better than
one.
My Solution for SQL Mail on SBS
It seems that most of the SBS admins have had the same problem with SQL Mail
because Outlook cannot be installed when Exchange is present (usually the
case with SBS) and therefore no MAPI profile is available for SQL Mail.
I use the following stored procedure. It creates a CDO object (sorry,
redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
application.
Feel free to comment and improve if you want. One thing I cannot do is
change the importance (low, normal, high) of the email. I'd like to know how
to do this... It's actually harder than it seems.
-- Switch CREATE to ALTER if needed
CREATE PROCEDURE dbo.sp_sendSMTPmail
(
@.To varchar(8000) = null,
@.Subject varchar(255) = null,
@.Body text = null,
@.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
@.Cc varchar(8000) = null,
@.Bcc varchar(8000) = null,
@.Attachments varchar(8000) = null, -- delimeter is ;
@.HTMLFormat int = 0,
@.From varchar(255) = null
)
AS
-- Declare
DECLARE @.message int
DECLARE @.config int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
object
EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
configuration object
-- Configuration Object
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)', 'localhost' --
SMTP Server
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
Server SMTP Port
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @.config, 'Fields.Update'
-- Message Object
EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
message.configuration = config
EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
IF (@.From Is Not Null) AND (@.From Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
END
ELSE BEGIN
-- Modify the following to fit your needs
EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
END
IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
END
IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
END
IF (@.HTMLFormat = 1)
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
END
ELSE BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
END
-- The full path to the attachments has to be provided: 'c:/path/myfile.txt'
IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
END
EXEC sp_OAMethod @.message, 'Send()'
-- Destroys the objects
EXEC @.hr = sp_OADestroy @.message
EXEC @.hr = sp_OADestroy @.config
-- Errorhandler
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
GO
/* Test below - Replace @.email addresses with real addresses to test
EXEC dbo.sp_sendSMTPmail
@.To='recipient@.email.com',
@.Subject='This is a test',
@.Body='HTML Body',
@.Cc='',
@.Bcc='',
@.Attachments='',
@.HTMLFormat=1,
@.From='sender@.email.com'
*/Not to step on your solution, but I have found that xpsmtp from
www.sqldev.net works very well as a smtp connector for SQL.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314abac$0$32207$39cecf19@.news.twtelecom.net...
> It seems that most of the SBS admins have had the same problem with SQL
> Mail because Outlook cannot be installed when Exchange is present (usually
> the case with SBS) and therefore no MAPI profile is available for SQL
> Mail.
> I use the following stored procedure. It creates a CDO object (sorry,
> redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
> application.
> Feel free to comment and improve if you want. One thing I cannot do is
> change the importance (low, normal, high) of the email. I'd like to know
> how to do this... It's actually harder than it seems.
> -- Switch CREATE to ALTER if needed
> CREATE PROCEDURE dbo.sp_sendSMTPmail
> (
> @.To varchar(8000) = null,
> @.Subject varchar(255) = null,
> @.Body text = null,
> @.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
> @.Cc varchar(8000) = null,
> @.Bcc varchar(8000) = null,
> @.Attachments varchar(8000) = null, -- delimeter is ;
> @.HTMLFormat int = 0,
> @.From varchar(255) = null
> )
> AS
> -- Declare
> DECLARE @.message int
> DECLARE @.config int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
> object
> EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
> configuration object
> -- Configuration Object
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
> 'cdoSendUsingPort' -- Send the message using the network
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)',
> 'localhost' -- SMTP Server
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
> Server SMTP Port
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
> 'cdoAnonymous' -- Anonymous SMTP Authenticate
> EXEC sp_OAMethod @.config, 'Fields.Update'
>
> -- Message Object
> EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
> message.configuration = config
> EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
> EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
>
> IF (@.From Is Not Null) AND (@.From Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
> END
> ELSE BEGIN
> -- Modify the following to fit your needs
> EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
> END
> IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
> END
> IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
> END
> IF (@.HTMLFormat = 1)
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
> END
> ELSE BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
> END
> -- The full path to the attachments has to be provided:
> 'c:/path/myfile.txt'
> IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
> END
>
> EXEC sp_OAMethod @.message, 'Send()'
> -- Destroys the objects
> EXEC @.hr = sp_OADestroy @.message
> EXEC @.hr = sp_OADestroy @.config
> -- Errorhandler
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> GO
>
> /* Test below - Replace @.email addresses with real addresses to test
> EXEC dbo.sp_sendSMTPmail
> @.To='recipient@.email.com',
> @.Subject='This is a test',
> @.Body='HTML Body',
> @.Cc='',
> @.Bcc='',
> @.Attachments='',
> @.HTMLFormat=1,
> @.From='sender@.email.com'
> */
>|||It uses xp_smtp_sendmail wich is not available without a MAPI profile.
Your "solution" is not really a solution, it is simply a development over
SQL Mail which, in my case, and that of others, does not work.|||You are incorrect. xp_smtp_sendmail does not use a MAPI profile, nor does
it require that Outlook be installed. If you read teh documentation on
www.sqldev.net you will see that while it is not a complete, drop-in
replacement, it does allow an outbound email path without using Outlook. I
have used this tool for several years now and have had zero problems. I
also do not have Outlook installed on any server where I use this add-on.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314bda0$0$32203$39cecf19@.news.twtelecom.net...
> It uses xp_smtp_sendmail wich is not available without a MAPI profile.
> Your "solution" is not really a solution, it is simply a development over
> SQL Mail which, in my case, and that of others, does not work.
>|||My solution is specifically when xp_smtp_sendmail does not work.
Is xp_smtp_sendmail does not work then the solution you recommend does not
work, because it is simply a development of SQL Mail.
If I used your solution it would fail, because xp_smtp_sendmail does not
work.|||Nicolas Verhaeghe wrote:
>My solution is specifically when xp_smtp_sendmail does not work.
>Is xp_smtp_sendmail does not work then the solution you recommend does not
>work, because it is simply a development of SQL Mail.
>If I used your solution it would fail, because xp_smtp_sendmail does not
>work.
You are confusing xp_sendmail, and xp_smtp_sendmail, I think.
xp_sendmail relies on SQL Mail.
xp_smtp_sendmail is a 3rd-party alternative to xp_sendmail and does *not*
rely on either SQL Mail or MAPI.
--
Steve Foster [SBS MVP]
---
MVPs do not work for Microsoft. Please reply only to the newsgroups.|||Then I am confusing and I apologize. I guess two solutions are better than
one.
because Outlook cannot be installed when Exchange is present (usually the
case with SBS) and therefore no MAPI profile is available for SQL Mail.
I use the following stored procedure. It creates a CDO object (sorry,
redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
application.
Feel free to comment and improve if you want. One thing I cannot do is
change the importance (low, normal, high) of the email. I'd like to know how
to do this... It's actually harder than it seems.
-- Switch CREATE to ALTER if needed
CREATE PROCEDURE dbo.sp_sendSMTPmail
(
@.To varchar(8000) = null,
@.Subject varchar(255) = null,
@.Body text = null,
@.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
@.Cc varchar(8000) = null,
@.Bcc varchar(8000) = null,
@.Attachments varchar(8000) = null, -- delimeter is ;
@.HTMLFormat int = 0,
@.From varchar(255) = null
)
AS
-- Declare
DECLARE @.message int
DECLARE @.config int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
object
EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
configuration object
-- Configuration Object
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)', 'localhost' --
SMTP Server
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
Server SMTP Port
EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @.config, 'Fields.Update'
-- Message Object
EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
message.configuration = config
EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
IF (@.From Is Not Null) AND (@.From Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
END
ELSE BEGIN
-- Modify the following to fit your needs
EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
END
IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
END
IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
END
IF (@.HTMLFormat = 1)
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
END
ELSE BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
END
-- The full path to the attachments has to be provided: 'c:/path/myfile.txt'
IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
BEGIN
EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
END
EXEC sp_OAMethod @.message, 'Send()'
-- Destroys the objects
EXEC @.hr = sp_OADestroy @.message
EXEC @.hr = sp_OADestroy @.config
-- Errorhandler
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
GO
/* Test below - Replace @.email addresses with real addresses to test
EXEC dbo.sp_sendSMTPmail
@.To='recipient@.email.com',
@.Subject='This is a test',
@.Body='HTML Body',
@.Cc='',
@.Bcc='',
@.Attachments='',
@.HTMLFormat=1,
@.From='sender@.email.com'
*/Not to step on your solution, but I have found that xpsmtp from
www.sqldev.net works very well as a smtp connector for SQL.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314abac$0$32207$39cecf19@.news.twtelecom.net...
> It seems that most of the SBS admins have had the same problem with SQL
> Mail because Outlook cannot be installed when Exchange is present (usually
> the case with SBS) and therefore no MAPI profile is available for SQL
> Mail.
> I use the following stored procedure. It creates a CDO object (sorry,
> redundancy) and it works pretty much like in an ASP(x) page or a VB(net)
> application.
> Feel free to comment and improve if you want. One thing I cannot do is
> change the importance (low, normal, high) of the email. I'd like to know
> how to do this... It's actually harder than it seems.
> -- Switch CREATE to ALTER if needed
> CREATE PROCEDURE dbo.sp_sendSMTPmail
> (
> @.To varchar(8000) = null,
> @.Subject varchar(255) = null,
> @.Body text = null,
> @.Importance int = 1, -- 0=low, 1=normal, 2=high -- Does not work yet!
> @.Cc varchar(8000) = null,
> @.Bcc varchar(8000) = null,
> @.Attachments varchar(8000) = null, -- delimeter is ;
> @.HTMLFormat int = 0,
> @.From varchar(255) = null
> )
> AS
> -- Declare
> DECLARE @.message int
> DECLARE @.config int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'CDO.Message', @.message OUT -- create the message
> object
> EXEC @.hr = sp_OACreate 'CDO.Configuration', @.config OUT -- create the
> configuration object
> -- Configuration Object
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSendUsingMethod)',
> 'cdoSendUsingPort' -- Send the message using the network
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServer)',
> 'localhost' -- SMTP Server
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPServerPort)', 25 --
> Server SMTP Port
> EXEC @.hr = sp_OASetProperty @.config, 'Fields(cdoSMTPAuthenticate)',
> 'cdoAnonymous' -- Anonymous SMTP Authenticate
> EXEC sp_OAMethod @.config, 'Fields.Update'
>
> -- Message Object
> EXEC @.hr = sp_OASetProperty @.message, 'Configuration', @.config -- set
> message.configuration = config
> EXEC @.hr = sp_OASetProperty @.message, 'To', @.To
> EXEC @.hr = sp_OASetProperty @.message, 'Subject', @.Subject
>
> IF (@.From Is Not Null) AND (@.From Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'From', @.From
> END
> ELSE BEGIN
> -- Modify the following to fit your needs
> EXEC @.hr = sp_OASetProperty @.message, 'From', 'defaultsend@.email.com'
> END
> IF (@.Cc Is Not Null) AND (@.Cc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'CC', @.Cc
> END
> IF (@.Bcc Is Not Null) AND (@.Bcc Like '%@.%')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'BCC', @.Bcc
> END
> IF (@.HTMLFormat = 1)
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'HTMLBody', @.Body
> END
> ELSE BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'TextBody', @.Body
> END
> -- The full path to the attachments has to be provided:
> 'c:/path/myfile.txt'
> IF (@.Attachments Is Not Null) AND (@.Attachments <> '')
> BEGIN
> EXEC @.hr = sp_OASetProperty @.message, 'AddAttachment', @.Attachments
> END
>
> EXEC sp_OAMethod @.message, 'Send()'
> -- Destroys the objects
> EXEC @.hr = sp_OADestroy @.message
> EXEC @.hr = sp_OADestroy @.config
> -- Errorhandler
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.message, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> GO
>
> /* Test below - Replace @.email addresses with real addresses to test
> EXEC dbo.sp_sendSMTPmail
> @.To='recipient@.email.com',
> @.Subject='This is a test',
> @.Body='HTML Body',
> @.Cc='',
> @.Bcc='',
> @.Attachments='',
> @.HTMLFormat=1,
> @.From='sender@.email.com'
> */
>|||It uses xp_smtp_sendmail wich is not available without a MAPI profile.
Your "solution" is not really a solution, it is simply a development over
SQL Mail which, in my case, and that of others, does not work.|||You are incorrect. xp_smtp_sendmail does not use a MAPI profile, nor does
it require that Outlook be installed. If you read teh documentation on
www.sqldev.net you will see that while it is not a complete, drop-in
replacement, it does allow an outbound email path without using Outlook. I
have used this tool for several years now and have had zero problems. I
also do not have Outlook installed on any server where I use this add-on.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nicolas Verhaeghe" <nospam_nicver@.yahoo.com_nospam> wrote in message
news:4314bda0$0$32203$39cecf19@.news.twtelecom.net...
> It uses xp_smtp_sendmail wich is not available without a MAPI profile.
> Your "solution" is not really a solution, it is simply a development over
> SQL Mail which, in my case, and that of others, does not work.
>|||My solution is specifically when xp_smtp_sendmail does not work.
Is xp_smtp_sendmail does not work then the solution you recommend does not
work, because it is simply a development of SQL Mail.
If I used your solution it would fail, because xp_smtp_sendmail does not
work.|||Nicolas Verhaeghe wrote:
>My solution is specifically when xp_smtp_sendmail does not work.
>Is xp_smtp_sendmail does not work then the solution you recommend does not
>work, because it is simply a development of SQL Mail.
>If I used your solution it would fail, because xp_smtp_sendmail does not
>work.
You are confusing xp_sendmail, and xp_smtp_sendmail, I think.
xp_sendmail relies on SQL Mail.
xp_smtp_sendmail is a 3rd-party alternative to xp_sendmail and does *not*
rely on either SQL Mail or MAPI.
--
Steve Foster [SBS MVP]
---
MVPs do not work for Microsoft. Please reply only to the newsgroups.|||Then I am confusing and I apologize. I guess two solutions are better than
one.
Subscribe to:
Posts (Atom)