alerting for severity levels and not through SQL Alerts

Right now, we do not use the SQL Server Alerts since we
do not have an Outlook profile setup on our SQL Servers.
So is there any other way, i could be alerted for Sev
levels 17-25 thru some sproc that i can call
periodically ?
Also is there a way one can implement this with Site
scope tool ?
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
One option is mentioned at:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?
|||You can still use alerts if you want. You could have the alert start a SQL
Agent job when it is fired that sends a email via CDOSYS of CDONTS. Check
out the KB article:
http://support.microsoft.com/default...b;EN-US;312839
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?
sql

alerting for severity levels and not through SQL Alerts

Right now, we do not use the SQL Server Alerts since we
do not have an Outlook profile setup on our SQL Servers.
So is there any other way, i could be alerted for Sev
levels 17-25 thru some sproc that i can call
periodically ?
Also is there a way one can implement this with Site
scope tool ?> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
One option is mentioned at:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?|||You can still use alerts if you want. You could have the alert start a SQL
Agent job when it is fired that sends a email via CDOSYS of CDONTS. Check
out the KB article:
http://support.microsoft.com/defaul...kb;EN-US;312839
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?

alerting for severity levels and not through SQL Alerts

Right now, we do not use the SQL Server Alerts since we
do not have an Outlook profile setup on our SQL Servers.
So is there any other way, i could be alerted for Sev
levels 17-25 thru some sproc that i can call
periodically ?
Also is there a way one can implement this with Site
scope tool ?> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
One option is mentioned at:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?|||You can still use alerts if you want. You could have the alert start a SQL
Agent job when it is fired that sends a email via CDOSYS of CDONTS. Check
out the KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;312839
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?

Alert setup

I setup Outlook to use a POP3 client. I can send and
recieve mail no problem. I can even open up SQL Server
Agent and define it to use the mail profile I setup and it
start and stops the MAPI session no problem. I define an
operator and click test it says it sent it successfully
however I don't get the message. When I stop the agent and
open Outlook 2000 I get this:
Your message did not reach some or all of the intended
recipients.
Subject:Microsoft SQL Server Alert System Test
Sent:7/20/2004 6:47 PM
The following recipient(s) could not be reached:
'me@.mycompany.com' on 7/20/2004 6:47 PM
No transport provider was available for
delivery to this recipient.
Does anyone know what is the problem here. I can still
send email to myself from this account.
Hi
You can try to log on to the server with the account that runs
Outlook/SQLAgent/SQLServer and then delete the Outlook profile. Then try
setting up a new profile. Sometimes a profile gets corrupted thus causing
strange errors.
I've come across this problem once in a while over the years when an
external program are trying to send an email via Outlook. I've never really
found the definate solution or work around - usualy it has been a bit of
playing around.You should also check that you have setup your mail account
correctly i.e. dosen't have any options that has to be chosen by the user
when sending an email.
I also think that I once read that Outlook needs Outlook Express to be
installed - otherwise you can get this error message. I think this was an
issue with Outlook2000 but I don't know if it's still the case with newer
versions.
Regards
Steen
"adba" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:0f6e01c46ec5$7e99ae30$a501280a@.phx.gbl...
> I setup Outlook to use a POP3 client. I can send and
> recieve mail no problem. I can even open up SQL Server
> Agent and define it to use the mail profile I setup and it
> start and stops the MAPI session no problem. I define an
> operator and click test it says it sent it successfully
> however I don't get the message. When I stop the agent and
> open Outlook 2000 I get this:
> Your message did not reach some or all of the intended
> recipients.
> Subject: Microsoft SQL Server Alert System Test
> Sent: 7/20/2004 6:47 PM
> The following recipient(s) could not be reached:
> 'me@.mycompany.com' on 7/20/2004 6:47 PM
> No transport provider was available for
> delivery to this recipient.
> Does anyone know what is the problem here. I can still
> send email to myself from this account.

Alert setup

I setup Outlook to use a POP3 client. I can send and
recieve mail no problem. I can even open up SQL Server
Agent and define it to use the mail profile I setup and it
start and stops the MAPI session no problem. I define an
operator and click test it says it sent it successfully
however I don't get the message. When I stop the agent and
open Outlook 2000 I get this:
Your message did not reach some or all of the intended
recipients.
Subject: Microsoft SQL Server Alert System Test
Sent: 7/20/2004 6:47 PM
The following recipient(s) could not be reached:
'me@.mycompany.com' on 7/20/2004 6:47 PM
No transport provider was available for
delivery to this recipient.
Does anyone know what is the problem here. I can still
send email to myself from this account.Hi
You can try to log on to the server with the account that runs
Outlook/SQLAgent/SQLServer and then delete the Outlook profile. Then try
setting up a new profile. Sometimes a profile gets corrupted thus causing
strange errors.
I've come across this problem once in a while over the years when an
external program are trying to send an email via Outlook. I've never really
found the definate solution or work around - usualy it has been a bit of
playing around.You should also check that you have setup your mail account
correctly i.e. dosen't have any options that has to be chosen by the user
when sending an email.
I also think that I once read that Outlook needs Outlook Express to be
installed - otherwise you can get this error message. I think this was an
issue with Outlook2000 but I don't know if it's still the case with newer
versions.
Regards
Steen
"adba" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:0f6e01c46ec5$7e99ae30$a501280a@.phx.gbl...
> I setup Outlook to use a POP3 client. I can send and
> recieve mail no problem. I can even open up SQL Server
> Agent and define it to use the mail profile I setup and it
> start and stops the MAPI session no problem. I define an
> operator and click test it says it sent it successfully
> however I don't get the message. When I stop the agent and
> open Outlook 2000 I get this:
> Your message did not reach some or all of the intended
> recipients.
> Subject: Microsoft SQL Server Alert System Test
> Sent: 7/20/2004 6:47 PM
> The following recipient(s) could not be reached:
> 'me@.mycompany.com' on 7/20/2004 6:47 PM
> No transport provider was available for
> delivery to this recipient.
> Does anyone know what is the problem here. I can still
> send email to myself from this account.

Alert setup

I setup Outlook to use a POP3 client. I can send and
recieve mail no problem. I can even open up SQL Server
Agent and define it to use the mail profile I setup and it
start and stops the MAPI session no problem. I define an
operator and click test it says it sent it successfully
however I don't get the message. When I stop the agent and
open Outlook 2000 I get this:
Your message did not reach some or all of the intended
recipients.
Subject: Microsoft SQL Server Alert System Test
Sent: 7/20/2004 6:47 PM
The following recipient(s) could not be reached:
'me@.mycompany.com' on 7/20/2004 6:47 PM
No transport provider was available for
delivery to this recipient.
Does anyone know what is the problem here. I can still
send email to myself from this account.Hi
You can try to log on to the server with the account that runs
Outlook/SQLAgent/SQLServer and then delete the Outlook profile. Then try
setting up a new profile. Sometimes a profile gets corrupted thus causing
strange errors.
I've come across this problem once in a while over the years when an
external program are trying to send an email via Outlook. I've never really
found the definate solution or work around - usualy it has been a bit of
playing around.You should also check that you have setup your mail account
correctly i.e. dosen't have any options that has to be chosen by the user
when sending an email.
I also think that I once read that Outlook needs Outlook Express to be
installed - otherwise you can get this error message. I think this was an
issue with Outlook2000 but I don't know if it's still the case with newer
versions.
Regards
Steen
"adba" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:0f6e01c46ec5$7e99ae30$a501280a@.phx.gbl...
> I setup Outlook to use a POP3 client. I can send and
> recieve mail no problem. I can even open up SQL Server
> Agent and define it to use the mail profile I setup and it
> start and stops the MAPI session no problem. I define an
> operator and click test it says it sent it successfully
> however I don't get the message. When I stop the agent and
> open Outlook 2000 I get this:
> Your message did not reach some or all of the intended
> recipients.
> Subject: Microsoft SQL Server Alert System Test
> Sent: 7/20/2004 6:47 PM
> The following recipient(s) could not be reached:
> 'me@.mycompany.com' on 7/20/2004 6:47 PM
> No transport provider was available for
> delivery to this recipient.
> Does anyone know what is the problem here. I can still
> send email to myself from this account.

AgentMail Problem (SQL2K)

Hi,
I use Microsoft outlook as MAPI client. On my own computer, when the outlook
is open, both SQL Mail and Agent Mail can use the file of mail profile to
send email. But on one of client's computer, when outlook is open, it locks
the file of profile and both SQL Mail and Agent Mail fail when try to
connect to profile. When I close outlook, they can access profile. This is
strange behavior that I had never seen elsewhere.
Any help would be greatly appreciated.
Leila
I believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen the
strange behavior with later versions). It might also be related to whether all users use the same
Windows account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail fail
> when try to connect to profile. When I close outlook, they can access profile. This is strange
> behavior that I had never seen elsewhere.
> Any help would be greatly appreciated.
> Leila
>
|||> It might also be related to whether all users use the same Windows
> account.
If this is the case, then closing the outlook must not solve the problem I
think
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>I believe this behavior can depend on your version of Outlook (I prefer
>Outlook 2000, I've seen the strange behavior with later versions). It might
>also be related to whether all users use the same Windows account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||I think that some versions of MAPI (Outlook) doesn't like several Windows accounts using mail at the
same time. Say that Agent and SQL Server uses the same service account. They might not conflict, but
if you are logged in interactively using some other account, you might be in for some problems.
Anyhow, I recommend Outlook 2000. Or actually, I don't recommend SQL Mail at all
(http://www.karaszi.com/SQLServer/info_no_mapi.asp), since it causes so many of these problems...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OLQuVxOjHHA.2552@.TK2MSFTNGP06.phx.gbl...
> If this is the case, then closing the outlook must not solve the problem I think
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>

AgentMail Problem (SQL2K)

Hi,
I use Microsoft outlook as MAPI client. On my own computer, when the outlook
is open, both SQL Mail and Agent Mail can use the file of mail profile to
send email. But on one of client's computer, when outlook is open, it locks
the file of profile and both SQL Mail and Agent Mail fail when try to
connect to profile. When I close outlook, they can access profile. This is
strange behavior that I had never seen elsewhere.
Any help would be greatly appreciated.
LeilaI believe this behavior can depend on your version of Outlook (I prefer Outl
ook 2000, I've seen the
strange behavior with later versions). It might also be related to whether a
ll users use the same
Windows account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...[v
bcol=seagreen]
> Hi,
> I use Microsoft outlook as MAPI client. On my own computer, when the outlo
ok is open, both SQL
> Mail and Agent Mail can use the file of mail profile to send email. But on
one of client's
> computer, when outlook is open, it locks the file of profile and both SQL
Mail and Agent Mail fail
> when try to connect to profile. When I close outlook, they can access prof
ile. This is strange
> behavior that I had never seen elsewhere.
> Any help would be greatly appreciated.
> Leila
>[/vbcol]|||> It might also be related to whether all users use the same Windows
> account.
If this is the case, then closing the outlook must not solve the problem I
think
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>I believe this behavior can depend on your version of Outlook (I prefer
>Outlook 2000, I've seen the strange behavior with later versions). It might
>also be related to whether all users use the same Windows account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>|||I think that some versions of MAPI (Outlook) doesn't like several Windows ac
counts using mail at the
same time. Say that Agent and SQL Server uses the same service account. They
might not conflict, but
if you are logged in interactively using some other account, you might be in
for some problems.
Anyhow, I recommend Outlook 2000. Or actually, I don't recommend SQL Mail at
all
(http://www.karaszi.com/SQLServer/info_no_mapi.asp), since it causes so many
of these problems...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OLQuVxOjHHA.2552@.TK2MSFTNGP06.phx.gbl...[v
bcol=seagreen]
> If this is the case, then closing the outlook must not solve the problem I
think
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>[/vbcol]

AgentMail Problem (SQL2K)

Hi,
I use Microsoft outlook as MAPI client. On my own computer, when the outlook
is open, both SQL Mail and Agent Mail can use the file of mail profile to
send email. But on one of client's computer, when outlook is open, it locks
the file of profile and both SQL Mail and Agent Mail fail when try to
connect to profile. When I close outlook, they can access profile. This is
strange behavior that I had never seen elsewhere.
Any help would be greatly appreciated.
LeilaI believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen the
strange behavior with later versions). It might also be related to whether all users use the same
Windows account.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail fail
> when try to connect to profile. When I close outlook, they can access profile. This is strange
> behavior that I had never seen elsewhere.
> Any help would be greatly appreciated.
> Leila
>|||> It might also be related to whether all users use the same Windows
> account.
If this is the case, then closing the outlook must not solve the problem I
think
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>I believe this behavior can depend on your version of Outlook (I prefer
>Outlook 2000, I've seen the strange behavior with later versions). It might
>also be related to whether all users use the same Windows account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I use Microsoft outlook as MAPI client. On my own computer, when the
>> outlook is open, both SQL Mail and Agent Mail can use the file of mail
>> profile to send email. But on one of client's computer, when outlook is
>> open, it locks the file of profile and both SQL Mail and Agent Mail fail
>> when try to connect to profile. When I close outlook, they can access
>> profile. This is strange behavior that I had never seen elsewhere.
>> Any help would be greatly appreciated.
>> Leila
>|||I think that some versions of MAPI (Outlook) doesn't like several Windows accounts using mail at the
same time. Say that Agent and SQL Server uses the same service account. They might not conflict, but
if you are logged in interactively using some other account, you might be in for some problems.
Anyhow, I recommend Outlook 2000. Or actually, I don't recommend SQL Mail at all
(http://www.karaszi.com/SQLServer/info_no_mapi.asp), since it causes so many of these problems...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OLQuVxOjHHA.2552@.TK2MSFTNGP06.phx.gbl...
>> It might also be related to whether all users use the same Windows account.
> If this is the case, then closing the outlook must not solve the problem I think
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>>I believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen
>>the strange behavior with later versions). It might also be related to whether all users use the
>>same Windows account.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
>> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
>> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail
>> fail when try to connect to profile. When I close outlook, they can access profile. This is
>> strange behavior that I had never seen elsewhere.
>> Any help would be greatly appreciated.
>> Leila
>>
>

agent mail / outlook 2003 catch 22

I've just installed and configured outlook 2003 with sql agent mail and I've come across a strange conundrum: messages from agent mail are not actually sent unless the outlook client is open, but the default mail folder is locked by agent when agent is started, thus preventing outlook 2003 from opening. If I try things the other way - opening the client and then configuring/restarting agent mail - then agent mail can't access the default mail folder because the client has it locked.

There doesn't seem to be any way to win here.

Anyone come across this issue and found a workaround?I don't have a solution for your specific problem. I use this:

http://sqldev.net/xp/xpsmtp.htm

which doesn't require an email client on the server at all. Perhaps it would work in your situation.|||Deleted duplicate post.|||If I recall correctly, you have to configure SQL Agent Mail using the Outlook 2000 Client.

Here are some links:

MS FAQ (http://support.microsoft.com/default.aspx?scid=kb;en-us;311231)
Common Problems (http://support.microsoft.com/default.aspx?scid=kb;en-us;315886)
Configuring SQL Mail (http://support.microsoft.com/default.aspx?scid=kb;en-us;263556)

Regards,

hmscott|||Thanks, guys. It appears that Outlook 2003 is not compatible with Agent Mail (though it's fine with SQL Mail).

For anyone else who runs into this problem, I've thrown together a stored procedure which, when scheduled into an hourly job, will email you when a job fails along with the cause of the failure. I'm sure I could have made this neater, but it works and that's all I need. An example of the error email message is as follows:

--Original Message--
From: sender@.mydomain.com
Sent: Tuesday, January 31, 2006 5:04 PM
To: me@.mydomain.com
Subject: Jobs failed in the last hour - Feb 01 2006 10:00AM

The jobs listed below failed in the last hour:

Job: testjob
Step: 1
Step Name: test
Date Failed: Feb 1 2006 9:31AM
Error: Executed as user: SERVERS\Administrator. Invalid object name 'zigglyzorp'. [SQLSTATE 42S02] (Error 208). The step failed.
--sp attached as txt file--

Advice requested : Choosing the best Key for a table

I would appreciate good advice about the following Schema for a Table called
Messages. The table will hold archived messages from Microsoft Outlook. The
SQL 2005 schema that I have posted, is simply what the data looks like in
it's *raw* form. I intend to modify the schema in the most efficient way
possible. This table was created using MS SQL server 2005 and uses two if
the new (MAX) data types.
You can help me the most by focusing on the best Key for the Messages table.
The "natural key" for the table (in raw form), would be MessageID, so lets
discuss it's problems. The first problem is that varbinary(MAX) (new in
SQL2005), does not allow Primary Key constraints, and so it should be!
Obviously we will need to use a smaller DataType than varbinary(MAX), once
you pick something reasonable in size you are allowed to create PrimaryKey
constraint. MessageID's do not have a documented or published limit, they
can be *any* length. From my observations MessageID will typically be 70-340
bytes. So I suppose we could set varbinary(500). This would allow us to
handle most situations. But this is still way too long for a Primary Key in
my opinion. The other columns that are set as xxx(MAX) have similar
problems, ignore these columns for now until we find a decent Key for the
table.
In my opinion this Table does not have a "Natural Key" we can use it's
simply to long, and this table will have "hundreds of millions of rows", so
I am worried about JOIN performance. Once a message is archived (then
deleted), from it's original location and placed in SQL server, the
MessageID has no value and is never displayed to the user. There are more
complications because this table is replicated using (Merge replication).
The database is a distributed database, so using auto identity columns
complicate replication issues, not too mention irritating --Celko--. So I
really want to find a suitable surrogate key.
So what to do?
1. Use RSA to reduce MessageID to 20 bytes, and deal with the collisions?
2. Use a rowGuid (Outch!)
At this point I like option #1 the best because it works nicely with
"replication" as the original MessageID is a Globally Unique Identifier, so
no two MessageID's (PR_ENTRY_ID long term) will ever exist. But I think
there is are issues with generating these shorter keys using RSA, collisions
will happen. Additionally it is quite simple to encode the MessageID using
RSA. The MessageID would now be 20 bytes or [varbinary](20).
Does anyone have any good suggestions?
Thanks for your time.
Russell Mangel
Las Vegas, NV
-- Note: I commented out the Constraint, as varbinary(MAX) is not allowed.
-- This schema is just what the data looks like in the real world.
CREATE TABLE
[dbo].[Messages]
(
[MessageID] [varbinary](MAX) NOT NULL,
[ParentID] [varbinary](MAX) NOT NULL, -- FK for Folders Table
[SenderID] [int] NOT NULL, -- FK for Sender of message
[MessageType] [int] NOT NULL, -- FK MessageType
[Length] [bigint] NOT NULL,
[To] [nvarchar](MAX) NOT NULL,
[CC] [nvarchar](MAX) NOT NULL,
[Subject] [nvarchar](MAX) NOT NULL,
[Body] [ntext] NOT NULL,
[HasAttachment] [bit] NOT NULL,
-- CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
-- (
-- [MessageID]
-- )
)ON [PRIMARY]
-- End SchemaHi, Russel
Well, why do you want the MessageId column to be varbinary datatype? Any
Reason?
Sure, I don't know your business requirements so my guess is (if you do not
want to use an IDENTITYT)
BTW an IDENTITY in most cases is a good candidate for surrogate keys
CREATE TABLE Messages
(
MessageId INT NOT NULL PRIMARY KEY
)
Then you will need to generate a next MessageId by your self like
DECLARE @.MaxId INT
BEGIN TRAN
SELECT @.MaxId =COALESCE(MAX(MessageId),0) FROM Messages WITH
(UPDLOCK,HOLDLOCK)
INSERT INTO Messages SELECT @.MaxId ,....... FROM Table
COMMIT TRAN
"Russell Mangel" <russell@.tymer.net> wrote in message
news:%23JD8adcaGHA.3304@.TK2MSFTNGP04.phx.gbl...
>I would appreciate good advice about the following Schema for a Table
>called Messages. The table will hold archived messages from Microsoft
>Outlook. The SQL 2005 schema that I have posted, is simply what the data
>looks like in it's *raw* form. I intend to modify the schema in the most
>efficient way possible. This table was created using MS SQL server 2005 and
>uses two if the new (MAX) data types.
> You can help me the most by focusing on the best Key for the Messages
> table.
> The "natural key" for the table (in raw form), would be MessageID, so lets
> discuss it's problems. The first problem is that varbinary(MAX) (new in
> SQL2005), does not allow Primary Key constraints, and so it should be!
> Obviously we will need to use a smaller DataType than varbinary(MAX), once
> you pick something reasonable in size you are allowed to create PrimaryKey
> constraint. MessageID's do not have a documented or published limit, they
> can be *any* length. From my observations MessageID will typically be
> 70-340 bytes. So I suppose we could set varbinary(500). This would allow
> us to handle most situations. But this is still way too long for a Primary
> Key in my opinion. The other columns that are set as xxx(MAX) have similar
> problems, ignore these columns for now until we find a decent Key for the
> table.
> In my opinion this Table does not have a "Natural Key" we can use it's
> simply to long, and this table will have "hundreds of millions of rows",
> so I am worried about JOIN performance. Once a message is archived (then
> deleted), from it's original location and placed in SQL server, the
> MessageID has no value and is never displayed to the user. There are more
> complications because this table is replicated using (Merge replication).
> The database is a distributed database, so using auto identity columns
> complicate replication issues, not too mention irritating --Celko--. So I
> really want to find a suitable surrogate key.
> So what to do?
> 1. Use RSA to reduce MessageID to 20 bytes, and deal with the collisions?
> 2. Use a rowGuid (Outch!)
> At this point I like option #1 the best because it works nicely with
> "replication" as the original MessageID is a Globally Unique Identifier,
> so no two MessageID's (PR_ENTRY_ID long term) will ever exist. But I think
> there is are issues with generating these shorter keys using RSA,
> collisions will happen. Additionally it is quite simple to encode the
> MessageID using RSA. The MessageID would now be 20 bytes or
> [varbinary](20).
> Does anyone have any good suggestions?
> Thanks for your time.
> Russell Mangel
> Las Vegas, NV
>
> -- Note: I commented out the Constraint, as varbinary(MAX) is not
> allowed.
> -- This schema is just what the data looks like in the real world.
> CREATE TABLE
> [dbo].[Messages]
> (
> [MessageID] [varbinary](MAX) NOT NULL,
> [ParentID] [varbinary](MAX) NOT NULL, -- FK for Folders Table
> [SenderID] [int] NOT NULL, -- FK for Sender of message
> [MessageType] [int] NOT NULL, -- FK MessageType
> [Length] [bigint] NOT NULL,
> [To] [nvarchar](MAX) NOT NULL,
> [CC] [nvarchar](MAX) NOT NULL,
> [Subject] [nvarchar](MAX) NOT NULL,
> [Body] [ntext] NOT NULL,
> [HasAttachment] [bit] NOT NULL,
> -- CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
> -- (
> -- [MessageID]
> -- )
> )ON [PRIMARY]
> -- End Schema
>|||Can MessageID ever change for a given message? If not, then use it as the
natural key (as silly as this name sounds in this case). Put a unique
constraint on it, but create a surrogate key for joins.
Although everybody else will say that the GUID is a poor choice for a
primary key, I'd say use it but NEVER as a clustered key. Perhaps you'd have
more use for a clustered index on a datetime column (although I can't see on
e
in your proposed schema). Maybe you could also use an IDENTITY column - it
makes a great
candidate for a clustered index.
Of course a better option still would be to do more research and find a
better natural key (maybe even composite) candidate, then use a single colum
n
surrogate key for joins and references.
ML
http://milambda.blogspot.com/|||There is 1 other Column in the Messages table "Created" it's a DateTime,
that might help us find a better key.
Not sure what to call this Key as it has two columns, must be
Composite/Natural?
Is it possible for a person (SenderID), to send a message at the exact same
time?
Hmmmmmmm. Got to think about this for a bit, and maybe I can find another
column that I can use for Composite key.
-- Here is the new Schema,
CREATE TABLE
[dbo].[Messages]
(
[MessageID] [varbinary](max) NOT NULL,
[ParentID] [varbinary](max) NOT NULL,
[SenderID] [int] NOT NULL,
[MessageType] [int] NOT NULL,
[Length] [bigint] NOT NULL,
[To] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CC] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Subject] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HasAttachment] [bit] NOT NULL,
[Created] [datetime] NOT NULL, -- New Column
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[SenderID] ASC,
[Created] ASC
)
)ON [PRIMARY]|||Russell Mangel wrote:
> I would appreciate good advice about the following Schema for a Table call
ed
> Messages. The table will hold archived messages from Microsoft Outlook. Th
e
> SQL 2005 schema that I have posted, is simply what the data looks like in
> it's *raw* form. I intend to modify the schema in the most efficient way
> possible. This table was created using MS SQL server 2005 and uses two if
> the new (MAX) data types.
> You can help me the most by focusing on the best Key for the Messages tabl
e.
> The "natural key" for the table (in raw form), would be MessageID, so lets
> discuss it's problems. The first problem is that varbinary(MAX) (new in
> SQL2005), does not allow Primary Key constraints, and so it should be!
> Obviously we will need to use a smaller DataType than varbinary(MAX), once
> you pick something reasonable in size you are allowed to create PrimaryKey
> constraint. MessageID's do not have a documented or published limit, they
> can be *any* length. From my observations MessageID will typically be 70-3
40
> bytes. So I suppose we could set varbinary(500). This would allow us to
> handle most situations. But this is still way too long for a Primary Key i
n
> my opinion. The other columns that are set as xxx(MAX) have similar
> problems, ignore these columns for now until we find a decent Key for the
> table.
> In my opinion this Table does not have a "Natural Key" we can use it's
> simply to long, and this table will have "hundreds of millions of rows", s
o
> I am worried about JOIN performance. Once a message is archived (then
> deleted), from it's original location and placed in SQL server, the
> MessageID has no value and is never displayed to the user. There are more
> complications because this table is replicated using (Merge replication).
> The database is a distributed database, so using auto identity columns
> complicate replication issues, not too mention irritating --Celko--. So I
> really want to find a suitable surrogate key.
> So what to do?
> 1. Use RSA to reduce MessageID to 20 bytes, and deal with the collisions?
> 2. Use a rowGuid (Outch!)
> At this point I like option #1 the best because it works nicely with
> "replication" as the original MessageID is a Globally Unique Identifier, s
o
> no two MessageID's (PR_ENTRY_ID long term) will ever exist. But I think
> there is are issues with generating these shorter keys using RSA, collisio
ns
> will happen. Additionally it is quite simple to encode the MessageID using
> RSA. The MessageID would now be 20 bytes or [varbinary](20).
> Does anyone have any good suggestions?
> Thanks for your time.
> Russell Mangel
> Las Vegas, NV
>
> -- Note: I commented out the Constraint, as varbinary(MAX) is not allowed
.
> -- This schema is just what the data looks like in the real world.
> CREATE TABLE
> [dbo].[Messages]
> (
> [MessageID] [varbinary](MAX) NOT NULL,
> [ParentID] [varbinary](MAX) NOT NULL, -- FK for Folders Table
> [SenderID] [int] NOT NULL, -- FK for Sender of message
> [MessageType] [int] NOT NULL, -- FK MessageType
> [Length] [bigint] NOT NULL,
> [To] [nvarchar](MAX) NOT NULL,
> [CC] [nvarchar](MAX) NOT NULL,
> [Subject] [nvarchar](MAX) NOT NULL,
> [Body] [ntext] NOT NULL,
> [HasAttachment] [bit] NOT NULL,
> -- CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
> -- (
> -- [MessageID]
> -- )
> )ON [PRIMARY]
> -- End Schema
You can add a constraint on MessageID like this:
CREATE FUNCTION dbo.ufn_sha_hash
(@.msg VARBINARY(8000))
RETURNS VARBINARY(20)
AS
BEGIN
RETURN
ISNULL(HASHBYTES('SHA1',@.msg),0x7C7CDB42
7449446E8EA812C5981C5D6A) ;
END
GO
CREATE TABLE dbo.Messages
(
MessageID varbinary(8000) NOT NULL,
HashID varbinary(20) NOT NULL PRIMARY KEY,
CHECK (HashID = dbo.ufn_sha_hash(MessageID)),
);
GO
Whether you should also use a surrogate key is a different question. It
would probably make sense to do so. Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> CREATE FUNCTION dbo.ufn_sha_hash
> (@.msg VARBINARY(8000))
> RETURNS VARBINARY(20)
> AS
> BEGIN
> RETURN
> ISNULL(HASHBYTES('SHA1',@.msg),0x7C7CDB42
7449446E8EA812C5981C5D6A) ;
> END
> GO
> CREATE TABLE dbo.Messages
> (
> MessageID varbinary(8000) NOT NULL,
> HashID varbinary(20) NOT NULL PRIMARY KEY,
> CHECK (HashID = dbo.ufn_sha_hash(MessageID)),
> );
> GO
> Whether you should also use a surrogate key is a different question. It
> would probably make sense to do so. Hope this helps.
> --
> David Portas, SQL Server MVP
I didn't know that SQL could do SHA1 hashes, thanks. This table is a
replicated table so once you add surrogate keys, you have to deal with that.
Personally I would rather have a binary(20) column which replaces MessageID
column completely than deal with the surrogate key problems with Replicated
(Merge) databases.
The Messages table also has a FK (FolderID) for Folders table, so this
column could also be removed and replaced by a binary(20) column. Folders
Table PK (FolderID)would be replaced with SHA1 binary(20) as well.
Which brings a couple questions:
1. If we are going to store an SHA1 hash, wouldn't it be best to use:
binary(20) instead of varbinary(20)?
2. Will I have any collisions (PK violations) with SHA1 hash (more on this
in next sentence)?
The MessageID is a variable length Binary byte array, and is a Globally
UniqueIdentifier (it's just an older form of GUID before GUID was born) this
MessageID is from MAPI (Exchange Server). So if you had 50 million
varbinary(MAX) MessageIDs, and you ran SHA1 hash against all of them would
you find duplicated SHA1 hash values?
Thanks
Russell Mangel|||Russell Mangel wrote:
> I didn't know that SQL could do SHA1 hashes, thanks. This table is a
> replicated table so once you add surrogate keys, you have to deal with tha
t.
> Personally I would rather have a binary(20) column which replaces Message
ID
> column completely than deal with the surrogate key problems with Replicate
d
> (Merge) databases.
> The Messages table also has a FK (FolderID) for Folders table, so this
> column could also be removed and replaced by a binary(20) column. Folders
> Table PK (FolderID)would be replaced with SHA1 binary(20) as well.
> Which brings a couple questions:
> 1. If we are going to store an SHA1 hash, wouldn't it be best to use:
> binary(20) instead of varbinary(20)?
> 2. Will I have any collisions (PK violations) with SHA1 hash (more on this
> in next sentence)?
> The MessageID is a variable length Binary byte array, and is a Globally
> UniqueIdentifier (it's just an older form of GUID before GUID was born) th
is
> MessageID is from MAPI (Exchange Server). So if you had 50 million
> varbinary(MAX) MessageIDs, and you ran SHA1 hash against all of them would
> you find duplicated SHA1 hash values?
> Thanks
> Russell Mangel
1. True. Although VARBINARY is functionaly equivalent because the
return value is always 20 bytes.
2. Not unless the MessageID exceeds 8000 bytes, which is the maximum
supported length for HashBytes. Although there is a theoretical risk of
a hash collision, you need 2^80 hashed messages on average before you
hit a duplicate. You are hashing GUIDs though. I don't know the GUIDs
used by Exchange but if they have similar qualities to the ones
generated by Windows then theoretically you are much, much more likely
to hit a duplicate GUID than a duplicate hash.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--