ERwin Triogger Bug

Hi, guys...new guy here. Let me introduce:

I'm a technical writer for B&N.com and I've been using ERwin
Datamodeler 4, release 4.1.2771 to create data dictionaries. I'm not a
DBA, though I'm slowly picking stuff up.

I discovered something yesterday that from my perspective is quite
serious and I wanted to verify that it is indeed an issue.

I have bunches of tables and for some tables, I have
triggers...anywhere from 1 to 6 of them for a table. Yesterday, I
noticed that triggers appeared in SQL Query Analyzer that didn't
appear in ERwin and after a long conversation with CA tech support, it
was determined that ERwin doesn't properly support showing all
triggers for a table when more than one trigger for that table
performs the same action; insert, uipdate, or delete. When it finds
more than one, it only shows one of them and ignores the others.

As anyone found this to be true? There is a kludge workaround...use
SQL Enterprise to script out the database, then perform a reverse
engineer on the script rather than the database, but that sucks.

Has anyone else noticed this?

John PosadaJohn Posada (jposada@.book.com) writes:
> I have bunches of tables and for some tables, I have
> triggers...anywhere from 1 to 6 of them for a table. Yesterday, I
> noticed that triggers appeared in SQL Query Analyzer that didn't
> appear in ERwin and after a long conversation with CA tech support, it
> was determined that ERwin doesn't properly support showing all
> triggers for a table when more than one trigger for that table
> performs the same action; insert, uipdate, or delete. When it finds
> more than one, it only shows one of them and ignores the others.
> As anyone found this to be true? There is a kludge workaround...use
> SQL Enterprise to script out the database, then perform a reverse
> engineer on the script rather than the database, but that sucks.

I don't use ERwin, but PowerDesigner from Sybase. Whether PowerDesigner
can handle this situation nicely, I don't know, because I don't have
triggers in PowerDesigner, and I never have more than one trigger
per action for the same table.

However, unless the ERwin documentation clearly says that ERwin handles
this, I find it difficult to call it a bug. Sounds more like a limitation
to me. To make an analogy: in PowerDesigner you can only have one table
constraint per table.

As for having more than one trigger per action for a table, I am inclined
to say that just because this feature is there, does not mean that you
have to use it. Since the execution order is undefined, this seems
somewhat risky to me. (Unless you have one INSETEAD OF and one AFTER
trigger for the same action.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John Posada (jposada@.book.com) writes:
> Second, about it not being a bug, I'm not asking ERwin to allow me to
> create more than one trigger...that situation already exists. I'm only
> asking it to show me an accurate representation of what it is.

If ERwin does not have the capabilities to create more than one trigger
on a table, I would expect it to be able to display more than one either.

I did an experiment with PowerDesigner and reverse-engineered a table
which have a load of table constraints. (As I mentioned, PD does not
support more than one table constraint per table.) And sure enough,
only one of the constraints made it into PD.

So, unless you can find some documentation that says that ERwin handles
multiple triggers on a table, my conclusion is that you have run into a
limitation of the tool, and the best suggestion for you would be to
use what even forum for enhancement requestions that CA provides.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, I'm working on it.

John Posada

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

ErrorCode=(288)Attempt to release mutex not owned by caller

I keep getting Critical application Errors every night right at the start of
the scheduled NTbackup. It happens right when the SBS Backup runs @. 11PM.
This is the SQL Server 2005 Workgroup edition that comes with SBS Premium.
During installation, they had
me upgrade the Sharepoint Database to the 2005 version for Full-Test
searching. I went through the process w/o error.
I installed the MS Hotfix but the errors still show up.
Here's all the event log
entries that appear at this exact time during backup:
Event Type: Information
Event Source: ESENT
Event Category: ShadowCopy
Event ID: 2001
Date: 8/13/2007
Time: 11:00:44 PM
User: N/A
Computer: SBSERVER1
Description:
wins (2456) Shadow copy 1 freeze started.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp
----
Event Type: Information
Event Source: MSSQL$SHAREPOINT
Event Category: (2)
Event ID: 3197
Date: 8/13/2007
Time: 11:00:44 PM
User: NT AUTHORITY\SYSTEM
Computer: SBSERVER1
Description:
I/O is frozen on database model. No user action is required. However, if I/O
is not resumed promptly, you could cancel the backup.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 7d 0c 00 00 0a 00 00 00 }......
0008: 15 00 00 00 53 00 42 00 ...S.B.
0010: 53 00 45 00 52 00 56 00 S.E.R.V.
0018: 45 00 52 00 31 00 5c 00 E.R.1.\.
0020: 53 00 48 00 41 00 52 00 S.H.A.R.
0028: 45 00 50 00 4f 00 49 00 E.P.O.I.
0030: 4e 00 54 00 00 00 06 00 N.T....
0038: 00 00 6d 00 6f 00 64 00 ..m.o.d.
0040: 65 00 6c 00 00 00 e.l...
-----
============================================ ***The above message is the same for all the db's...about 7***
============================================ Event Type: Information
Event Source: ESENT
Event Category: ShadowCopy
Event ID: 2003
Date: 8/13/2007
Time: 11:00:46 PM
User: N/A
Computer: SBSERVER1
Description:
wins (2456) Shadow copy 1 freeze stopped.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp
----
Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/13/2007
Time: 11:00:46 PM
User: N/A
Computer: SBSERVER1
Description:
SQLVDI: Loc=CVDS::Cleanup. Desc=Release(ClientAliveMutex).
ErrorCode=(288)Attempt to release mutex not owned by caller.
. Process=2256. Thread=3220. Client. Instance=SBSMONITORING. VD=.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp
----
It seems to be related to the SBSMONITORING DB's.
Please help. I'm at a loss.
-JoeFWe had a similar problem when backing up databases. Some process had not
released the mutex tables and we ending up restating the server in order to
free it. Hopefully doing this might help you.
If this is not an option download Process Explorer from sysinternals to
see if it can pinpoint the problem. You may be able to free the process
preventing backups,
"JoeF" wrote:
> I keep getting Critical application Errors every night right at the start of
> the scheduled NTbackup. It happens right when the SBS Backup runs @. 11PM.
> This is the SQL Server 2005 Workgroup edition that comes with SBS Premium.
> During installation, they had
> me upgrade the Sharepoint Database to the 2005 version for Full-Test
> searching. I went through the process w/o error.
> I installed the MS Hotfix but the errors still show up.
> Here's all the event log
> entries that appear at this exact time during backup:
> Event Type: Information
> Event Source: ESENT
> Event Category: ShadowCopy
> Event ID: 2001
> Date: 8/13/2007
> Time: 11:00:44 PM
> User: N/A
> Computer: SBSERVER1
> Description:
> wins (2456) Shadow copy 1 freeze started.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
> Event Type: Information
> Event Source: MSSQL$SHAREPOINT
> Event Category: (2)
> Event ID: 3197
> Date: 8/13/2007
> Time: 11:00:44 PM
> User: NT AUTHORITY\SYSTEM
> Computer: SBSERVER1
> Description:
> I/O is frozen on database model. No user action is required. However, if I/O
> is not resumed promptly, you could cancel the backup.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Data:
> 0000: 7d 0c 00 00 0a 00 00 00 }......
> 0008: 15 00 00 00 53 00 42 00 ...S.B.
> 0010: 53 00 45 00 52 00 56 00 S.E.R.V.
> 0018: 45 00 52 00 31 00 5c 00 E.R.1.\.
> 0020: 53 00 48 00 41 00 52 00 S.H.A.R.
> 0028: 45 00 50 00 4f 00 49 00 E.P.O.I.
> 0030: 4e 00 54 00 00 00 06 00 N.T....
> 0038: 00 00 6d 00 6f 00 64 00 ..m.o.d.
> 0040: 65 00 6c 00 00 00 e.l...
> -----
> ============================================> ***The above message is the same for all the db's...about 7***
> ============================================> Event Type: Information
> Event Source: ESENT
> Event Category: ShadowCopy
> Event ID: 2003
> Date: 8/13/2007
> Time: 11:00:46 PM
> User: N/A
> Computer: SBSERVER1
> Description:
> wins (2456) Shadow copy 1 freeze stopped.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
> Event Type: Error
> Event Source: SQLVDI
> Event Category: None
> Event ID: 1
> Date: 8/13/2007
> Time: 11:00:46 PM
> User: N/A
> Computer: SBSERVER1
> Description:
> SQLVDI: Loc=CVDS::Cleanup. Desc=Release(ClientAliveMutex).
> ErrorCode=(288)Attempt to release mutex not owned by caller.
> . Process=2256. Thread=3220. Client. Instance=SBSMONITORING. VD=.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
> It seems to be related to the SBSMONITORING DB's.
> Please help. I'm at a loss.
> -JoeF