Any reason to avoid SQL Mail on a Production Server?

We are supporting a SQL Server 2000 (sp3) / ASP application.
The application is extremely poorly written (deadlock, timeout, stored
procs longer than my kids Christmas lists...) and I would like to have
SQL Mail installed to get various information emailed to us when one
of these events occur (through alert).
I meet stiff resistance from the Production guys but without proper
reason.
Before I put my head on the line... Is there any reason we should be
scared to install SQL Mail on a Production box?
One developer told me that it used to hang their production SQL
Server... (but that may have been SQL Server 6.5)
Thanks
EricEric,
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
>
I would not necessarily view long stored proc as a bad thing. If the task
needs a long stored proc, so be it. It will perform better than breaking it
down to shorter ones.
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
Though not scared, we did see some problem with Outlook:
1. When a process involves Outlook mail, and the mail server or client is
down, the process hangs (at least we saw this on SQL7).
2. Not whether a general problem or server specific: we saw that a SQL7
server's service be restarted by a too long mail recipient value.
We have the notifications you are considering on most of our servers. Just
mention this for your consideration to be prepared to talk to your
counterparts in other groups.
hth
Quentin|||Well, you'll need to run SQL under a domain user account -- not necessarily
a domain admin if you set all the appropriate permissions, but without a
domain user, there's no way to hook to an Exchange mailbox.
You'll also need to install a MAPI-compliant email program on the server
(preferably Outlook). Yet anothe application on a production server means
another point of hacking attacks.
In my experience, SQL Mail and SQL Agent Mail (they are two different
things) don't crash a server, but there are circumstances where they'll
stop working. If they try to send an email while the Exchange server is
offline or unreachable, that particular MAPI session can get messed up, but
remains in memory. You need to stop and start SQL Mail/SQL Agent Mail to
get it working. There are some ways around this like including a .pst file
in the profile you use for mail, but that too has issues.
Another option you may want to look into is Gert Drapers SMTP-based email
(www.sqldev.net). The only thing installed is an extended stored procedure,
it doesn't run into the problems MAPI can create, and you don't need to set
SQL to run under a specific account. The only limitation is that you can
use it for the integrated alert system.
Hope that helps.
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message
news:11269dcb.0307250628.35979849@.posting.google.com...
> We are supporting a SQL Server 2000 (sp3) / ASP application.
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
> I meet stiff resistance from the Production guys but without proper
> reason.
> Before I put my head on the line... Is there any reason we should be
> scared to install SQL Mail on a Production box?
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
> Thanks
>
> Eric|||> The only limitation is that you can
> use it for the integrated alert system.
I assume you meant "can't"...
To get around this limitation, I simply add a step to jobs I need alerts
for:
step 1
call some sql
if succeeds, quit with success
if fails, goto step 2
step 2
call xp_smtp_sendmail to send alert
-- so that the failure of step 1 is correctly reflected in sys:
if succeeds, quit with failure
if fails, quit with failure|||I think I'll try to get them to install Outlook.
Our SQL Server and SQL Agent services already run under domain
accounts.
Otherwise I know there are ActiveX objects available to send mail on
our production machine but it would be far less convenient than
xp_sendmail.
Thanks for your comments
Eric

Any other way of dealing w/ logical corruption in MS SQL Server 2K

Hi,
Is there any other way of dealing with so called "logical corruptions" in MS
SQL Server 2000 SP3, other than transactional replication?
-- Many thanks, OskarWhat is a logical corruption? A modification that you want to undo? Or some corruption (broken page)
than you want to clean?
Anyhow, perhaps log shipping can be an option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:79B18C1C-27B4-4A85-B0EC-D68A399723FC@.microsoft.com...
> Hi,
> Is there any other way of dealing with so called "logical corruptions" in MS
> SQL Server 2000 SP3, other than transactional replication?
> -- Many thanks, Oskar
>|||I guess you mean "If someone deletes something unintentionally \ not
deliberately...".
Tibor' s suggestion is good. You may want to use Log Shipping. Which backs
up your transaction log at your production SQL Server and then copies it to
your Secondary SQL Server and then restores them on this server. Set about 1
hour latency before restoring or copying your transaction logs to the
Secondary SQL Server, so that, you will be able to save your data that
damages if you realize this mistake in one hour. Or, you could change that
intervals according to your needs.
For more information about Log Shipping:
http://msdn2.microsoft.com/en-us/library/ms190016.aspx
--
Ekrem Ã?nsoy
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:79B18C1C-27B4-4A85-B0EC-D68A399723FC@.microsoft.com...
> Hi,
> Is there any other way of dealing with so called "logical corruptions" in
> MS
> SQL Server 2000 SP3, other than transactional replication?
> -- Many thanks, Oskar
>|||(MS SQL Server 2000 SP3)
No, I didn't mean user errors. Apparently, in rare cases, a "logical"
corruption could be a byproduct of "physical" corruption e.g. disk or IO
driver malfunction. The logical corruption, for example, supposedly could
happen if a DML statement is passed column values that were calculated based
on "physically" corrupt column values and the DML statement succeeds i.e. is
logged in the transaction log (and possibly shipped subsequently).
When I come to think of it again, this probably means that such corruptions
(i.e. corruptions that can be transfered by means of log shipping) actually
won't be detectable by DBCC CHECKDB on the secondary log shipping server, or
is this all nonsense? Maybe Michael Hotek has something to say in this regard?
-- Many thanks, (bewildered) Oskar
"Tibor Karaszi" wrote:
> What is a logical corruption? A modification that you want to undo? Or some corruption (broken page)
> than you want to clean?
> Anyhow, perhaps log shipping can be an option?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:79B18C1C-27B4-4A85-B0EC-D68A399723FC@.microsoft.com...
> > Hi,
> > Is there any other way of dealing with so called "logical corruptions" in MS
> > SQL Server 2000 SP3, other than transactional replication?
> >
> > -- Many thanks, Oskar
> >
>|||Oskar,
I (think) I understand what you are getting at. A physical corruption creeps up so it affects
something else in some undesired way. This "something else" is not a physical corruption, it is a
"valid value in SQL Server". I think that such is probably impossible to protect yourself from. If a
physical corruption makes a row having the value 1 instead of 2, and 1 is inside the domain for that
selected datatype, then whatever mean you use to "replicate" this database to somewhere else will
not detect this as a corruption. It is a valid value (according to SQL Server after all).
We enter a grey area if we add constraints to the discussion. Say that you have a CHECK constraint
saying col = 2, but the corruption result in col = 1, then I believe that replication will not allow
that row (assuming that you have that check constraint on the destination table), where log shipping
will allow that value.
Above gets pretty abstract, and I think that it is very unlikely that you will encounter this
situation. Especially if you do your DBCC CHECKDB *and* act ASAP if that finds the physical
corruption. Perhaps Paul Randall has more to say about this, so you might want to hunt down his blog
to see...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:1E24A00B-05E5-451A-8B1F-830155A9F4A5@.microsoft.com...
> (MS SQL Server 2000 SP3)
> No, I didn't mean user errors. Apparently, in rare cases, a "logical"
> corruption could be a byproduct of "physical" corruption e.g. disk or IO
> driver malfunction. The logical corruption, for example, supposedly could
> happen if a DML statement is passed column values that were calculated based
> on "physically" corrupt column values and the DML statement succeeds i.e. is
> logged in the transaction log (and possibly shipped subsequently).
> When I come to think of it again, this probably means that such corruptions
> (i.e. corruptions that can be transfered by means of log shipping) actually
> won't be detectable by DBCC CHECKDB on the secondary log shipping server, or
> is this all nonsense? Maybe Michael Hotek has something to say in this regard?
> -- Many thanks, (bewildered) Oskar
> "Tibor Karaszi" wrote:
>> What is a logical corruption? A modification that you want to undo? Or some corruption (broken
>> page)
>> than you want to clean?
>> Anyhow, perhaps log shipping can be an option?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
>> news:79B18C1C-27B4-4A85-B0EC-D68A399723FC@.microsoft.com...
>> > Hi,
>> > Is there any other way of dealing with so called "logical corruptions" in MS
>> > SQL Server 2000 SP3, other than transactional replication?
>> >
>> > -- Many thanks, Oskar
>> >|||I do run CHECKDB on a daily basis. Though in this particular case I suppose
I'll have to rely on SQL Server's on-the-spot corruption detection logic.
"Tibor Karaszi" wrote:
> Oskar,
> I (think) I understand what you are getting at. A physical corruption creeps up so it affects
> something else in some undesired way. This "something else" is not a physical corruption, it is a
> "valid value in SQL Server". I think that such is probably impossible to protect yourself from. If a
> physical corruption makes a row having the value 1 instead of 2, and 1 is inside the domain for that
> selected datatype, then whatever mean you use to "replicate" this database to somewhere else will
> not detect this as a corruption. It is a valid value (according to SQL Server after all).
> We enter a grey area if we add constraints to the discussion. Say that you have a CHECK constraint
> saying col = 2, but the corruption result in col = 1, then I believe that replication will not allow
> that row (assuming that you have that check constraint on the destination table), where log shipping
> will allow that value.
> Above gets pretty abstract, and I think that it is very unlikely that you will encounter this
> situation. Especially if you do your DBCC CHECKDB *and* act ASAP if that finds the physical
> corruption. Perhaps Paul Randall has more to say about this, so you might want to hunt down his blog
> to see...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:1E24A00B-05E5-451A-8B1F-830155A9F4A5@.microsoft.com...
> > (MS SQL Server 2000 SP3)
> >
> > No, I didn't mean user errors. Apparently, in rare cases, a "logical"
> > corruption could be a byproduct of "physical" corruption e.g. disk or IO
> > driver malfunction. The logical corruption, for example, supposedly could
> > happen if a DML statement is passed column values that were calculated based
> > on "physically" corrupt column values and the DML statement succeeds i.e. is
> > logged in the transaction log (and possibly shipped subsequently).
> >
> > When I come to think of it again, this probably means that such corruptions
> > (i.e. corruptions that can be transfered by means of log shipping) actually
> > won't be detectable by DBCC CHECKDB on the secondary log shipping server, or
> > is this all nonsense? Maybe Michael Hotek has something to say in this regard?
> >
> > -- Many thanks, (bewildered) Oskar
> >
> > "Tibor Karaszi" wrote:
> >
> >> What is a logical corruption? A modification that you want to undo? Or some corruption (broken
> >> page)
> >> than you want to clean?
> >>
> >> Anyhow, perhaps log shipping can be an option?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> >> news:79B18C1C-27B4-4A85-B0EC-D68A399723FC@.microsoft.com...
> >> > Hi,
> >> > Is there any other way of dealing with so called "logical corruptions" in MS
> >> > SQL Server 2000 SP3, other than transactional replication?
> >> >
> >> > -- Many thanks, Oskar
> >> >
> >>
>
>

Any Issues With Windows 2003 SP1?

Hi,
Does anybody know of any issues with Windows 2003 SP1?
We have a Standard SQL Server 2000 with SP3 running on Windows 2003.
Lee Aholima
DBA and IT Support
YES!
http://support.microsoft.com/default...b;en-us;899599
"dayfive" <dayfive@.discussions.microsoft.com> wrote in message
news:6FA46E11-7EB7-4EBC-9160-51411A87A8F3@.microsoft.com...
> Hi,
> Does anybody know of any issues with Windows 2003 SP1?
> We have a Standard SQL Server 2000 with SP3 running on Windows 2003.
> --
> Lee Aholima
> DBA and IT Support
>

Any Issues With Windows 2003 SP1?

Hi,
Does anybody know of any issues with Windows 2003 SP1?
We have a Standard SQL Server 2000 with SP3 running on Windows 2003.
Lee Aholima
DBA and IT SupportYES!
http://support.microsoft.com/defaul...kb;en-us;899599
"dayfive" <dayfive@.discussions.microsoft.com> wrote in message
news:6FA46E11-7EB7-4EBC-9160-51411A87A8F3@.microsoft.com...
> Hi,
> Does anybody know of any issues with Windows 2003 SP1?
> We have a Standard SQL Server 2000 with SP3 running on Windows 2003.
> --
> Lee Aholima
> DBA and IT Support
>

Any Issues With Windows 2003 SP1?

Hi,
Does anybody know of any issues with Windows 2003 SP1?
We have a Standard SQL Server 2000 with SP3 running on Windows 2003.
--
Lee Aholima
DBA and IT SupportYES!
http://support.microsoft.com/default.aspx?scid=kb;en-us;899599
"dayfive" <dayfive@.discussions.microsoft.com> wrote in message
news:6FA46E11-7EB7-4EBC-9160-51411A87A8F3@.microsoft.com...
> Hi,
> Does anybody know of any issues with Windows 2003 SP1?
> We have a Standard SQL Server 2000 with SP3 running on Windows 2003.
> --
> Lee Aholima
> DBA and IT Support
>

Any harm in changing nullability while a default exists for a colu

Hi,
In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
changing the table column nullability via the ALTER TABLE ALTER COLUMN while
a default is also defined for that column? If yes, what are they?
--
Many thanks,
OskarYou'll need to drop the default constraint before ALTER TABLE...ALTER COLUMN
and re-add the constraint afterwards.
Column nullability doesn't affect the behavior of the default constraint so
I'm not aware of any drawbacks. The default value is used only when the
column is not included in the column list of an INSERT statement. Of
course, an explicit NULL value will be permitted only when the column allows
nulls.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
> Hi,
> In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
> changing the table column nullability via the ALTER TABLE ALTER COLUMN
> while
> a default is also defined for that column? If yes, what are they?
> --
> Many thanks,
> Oskar
>|||Yes, but when I needed to change a column from NULL to NOT NULL in one of the
tables and a default was defined on that column, the SQL Server parser didn't
complain about that. It allowed me to change the definition without any
warnings. Does this mean that I've done it in the wrong way? If yes, why?
--
Many thanks,
Oskar
"Dan Guzman" wrote:
> You'll need to drop the default constraint before ALTER TABLE...ALTER COLUMN
> and re-add the constraint afterwards.
> Column nullability doesn't affect the behavior of the default constraint so
> I'm not aware of any drawbacks. The default value is used only when the
> column is not included in the column list of an INSERT statement. Of
> course, an explicit NULL value will be permitted only when the column allows
> nulls.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
> > Hi,
> > In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
> > changing the table column nullability via the ALTER TABLE ALTER COLUMN
> > while
> > a default is also defined for that column? If yes, what are they?
> >
> > --
> > Many thanks,
> > Oskar
> >
> >
>
>|||No, Oskar, you didn't do anything wrong. My comment about dropping and
re-adding the constraint applies when changing the column data type but not
when changing only column nullability. Sorry for the confusion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:3049DDD6-BA64-4A17-BEB6-7B34B9C68D80@.microsoft.com...
> Yes, but when I needed to change a column from NULL to NOT NULL in one of
> the
> tables and a default was defined on that column, the SQL Server parser
> didn't
> complain about that. It allowed me to change the definition without any
> warnings. Does this mean that I've done it in the wrong way? If yes, why?
> --
> Many thanks,
> Oskar
> "Dan Guzman" wrote:
>> You'll need to drop the default constraint before ALTER TABLE...ALTER
>> COLUMN
>> and re-add the constraint afterwards.
>> Column nullability doesn't affect the behavior of the default constraint
>> so
>> I'm not aware of any drawbacks. The default value is used only when the
>> column is not included in the column list of an INSERT statement. Of
>> course, an explicit NULL value will be permitted only when the column
>> allows
>> nulls.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
>> news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
>> > Hi,
>> > In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
>> > changing the table column nullability via the ALTER TABLE ALTER COLUMN
>> > while
>> > a default is also defined for that column? If yes, what are they?
>> >
>> > --
>> > Many thanks,
>> > Oskar
>> >
>> >
>>