alert not firing

Hi,
I set up an alert to notify me of failed login attempts and it worked fine
up until SQL Server was bounced yesterday. The messages are still going into
the error log but the alert count doesn't go up and so the notification
doesn't happen any more. The SQLServer agent is running, jobs are running on
schedule, but not these alerts. I'm using netsend and can successfully send a
test message.
I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks for any help
Is the alert appearing in the Windows event log and is the
event log service running? If it's not in the event log,
check the properties for the event log, make sure the
service is running and that the log is not full if not set
to overwrite.
-Sue
On Thu, 7 Apr 2005 03:57:02 -0700, "John"
<John@.discussions.microsoft.com> wrote:

>Hi,
>I set up an alert to notify me of failed login attempts and it worked fine
>up until SQL Server was bounced yesterday. The messages are still going into
>the error log but the alert count doesn't go up and so the notification
>doesn't happen any more. The SQLServer agent is running, jobs are running on
>schedule, but not these alerts. I'm using netsend and can successfully send a
>test message.
>I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
>Windows NT 4.0 (Build 1381: Service Pack 6)
>Thanks for any help
|||thanks for the quick response Sue,
the alerts are still appearing in the Windows event log
"Sue Hoegemeier" wrote:

> Is the alert appearing in the Windows event log and is the
> event log service running? If it's not in the event log,
> check the properties for the event log, make sure the
> service is running and that the log is not full if not set
> to overwrite.
> -Sue
> On Thu, 7 Apr 2005 03:57:02 -0700, "John"
> <John@.discussions.microsoft.com> wrote:
>
>
|||Check the Agent Error log for all messages. If you are set
up as an operator, check to see if you have any hours
restrictions.
There is also a troubleshooting article in books online:
Troubleshooting Alerts
-Sue
On Thu, 7 Apr 2005 06:31:06 -0700, "John"
<John@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>thanks for the quick response Sue,
>the alerts are still appearing in the Windows event log
>"Sue Hoegemeier" wrote:
|||doh! It's part of a failover cluster - I must have set up the alert on the
primary cluster but not on the secondary, so when the server was bounced it
would have failed over, hence the alert not firing! That's my theory anyway,
I can't test it just yet because I need to recycle once again to ensure that
the audit level is correctly set to 'failures' - how do you check that this
is set? I can see that I have set it in EM but that won't take effect until I
recycle - does sp_configure display the current setting?
"Sue Hoegemeier" wrote:

> Check the Agent Error log for all messages. If you are set
> up as an operator, check to see if you have any hours
> restrictions.
> There is also a troubleshooting article in books online:
> Troubleshooting Alerts
> -Sue
> On Thu, 7 Apr 2005 06:31:06 -0700, "John"
> <John@.discussions.microsoft.com> wrote:
>
>
|||No...the auditing setting is not available with sp_configure
The audit level is recorded in the AuditLevel key in the
registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer
3 = All, 2 = Failure, 1 = Success, 0 = None.
-Sue
On Fri, 8 Apr 2005 02:51:05 -0700, "John"
<John@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>doh! It's part of a failover cluster - I must have set up the alert on the
>primary cluster but not on the secondary, so when the server was bounced it
>would have failed over, hence the alert not firing! That's my theory anyway,
>I can't test it just yet because I need to recycle once again to ensure that
>the audit level is correctly set to 'failures' - how do you check that this
>is set? I can see that I have set it in EM but that won't take effect until I
>recycle - does sp_configure display the current setting?
>"Sue Hoegemeier" wrote:

alert not firing

Hi,
I set up an alert to notify me of failed login attempts and it worked fine
up until SQL Server was bounced yesterday. The messages are still going into
the error log but the alert count doesn't go up and so the notification
doesn't happen any more. The SQLServer agent is running, jobs are running on
schedule, but not these alerts. I'm using netsend and can successfully send
a
test message.
I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks for any helpIs the alert appearing in the Windows event log and is the
event log service running? If it's not in the event log,
check the properties for the event log, make sure the
service is running and that the log is not full if not set
to overwrite.
-Sue
On Thu, 7 Apr 2005 03:57:02 -0700, "John"
<John@.discussions.microsoft.com> wrote:

>Hi,
>I set up an alert to notify me of failed login attempts and it worked fine
>up until SQL Server was bounced yesterday. The messages are still going int
o
>the error log but the alert count doesn't go up and so the notification
>doesn't happen any more. The SQLServer agent is running, jobs are running o
n
>schedule, but not these alerts. I'm using netsend and can successfully send
a
>test message.
>I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
>Windows NT 4.0 (Build 1381: Service Pack 6)
>Thanks for any help|||thanks for the quick response Sue,
the alerts are still appearing in the Windows event log
"Sue Hoegemeier" wrote:

> Is the alert appearing in the Windows event log and is the
> event log service running? If it's not in the event log,
> check the properties for the event log, make sure the
> service is running and that the log is not full if not set
> to overwrite.
> -Sue
> On Thu, 7 Apr 2005 03:57:02 -0700, "John"
> <John@.discussions.microsoft.com> wrote:
>
>|||Check the Agent Error log for all messages. If you are set
up as an operator, check to see if you have any hours
restrictions.
There is also a troubleshooting article in books online:
Troubleshooting Alerts
-Sue
On Thu, 7 Apr 2005 06:31:06 -0700, "John"
<John@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>thanks for the quick response Sue,
>the alerts are still appearing in the Windows event log
>"Sue Hoegemeier" wrote:
>|||doh! It's part of a failover cluster - I must have set up the alert on the
primary cluster but not on the secondary, so when the server was bounced it
would have failed over, hence the alert not firing! That's my theory anyway,
I can't test it just yet because I need to recycle once again to ensure that
the audit level is correctly set to 'failures' - how do you check that this
is set? I can see that I have set it in EM but that won't take effect until
I
recycle - does sp_configure display the current setting?
"Sue Hoegemeier" wrote:

> Check the Agent Error log for all messages. If you are set
> up as an operator, check to see if you have any hours
> restrictions.
> There is also a troubleshooting article in books online:
> Troubleshooting Alerts
> -Sue
> On Thu, 7 Apr 2005 06:31:06 -0700, "John"
> <John@.discussions.microsoft.com> wrote:
>
>|||No...the auditing setting is not available with sp_configure
The audit level is recorded in the AuditLevel key in the
registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
3 = All, 2 = Failure, 1 = Success, 0 = None.
-Sue
On Fri, 8 Apr 2005 02:51:05 -0700, "John"
<John@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>doh! It's part of a failover cluster - I must have set up the alert on the
>primary cluster but not on the secondary, so when the server was bounced it
>would have failed over, hence the alert not firing! That's my theory anyway
,
>I can't test it just yet because I need to recycle once again to ensure tha
t
>the audit level is correctly set to 'failures' - how do you check that this
>is set? I can see that I have set it in EM but that won't take effect until
I
>recycle - does sp_configure display the current setting?
>"Sue Hoegemeier" wrote:
>

alert not firing

Hi,
I set up an alert to notify me of failed login attempts and it worked fine
up until SQL Server was bounced yesterday. The messages are still going into
the error log but the alert count doesn't go up and so the notification
doesn't happen any more. The SQLServer agent is running, jobs are running on
schedule, but not these alerts. I'm using netsend and can successfully send a
test message.
I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks for any helpIs the alert appearing in the Windows event log and is the
event log service running? If it's not in the event log,
check the properties for the event log, make sure the
service is running and that the log is not full if not set
to overwrite.
-Sue
On Thu, 7 Apr 2005 03:57:02 -0700, "John"
<John@.discussions.microsoft.com> wrote:
>Hi,
>I set up an alert to notify me of failed login attempts and it worked fine
>up until SQL Server was bounced yesterday. The messages are still going into
>the error log but the alert count doesn't go up and so the notification
>doesn't happen any more. The SQLServer agent is running, jobs are running on
>schedule, but not these alerts. I'm using netsend and can successfully send a
>test message.
>I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
>Windows NT 4.0 (Build 1381: Service Pack 6)
>Thanks for any help|||thanks for the quick response Sue,
the alerts are still appearing in the Windows event log
"Sue Hoegemeier" wrote:
> Is the alert appearing in the Windows event log and is the
> event log service running? If it's not in the event log,
> check the properties for the event log, make sure the
> service is running and that the log is not full if not set
> to overwrite.
> -Sue
> On Thu, 7 Apr 2005 03:57:02 -0700, "John"
> <John@.discussions.microsoft.com> wrote:
> >Hi,
> >I set up an alert to notify me of failed login attempts and it worked fine
> >up until SQL Server was bounced yesterday. The messages are still going into
> >the error log but the alert count doesn't go up and so the notification
> >doesn't happen any more. The SQLServer agent is running, jobs are running on
> >schedule, but not these alerts. I'm using netsend and can successfully send a
> >test message.
> >I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
> >Windows NT 4.0 (Build 1381: Service Pack 6)
> >Thanks for any help
>|||Check the Agent Error log for all messages. If you are set
up as an operator, check to see if you have any hours
restrictions.
There is also a troubleshooting article in books online:
Troubleshooting Alerts
-Sue
On Thu, 7 Apr 2005 06:31:06 -0700, "John"
<John@.discussions.microsoft.com> wrote:
>thanks for the quick response Sue,
>the alerts are still appearing in the Windows event log
>"Sue Hoegemeier" wrote:
>> Is the alert appearing in the Windows event log and is the
>> event log service running? If it's not in the event log,
>> check the properties for the event log, make sure the
>> service is running and that the log is not full if not set
>> to overwrite.
>> -Sue
>> On Thu, 7 Apr 2005 03:57:02 -0700, "John"
>> <John@.discussions.microsoft.com> wrote:
>> >Hi,
>> >I set up an alert to notify me of failed login attempts and it worked fine
>> >up until SQL Server was bounced yesterday. The messages are still going into
>> >the error log but the alert count doesn't go up and so the notification
>> >doesn't happen any more. The SQLServer agent is running, jobs are running on
>> >schedule, but not these alerts. I'm using netsend and can successfully send a
>> >test message.
>> >I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
>> >Windows NT 4.0 (Build 1381: Service Pack 6)
>> >Thanks for any help
>>|||doh! It's part of a failover cluster - I must have set up the alert on the
primary cluster but not on the secondary, so when the server was bounced it
would have failed over, hence the alert not firing! That's my theory anyway,
I can't test it just yet because I need to recycle once again to ensure that
the audit level is correctly set to 'failures' - how do you check that this
is set? I can see that I have set it in EM but that won't take effect until I
recycle - does sp_configure display the current setting?
"Sue Hoegemeier" wrote:
> Check the Agent Error log for all messages. If you are set
> up as an operator, check to see if you have any hours
> restrictions.
> There is also a troubleshooting article in books online:
> Troubleshooting Alerts
> -Sue
> On Thu, 7 Apr 2005 06:31:06 -0700, "John"
> <John@.discussions.microsoft.com> wrote:
> >thanks for the quick response Sue,
> >the alerts are still appearing in the Windows event log
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Is the alert appearing in the Windows event log and is the
> >> event log service running? If it's not in the event log,
> >> check the properties for the event log, make sure the
> >> service is running and that the log is not full if not set
> >> to overwrite.
> >>
> >> -Sue
> >>
> >> On Thu, 7 Apr 2005 03:57:02 -0700, "John"
> >> <John@.discussions.microsoft.com> wrote:
> >>
> >> >Hi,
> >> >I set up an alert to notify me of failed login attempts and it worked fine
> >> >up until SQL Server was bounced yesterday. The messages are still going into
> >> >the error log but the alert count doesn't go up and so the notification
> >> >doesn't happen any more. The SQLServer agent is running, jobs are running on
> >> >schedule, but not these alerts. I'm using netsend and can successfully send a
> >> >test message.
> >> >I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
> >> >Windows NT 4.0 (Build 1381: Service Pack 6)
> >> >Thanks for any help
> >>
> >>
>|||No...the auditing setting is not available with sp_configure
The audit level is recorded in the AuditLevel key in the
registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
3 = All, 2 = Failure, 1 = Success, 0 = None.
-Sue
On Fri, 8 Apr 2005 02:51:05 -0700, "John"
<John@.discussions.microsoft.com> wrote:
>doh! It's part of a failover cluster - I must have set up the alert on the
>primary cluster but not on the secondary, so when the server was bounced it
>would have failed over, hence the alert not firing! That's my theory anyway,
>I can't test it just yet because I need to recycle once again to ensure that
>the audit level is correctly set to 'failures' - how do you check that this
>is set? I can see that I have set it in EM but that won't take effect until I
>recycle - does sp_configure display the current setting?
>"Sue Hoegemeier" wrote:
>> Check the Agent Error log for all messages. If you are set
>> up as an operator, check to see if you have any hours
>> restrictions.
>> There is also a troubleshooting article in books online:
>> Troubleshooting Alerts
>> -Sue
>> On Thu, 7 Apr 2005 06:31:06 -0700, "John"
>> <John@.discussions.microsoft.com> wrote:
>> >thanks for the quick response Sue,
>> >the alerts are still appearing in the Windows event log
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> Is the alert appearing in the Windows event log and is the
>> >> event log service running? If it's not in the event log,
>> >> check the properties for the event log, make sure the
>> >> service is running and that the log is not full if not set
>> >> to overwrite.
>> >>
>> >> -Sue
>> >>
>> >> On Thu, 7 Apr 2005 03:57:02 -0700, "John"
>> >> <John@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hi,
>> >> >I set up an alert to notify me of failed login attempts and it worked fine
>> >> >up until SQL Server was bounced yesterday. The messages are still going into
>> >> >the error log but the alert count doesn't go up and so the notification
>> >> >doesn't happen any more. The SQLServer agent is running, jobs are running on
>> >> >schedule, but not these alerts. I'm using netsend and can successfully send a
>> >> >test message.
>> >> >I'm running Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition on
>> >> >Windows NT 4.0 (Build 1381: Service Pack 6)
>> >> >Thanks for any help
>> >>
>> >>
>>

alert messages in SSRS

hi,

I am working on migration from crystal reports to SSRS. my crystal report has some alerting formulas, which displays pop-ups at runtime based on some conditions.How do i implement those alerting formulas in SSRS.

Thanks in advance.

I think you can call msgboxes from code, beyond that there isn't anything.

Why are you migrating from Crystal to RS?

Agent started but not started?

I have a SQL 2005 server running on a win2k3 box. For some reason, no
matter what account the SQL Server Agent runs under, error messages
are logged in the security logs. Even if the login account is set to
"local service". BUT if I look at the service under "services", it
says started. It also says started in the SQL 2005 configuration
manager.
Has anyone seen this before?
ThanksHello,
Can you create one test job and try executing. If the job runs then SQL
Agent is working fine...also see the SQLAgent.out file in Log folder of SQL
Server
installation folder for any errors
Thanks
Hari
<costello_joe@.hotmail.com> wrote in message
news:1180399298.618353.292290@.q19g2000prn.googlegroups.com...
> I have a SQL 2005 server running on a win2k3 box. For some reason, no
> matter what account the SQL Server Agent runs under, error messages
> are logged in the security logs. Even if the login account is set to
> "local service". BUT if I look at the service under "services", it
> says started. It also says started in the SQL 2005 configuration
> manager.
> Has anyone seen this before?
> Thanks
>

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--

Agent Jobs Failure

I have setup the sending of an email when a job fails, but the Messages
portion of the email only includes the step that errored and a generic
message, how can I get the email to also contain the actual error message?
--
HaroldsHi Harolds,
Thank you for your post.
Based on my scope, you can not include the detail error message directly.
Please build up a custom stored procedure to send the email.
Here is an article for your reference:
http://www.orcsweb.com/articles/sendmailsql.aspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks that did the trick.
--
Harolds
"Wei Lu" wrote:
> Hi Harolds,
> Thank you for your post.
> Based on my scope, you can not include the detail error message directly.
> Please build up a custom stored procedure to send the email.
> Here is an article for your reference:
> http://www.orcsweb.com/articles/sendmailsql.aspx
> http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Harolds,
Thank you for the update. Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Have a nice day!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

After laptop goes into standby, SQLEXPRESS won't start

If my laptop goes into standby with the MSSQL$SQLEXPRESS service running, the service will not start again. I get the following error messages in my log:

2006-03-06 20:49:08.45 Server This instance of SQL Server last reported using a process ID of 1764 at 3/6/2006 1:48:21 PM (local) 3/6/2006 9:48:21 PM (UTC). This is an informational message only; no user action is required.

2006-03-06 20:49:08.45 Server Error: 17053, Severity: 16, State: 1.

2006-03-06 20:49:08.45 Server UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.

2006-03-06 20:49:08.45 Server Registry startup parameters:

2006-03-06 20:49:08.45 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2006-03-06 20:49:08.45 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2006-03-06 20:49:08.45 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2006-03-06 20:49:08.56 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2006-03-06 20:49:08.56 Server Detected 1 CPUs. This is an informational message; no user action is required.

2006-03-06 20:49:09.32 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2006-03-06 20:49:09.35 Server Database Mirroring Transport is disabled in the endpoint configuration.

2006-03-06 20:49:09.35 spid5s Starting up database 'master'.

2006-03-06 20:49:09.68 spid5s SQL Trace ID 1 was started by login "sa".

2006-03-06 20:49:09.76 spid5s Starting up database 'mssqlsystemresource'.

2006-03-06 20:49:10.03 spid5s Failed to check for new installation or a renamed server at startup. The logic for this check has failed unexpectedly. Run setup again, or fix the problematic registry key.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.

2006-03-06 20:49:10.03 Server Error: 17826, Severity: 18, State: 3.

2006-03-06 20:49:10.03 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2006-03-06 20:49:10.03 Server Error: 17120, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Uninstalling, reinstalling and restoring the databases fixes the problem. Also, is there anyway to gracefully shut down the service on standby? Any help would be greatly appreciated.

Thanks,

David

Essentially this error below means that SQL no longer has access to it's registry keys.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

Error 0x5 means "Access Denied" most likely due to the instance no longer having access to its configuration info stored in the registry. Not sure why going into standby mode would trigger a change in the registry key security.

|||

I don't have this problem on my laptop which goes in and out of standby multiple times a day. This seems like it is probably deeper than just SQL. I'm trying to track down some additional info from the Windows team.

In the mean time, I've occationally seen "weird" behavior associated with a corrupt user profile. You might consider recreating your user profile and see if the problem goes away. Kind of a scatter gun approach, but it's fairly easy to check. Read the following KB articles for more about troubleshooting profiles:

http://support.microsoft.com/kb/305506/en-us

http://support.microsoft.com/kb/811151/en-us

Regards,

Mike Wachal
SQL Express team

|||

Change your SQL Express to logon as the System account, by default it logs on as the Network something or another account which is a total crapshoot as to whether that account can get to the registry.

Gandalf

http://gandalf.hudlow.net

|||gandalf is totally right. I had the same problem on every reboot until I changed the account to LOCAL SYSTEM. Now everything is kosher|||

Every time you install a SQL instance it will automatically grant itself access to the various registry keys that it needs access to. Even if the service is running under network service. So you should not need to run it under local system account to get access to the registry keys. Switching SQL to run under local system will give it carte blanc to read the registry which will solve a problem where setup probably failed to grant itself proper perms on the reg keys.

Perhaps something on your machine is locking down reg key permissions after SQLExpress is installed, this is all I can think of that could cause this issue.

|||Yeah, this is a pretty good demo of why the registry is both the best and worst idea in history :)

After laptop goes into standby, SQLEXPRESS won't start

If my laptop goes into standby with the MSSQL$SQLEXPRESS service running, the service will not start again. I get the following error messages in my log:

2006-03-06 20:49:08.45 Server This instance of SQL Server last reported using a process ID of 1764 at 3/6/2006 1:48:21 PM (local) 3/6/2006 9:48:21 PM (UTC). This is an informational message only; no user action is required.

2006-03-06 20:49:08.45 Server Error: 17053, Severity: 16, State: 1.

2006-03-06 20:49:08.45 Server UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.

2006-03-06 20:49:08.45 Server Registry startup parameters:

2006-03-06 20:49:08.45 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2006-03-06 20:49:08.45 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2006-03-06 20:49:08.45 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2006-03-06 20:49:08.56 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2006-03-06 20:49:08.56 Server Detected 1 CPUs. This is an informational message; no user action is required.

2006-03-06 20:49:09.32 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2006-03-06 20:49:09.35 Server Database Mirroring Transport is disabled in the endpoint configuration.

2006-03-06 20:49:09.35 spid5s Starting up database 'master'.

2006-03-06 20:49:09.68 spid5s SQL Trace ID 1 was started by login "sa".

2006-03-06 20:49:09.76 spid5s Starting up database 'mssqlsystemresource'.

2006-03-06 20:49:10.03 spid5s Failed to check for new installation or a renamed server at startup. The logic for this check has failed unexpectedly. Run setup again, or fix the problematic registry key.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.

2006-03-06 20:49:10.03 Server Error: 17826, Severity: 18, State: 3.

2006-03-06 20:49:10.03 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2006-03-06 20:49:10.03 Server Error: 17120, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Uninstalling, reinstalling and restoring the databases fixes the problem. Also, is there anyway to gracefully shut down the service on standby? Any help would be greatly appreciated.

Thanks,

David

Essentially this error below means that SQL no longer has access to it's registry keys.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

Error 0x5 means "Access Denied" most likely due to the instance no longer having access to its configuration info stored in the registry. Not sure why going into standby mode would trigger a change in the registry key security.

|||

I don't have this problem on my laptop which goes in and out of standby multiple times a day. This seems like it is probably deeper than just SQL. I'm trying to track down some additional info from the Windows team.

In the mean time, I've occationally seen "weird" behavior associated with a corrupt user profile. You might consider recreating your user profile and see if the problem goes away. Kind of a scatter gun approach, but it's fairly easy to check. Read the following KB articles for more about troubleshooting profiles:

http://support.microsoft.com/kb/305506/en-us

http://support.microsoft.com/kb/811151/en-us

Regards,

Mike Wachal
SQL Express team

|||

Change your SQL Express to logon as the System account, by default it logs on as the Network something or another account which is a total crapshoot as to whether that account can get to the registry.

Gandalf

http://gandalf.hudlow.net

|||gandalf is totally right. I had the same problem on every reboot until I changed the account to LOCAL SYSTEM. Now everything is kosher|||

Every time you install a SQL instance it will automatically grant itself access to the various registry keys that it needs access to. Even if the service is running under network service. So you should not need to run it under local system account to get access to the registry keys. Switching SQL to run under local system will give it carte blanc to read the registry which will solve a problem where setup probably failed to grant itself proper perms on the reg keys.

Perhaps something on your machine is locking down reg key permissions after SQLExpress is installed, this is all I can think of that could cause this issue.

|||Yeah, this is a pretty good demo of why the registry is both the best and worst idea in history :)

After laptop goes into standby, SQLEXPRESS won't start

If my laptop goes into standby with the MSSQL$SQLEXPRESS service running, the service will not start again. I get the following error messages in my log:

2006-03-06 20:49:08.45 Server This instance of SQL Server last reported using a process ID of 1764 at 3/6/2006 1:48:21 PM (local) 3/6/2006 9:48:21 PM (UTC). This is an informational message only; no user action is required.

2006-03-06 20:49:08.45 Server Error: 17053, Severity: 16, State: 1.

2006-03-06 20:49:08.45 Server UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.

2006-03-06 20:49:08.45 Server Registry startup parameters:

2006-03-06 20:49:08.45 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2006-03-06 20:49:08.45 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2006-03-06 20:49:08.45 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2006-03-06 20:49:08.56 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2006-03-06 20:49:08.56 Server Detected 1 CPUs. This is an informational message; no user action is required.

2006-03-06 20:49:09.32 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2006-03-06 20:49:09.35 Server Database Mirroring Transport is disabled in the endpoint configuration.

2006-03-06 20:49:09.35 spid5s Starting up database 'master'.

2006-03-06 20:49:09.68 spid5s SQL Trace ID 1 was started by login "sa".

2006-03-06 20:49:09.76 spid5s Starting up database 'mssqlsystemresource'.

2006-03-06 20:49:10.03 spid5s Failed to check for new installation or a renamed server at startup. The logic for this check has failed unexpectedly. Run setup again, or fix the problematic registry key.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.

2006-03-06 20:49:10.03 Server Error: 17826, Severity: 18, State: 3.

2006-03-06 20:49:10.03 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2006-03-06 20:49:10.03 Server Error: 17120, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Uninstalling, reinstalling and restoring the databases fixes the problem. Also, is there anyway to gracefully shut down the service on standby? Any help would be greatly appreciated.

Thanks,

David

Essentially this error below means that SQL no longer has access to it's registry keys.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

Error 0x5 means "Access Denied" most likely due to the instance no longer having access to its configuration info stored in the registry. Not sure why going into standby mode would trigger a change in the registry key security.

|||

I don't have this problem on my laptop which goes in and out of standby multiple times a day. This seems like it is probably deeper than just SQL. I'm trying to track down some additional info from the Windows team.

In the mean time, I've occationally seen "weird" behavior associated with a corrupt user profile. You might consider recreating your user profile and see if the problem goes away. Kind of a scatter gun approach, but it's fairly easy to check. Read the following KB articles for more about troubleshooting profiles:

http://support.microsoft.com/kb/305506/en-us

http://support.microsoft.com/kb/811151/en-us

Regards,

Mike Wachal
SQL Express team

|||

Change your SQL Express to logon as the System account, by default it logs on as the Network something or another account which is a total crapshoot as to whether that account can get to the registry.

Gandalf

http://gandalf.hudlow.net

|||gandalf is totally right. I had the same problem on every reboot until I changed the account to LOCAL SYSTEM. Now everything is kosher|||

Every time you install a SQL instance it will automatically grant itself access to the various registry keys that it needs access to. Even if the service is running under network service. So you should not need to run it under local system account to get access to the registry keys. Switching SQL to run under local system will give it carte blanc to read the registry which will solve a problem where setup probably failed to grant itself proper perms on the reg keys.

Perhaps something on your machine is locking down reg key permissions after SQLExpress is installed, this is all I can think of that could cause this issue.

|||Yeah, this is a pretty good demo of why the registry is both the best and worst idea in history :)

After laptop goes into standby, SQLEXPRESS won't start

If my laptop goes into standby with the MSSQL$SQLEXPRESS service running, the service will not start again. I get the following error messages in my log:

2006-03-06 20:49:08.45 Server This instance of SQL Server last reported using a process ID of 1764 at 3/6/2006 1:48:21 PM (local) 3/6/2006 9:48:21 PM (UTC). This is an informational message only; no user action is required.

2006-03-06 20:49:08.45 Server Error: 17053, Severity: 16, State: 1.

2006-03-06 20:49:08.45 Server UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.

2006-03-06 20:49:08.45 Server Registry startup parameters:

2006-03-06 20:49:08.45 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

2006-03-06 20:49:08.45 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2006-03-06 20:49:08.45 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2006-03-06 20:49:08.56 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2006-03-06 20:49:08.56 Server Detected 1 CPUs. This is an informational message; no user action is required.

2006-03-06 20:49:09.32 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2006-03-06 20:49:09.35 Server Database Mirroring Transport is disabled in the endpoint configuration.

2006-03-06 20:49:09.35 spid5s Starting up database 'master'.

2006-03-06 20:49:09.68 spid5s SQL Trace ID 1 was started by login "sa".

2006-03-06 20:49:09.76 spid5s Starting up database 'mssqlsystemresource'.

2006-03-06 20:49:10.03 spid5s Failed to check for new installation or a renamed server at startup. The logic for this check has failed unexpectedly. Run setup again, or fix the problematic registry key.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.

2006-03-06 20:49:10.03 Server Error: 17826, Severity: 18, State: 3.

2006-03-06 20:49:10.03 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2006-03-06 20:49:10.03 Server Error: 17120, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Uninstalling, reinstalling and restoring the databases fixes the problem. Also, is there anyway to gracefully shut down the service on standby? Any help would be greatly appreciated.

Thanks,

David

Essentially this error below means that SQL no longer has access to it's registry keys.

2006-03-06 20:49:10.03 Server Error: 17182, Severity: 16, State: 1.

2006-03-06 20:49:10.03 Server TDSSNIClient initialization failed with error 0x5, status code 0x90.

Error 0x5 means "Access Denied" most likely due to the instance no longer having access to its configuration info stored in the registry. Not sure why going into standby mode would trigger a change in the registry key security.

|||

I don't have this problem on my laptop which goes in and out of standby multiple times a day. This seems like it is probably deeper than just SQL. I'm trying to track down some additional info from the Windows team.

In the mean time, I've occationally seen "weird" behavior associated with a corrupt user profile. You might consider recreating your user profile and see if the problem goes away. Kind of a scatter gun approach, but it's fairly easy to check. Read the following KB articles for more about troubleshooting profiles:

http://support.microsoft.com/kb/305506/en-us

http://support.microsoft.com/kb/811151/en-us

Regards,

Mike Wachal
SQL Express team

|||

Change your SQL Express to logon as the System account, by default it logs on as the Network something or another account which is a total crapshoot as to whether that account can get to the registry.

Gandalf

http://gandalf.hudlow.net

|||gandalf is totally right. I had the same problem on every reboot until I changed the account to LOCAL SYSTEM. Now everything is kosher
|||

Every time you install a SQL instance it will automatically grant itself access to the various registry keys that it needs access to. Even if the service is running under network service. So you should not need to run it under local system account to get access to the registry keys. Switching SQL to run under local system will give it carte blanc to read the registry which will solve a problem where setup probably failed to grant itself proper perms on the reg keys.

Perhaps something on your machine is locking down reg key permissions after SQLExpress is installed, this is all I can think of that could cause this issue.

|||Yeah, this is a pretty good demo of why the registry is both the best and worst idea in history :)

Affect of deleting error messages

I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?I'm still trying to figure out when I would ever want to do this.

What are you trying to do?

From BOL

Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.

If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.

The table shows when IGNORE_DUP_KEY can be used.|||I'm trying to use a server side solution to improve performance of a stored procedure. I've got a one-to many table with Street names (being the many) where the recno links back to the main table. It is possible for say King St to have a cross street of Kingsway. Hence Recno 13 for both. When I then match back to the main table I'd get multiple rows where I desire 1. To get around this I originally used the distinct clause. It works but is much slower than if I create a unique index with the ignore_dup_key option on a temp table. Problem being is the message. I've explicitly requested this action 'ignore_dup_key' so why tell me 'Duplicate key was ignored.' as if it is an error? We were force to a slower server and now my endusers are complaining about speed, that is why I wish to solve this server side instead of client side. I could write a routine to deal with the error client side but why go this route if I don't have too.|||Select Distinct

But you need to resolve this from a sql perspective...

did you read the quote...

You really should address handling MUCH more closely...you'r going to need to as well as have a general expectance of what the stored procedures are doing and what the expect...|||And that's not an error it's a warning...|||Similar to using a "set nocount" to eliminate an unwanted response to an end user. I'd expected and hoped an equivalent for eliminating an unwanted message from the use of a unique index. Yes I did read "SQL Server issues a warning and ignores the duplicate row" of your message. I wish to eliminate the message as it is unwanted. This is the contents of the message window in Query Analyzer "Server: Msg 3604, Level 16, State 1, Procedure ZZZStreet, Line 15
Duplicate key was ignored." The only way I can think of eliminating this is to delete the error message 3604 from the SQL Box. I've never done it and I am looking for guidance as to whether this will or will not fix the unwanted message from being given. I don't see any downside to this error being gone as the performance improvements should outweigh the loss of a strictly informational message. Maybe there is a downside that I'm unware of, an that's what I'm trying to figure out here.|||funny, I'm using EM here but when I create a table say Table1 with a varchar column (indexed, unique, ignore duplicate key) I am not able to insert duplicate values. From BOL it says: "Ignore duplicate key If you create a unique index, you can set this option to ensure each value in an indexed column is unique.".
Why am I confused?

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
--