Deafult NULL not working

I am using SQL Server 2000. I have a Column with DataType int and default value specified as (null). But, With Insert or Update if the column value is Blank, 0 is getting inserted instead of the desired NULL.

Thanks

Is there, by chance, a trigger on this table?|||

NO. There is no trigger on this Table.

|||Well... create a complete DDL script for this table and post it here - something must be there.

Also, how you make a insert / update - directly or via some kind of stored proc? There may be a preprocessing in there that you miss, for example.|||

What exactly do you mean by "column value is blank". If you are explicitly trying to force a blank into the field then yes, it is going to get assigned as zero:

create table dbo.testo
( rid int,
x int default (null)
)

insert into dbo.testo select 1, ' '

insert into dbo.testo (rid) select 1

select * from dbo.testo

/*
rid x
-- --
1 0
1 NULL
*/

If, however, you are wanting to insert a row and allow the default to occur you must do something similar to what I hilighted in red

If you want to UPDATE to the default value, you can use syntax something like this:

update dbo.testO
set x=default
where rid =1

|||

YES. The Column Value is getting evaluated to '' as the user did not enter anything for the field on the form. Is there any way '' can be evaluated to NULL instead of 0.

- vmrao

|||declare @.p1 varchar(255)

set @.p1 = ''

insert into Mytable (rid, myintcol)
select 10, nullif(@.p1, '')
|||Thanks. NULLIF worked.

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks
)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak time
s
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within
a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
Message posted via http://www.droptable.comHi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/e...con_7a_3hdf.asp
http://support.microsoft.com/defaul...kb;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.droptable.com|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
.....<query>
...............INSERT INTO Table_Name (column_list) VALUES (value_list
)
.....<query>
<transaction>
I'll have to see what more I can do to change this logic...
Message posted via http://www.droptable.com|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:

> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE
.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
Message posted via http://www.droptable.com|||OK...I modified the trigger and now everything seems to be working fine eve
n
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
Message posted via http://www.droptable.com|||are we talking SQL2000 or 2005 ?
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.

> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com|||SQL 2000 SP4 with Win2K Advanced Server 2000
Message posted via http://www.droptable.com|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
Message posted via http://www.droptable.com

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak times
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
Message posted via http://www.droptable.com
Hi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/en...on_7a_3hdf.asp
http://support.microsoft.com/default...b;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.droptable.com
|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
......<query>
................INSERT INTO Table_Name (column_list) VALUES (value_list)
......<query>
<transaction>
I'll have to see what more I can do to change this logic...
Message posted via http://www.droptable.com
|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:

> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan
|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
Message posted via http://www.droptable.com
|||OK...I modified the trigger and now everything seems to be working fine even
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
Message posted via http://www.droptable.com
|||are we talking SQL2000 or 2005 ?
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.

> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||SQL 2000 SP4 with Win2K Advanced Server 2000
Message posted via http://www.droptable.com
|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
Message posted via http://www.droptable.com

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak times
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
--
Message posted via http://www.sqlmonster.comHi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_3hdf.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.sqlmonster.com|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
.....<query>
...............INSERT INTO Table_Name (column_list) VALUES (value_list)
.....<query>
<transaction>
I'll have to see what more I can do to change this logic...
--
Message posted via http://www.sqlmonster.com|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:
> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
--
Message posted via http://www.sqlmonster.com|||OK...I modified the trigger and now everything seems to be working fine even
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
--
Message posted via http://www.sqlmonster.com|||are we talking SQL2000 or 2005 ?
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||SQL 2000 SP4 with Win2K Advanced Server 2000
--
Message posted via http://www.sqlmonster.com|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
--
Message posted via http://www.sqlmonster.com|||It really depends on how you are connecting to SQL Server. The easiest way
to see what isolation levels a specific connection is using is to run a
profiler trace. The Existing connection event will show the current
Isolation level and if the app or the driver changes it batch completed will
show the statement. You usually set it with SET TRANSACTION ISOLATION LEVEL
xxxx. See BooksOnLine for more details.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:5920cb73e8dd5@.uwe...
> Since I don't know how to set an Isolation Level, my guess is that it is
> using the default one.
> How do you check the setting ?
> How do you set it ?
> --
> Message posted via http://www.sqlmonster.com|||Thank you so much all....I highly appreciate your advises :)
--
Message posted via http://www.sqlmonster.com

Deadlock Condition

We are working on VB 6.0 as front-end tool and sqlserver 7.0 as back-end.
Currently we have started experiencing deadlock condition mostly when we are firing the update statements.
We haev tried closing all recordsets after using them and setting them to 'Nothing', but hasn't helped.
The number of users has nothing to do with this problem experienced.
As sometimes even with around 65-70 users we don't have this and sometimes even 1-2 people working on the network experience this.Hi Geeta,
It doesnt matter whether 50 users use the system simultaneously or no ... A deadlock can even occur when there are 2 users in the system. It depends on what tables of the database are being used and for what purpose.It is very likely that there are long running queries which are holding locks on the table while another user is either trying to query or update the same table.
There can be many reasons as to why a query/update suddenly starts running slowly all of a sudden... the simples reasons can be that the table size has grown a lot larger than what it used to be or there can be external factors like CPU being used by another process which keeps the SQL server process to starve...
You will have to be very specific as to when u observe the deadlocks ...esp because u are saying that they dont occur all the time.
It will be really nice if you can provide more information
Cheers
Sachin|||Oohhh, your problem is such general, that only general statements can be made.

Are you using DAO of ADO of ADO.NET, or are using Java or Borland technology?

First of all, I'm not sure whether you have a deadlock situation at all. In a deadlock situation, two transactions started, and one will be forced to roll-back. I guess, you have simply a locking problem, which occurs when you are updating a record, and a second process wants to read it.

Second thought: such a locking problem can een happen within 1 program, running by one user! I had that problem with two concurrent threads. So, the number of concurrent users isn't really an issue, if you have designed your application properly.

I don't have my old sources right here, but i remember that I had to set a kind of WaitForTransaction timeout, which was by default 0.|||Hi,

I am using ADO technology.
Please tell me more about what needs to be added into
the code so that I can get rid of this problem.

Thanks.

Geeta|||BOL:

Minimizing Deadlocks

Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

Rolled back, undoing all the work performed by the transaction.
Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks:

Access objects in the same order.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
Use a low isolation level.
Use bound connections.

deadlock alert

sql2k sp3
I want to have an alert email me whenever a deadlock occurs. SQLMail is
working properly. I create an alert and specify Error 1205. Then I specify
myself in the email section. Then I purposely cause a deadlock but never get
emailed. In addition to this, the deadlock never goes in the TLog either. Any
ideas?
TIA, ChrisRDid you select "Always write to Windows Eventlog" for error 1205?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> sql2k sp3
> I want to have an alert email me whenever a deadlock occurs. SQLMail is
> working properly. I create an alert and specify Error 1205. Then I specify
> myself in the email section. Then I purposely cause a deadlock but never get
> emailed. In addition to this, the deadlock never goes in the TLog either. Any
> ideas?
>
> TIA, ChrisR|||I don't see that as an option anywhere. Will this help my email/ TLog problem?
"Tibor Karaszi" wrote:
> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> > sql2k sp3
> >
> > I want to have an alert email me whenever a deadlock occurs. SQLMail is
> > working properly. I create an alert and specify Error 1205. Then I specify
> > myself in the email section. Then I purposely cause a deadlock but never get
> > emailed. In addition to this, the deadlock never goes in the TLog either. Any
> > ideas?
> >
> >
> > TIA, ChrisR
>
>|||Where would I check this at?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...
> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> > sql2k sp3
> >
> > I want to have an alert email me whenever a deadlock occurs. SQLMail is
> > working properly. I create an alert and specify Error 1205. Then I
specify
> > myself in the email section. Then I purposely cause a deadlock but never
get
> > emailed. In addition to this, the deadlock never goes in the TLog
either. Any
> > ideas?
> >
> >
> > TIA, ChrisR
>|||EM, right-click the server, Manage SQL Server Messages, search your message in this dialog.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <chris@.noemail.com> wrote in message news:%230t$w4MsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> Where would I check this at?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...
>> Did you select "Always write to Windows Eventlog" for error 1205?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
>> > sql2k sp3
>> >
>> > I want to have an alert email me whenever a deadlock occurs. SQLMail is
>> > working properly. I create an alert and specify Error 1205. Then I
> specify
>> > myself in the email section. Then I purposely cause a deadlock but never
> get
>> > emailed. In addition to this, the deadlock never goes in the TLog
> either. Any
>> > ideas?
>> >
>> >
>> > TIA, ChrisR
>>
>

DDL Triggers

Hi,
I am trying to write a DDL Trigger so that whenever someone creates or drops
Database I store information somewhere.
My Trigger is working, now I am trying to make it more useful by extracting:
* "Name" of the database being dropped or created
* Name of the User performing the action ( login account)
* And time the action was performed.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
values (', ', ', 'DB Created')
GO
How do Iobtain userName, dbName, actionDate within the trigger ?
ThanksIt is a little bit more complicated than that. Take a look at the Eventdata
function in BOL and some of the examples there. Eventdata is used to return
the information you are looking for.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"news.microsoft.com" wrote:
> Hi,
> I am trying to write a DDL Trigger so that whenever someone creates or drops
> Database I store information somewhere.
> My Trigger is working, now I am trying to make it more useful by extracting:
> * "Name" of the database being dropped or created
> * Name of the User performing the action ( login account)
> * And time the action was performed.
> CREATE TRIGGER ddl_trig_database
> ON ALL SERVER
> FOR CREATE_DATABASE
> AS
> PRINT 'Database Created.'
> INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
> values (', ', ', 'DB Created')
> GO
> How do Iobtain userName, dbName, actionDate within the trigger ?
> Thanks
>
>
>

DDL Triggers

Hi,
I am trying to write a DDL Trigger so that whenever someone creates or drops
Database I store information somewhere.
My Trigger is working, now I am trying to make it more useful by extracting:
* "Name" of the database being dropped or created
* Name of the User performing the action ( login account)
* And time the action was performed.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
values (?, ?, ?, 'DB Created')
GO
How do Iobtain userName, dbName, actionDate within the trigger ?
Thanks
It is a little bit more complicated than that. Take a look at the Eventdata
function in BOL and some of the examples there. Eventdata is used to return
the information you are looking for.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"news.microsoft.com" wrote:

> Hi,
> I am trying to write a DDL Trigger so that whenever someone creates or drops
> Database I store information somewhere.
> My Trigger is working, now I am trying to make it more useful by extracting:
> * "Name" of the database being dropped or created
> * Name of the User performing the action ( login account)
> * And time the action was performed.
> CREATE TRIGGER ddl_trig_database
> ON ALL SERVER
> FOR CREATE_DATABASE
> AS
> PRINT 'Database Created.'
> INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
> values (?, ?, ?, 'DB Created')
> GO
> How do Iobtain userName, dbName, actionDate within the trigger ?
> Thanks
>
>
>

DDL Triggers

Hi,
I am trying to write a DDL Trigger so that whenever someone creates or drops
Database I store information somewhere.
My Trigger is working, now I am trying to make it more useful by extracting:
* "Name" of the database being dropped or created
* Name of the User performing the action ( login account)
* And time the action was performed.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
values (', ', ', 'DB Created')
GO
How do Iobtain userName, dbName, actionDate within the trigger ?
ThanksIt is a little bit more complicated than that. Take a look at the Eventdata
function in BOL and some of the examples there. Eventdata is used to return
the information you are looking for.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"news.microsoft.com" wrote:

> Hi,
> I am trying to write a DDL Trigger so that whenever someone creates or dro
ps
> Database I store information somewhere.
> My Trigger is working, now I am trying to make it more useful by extractin
g:
> * "Name" of the database being dropped or created
> * Name of the User performing the action ( login account)
> * And time the action was performed.
> CREATE TRIGGER ddl_trig_database
> ON ALL SERVER
> FOR CREATE_DATABASE
> AS
> PRINT 'Database Created.'
> INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
> values (', ', ', 'DB Created')
> GO
> How do Iobtain userName, dbName, actionDate within the trigger ?
> Thanks
>
>
>