BOL:

"The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY and UNIQUE constraints."

Another useful source of info that works in triggers is

SELECT * from master.dbo.sysprocesses WHERE spid = @@spid

sysprocesses has lost of stuff: login, host, NT login, appname, (but not the nesting...

I certainly agree (as does everyone on this thread) about always having primary keys.

There are many reasons why one would not always use clustered indexes. One not mentioned so far...

This error occurs when your database has indexed views or indexes on computed columns.

http://support.microsoft.com/default.aspx?scid=kb;en-us;301292

Thanks dcpeterson,

The mechanism you describe sounds plausible.

Its something I really need to watch out for as I use READ UNCOMMITTED quite a bit for reporting.

I always thought the only way...

Sorry I cant really answer about DMO.

In TSQL you can look at sp_helprolemember

or try a variation of

select DbRole = g.name, MemberName = u.name, MemberSID = u.sid

from sysusers u, sysusers g,...

I am interested to know if noeld's elegant solution really does avoid deadlock.:

UPDATE unprocessed_message

SET state = @StateNext,

@id = id,

win_service_name = @WinServiceName,

machine_name...

The trigger code is not in scope, hence scope_identity() will not pick it up.

Maybe @@identity will do it for you - unless you have other identity inserts in the trigger.

Its...

If several instances of GetNextUnprocessedMessage can run at once, then deadlock can occur because 2 instances can both acquire a shared lock in in the first select statement.

Then each instance...

Can you view (and post) the agent error log?

Have you re-started SQL Agent? I found it sometimes needs to be restarted to re-establish a MAPI session if the exchange server has been re-configured.

Actually it sounds as though the SQLAgent service may be running under a different user account from SQLServer itself (since SQL server can see the profile but the agent cant).

You...

Hi Steve,

The cohortCount will always be the same in every row of the fact table with the same cohortID, so we could either pick the lastest or use min or...

I am new too Im afraid.

Have you looked at the member properties of dimensions. It is in books online:

example:

WITH

MEMBER [Measures].[Store Size] AS

'Val(Store.CurrentMember.Properties("Store Sqft"))'

SELECT

...

A good article with some interesting observations.

I would add a point that when using a string to represent the date it is better to get in the habit of using...