Deadlock blues

I am trying to rewrite 2 stored procs that have been perpetually having
deadlock issues against each other. The current problem is that the
pieces of SQL that are involved in the deadlock are both in a
transaction. The contention is for a single table. Would moving the
offending piece of code in stored proc #1 out of transaction solve the
deadlocking issue?
ThanksWe would need to see the code. Moving a statement out of a tran may not
give you the desired results when it comes to data integrity. Most deadlocks
are caused by updating the tables in different orders.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HxuQteJGHA.208@.tk2msftngp13.phx.gbl...
>I am trying to rewrite 2 stored procs that have been perpetually having
>deadlock issues against each other. The current problem is that the pieces
>of SQL that are involved in the deadlock are both in a transaction. The
>contention is for a single table. Would moving the offending piece of code
>in stored proc #1 out of transaction solve the deadlocking issue?
> Thanks

Deadlock blues

I am trying to rewrite 2 stored procs that have been perpetually having
deadlock issues against each other. The current problem is that the
pieces of SQL that are involved in the deadlock are both in a
transaction. The contention is for a single table. Would moving the
offending piece of code in stored proc #1 out of transaction solve the
deadlocking issue?
Thanks
We would need to see the code. Moving a statement out of a tran may not
give you the desired results when it comes to data integrity. Most deadlocks
are caused by updating the tables in different orders.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HxuQteJGHA.208@.tk2msftngp13.phx.gbl...
>I am trying to rewrite 2 stored procs that have been perpetually having
>deadlock issues against each other. The current problem is that the pieces
>of SQL that are involved in the deadlock are both in a transaction. The
>contention is for a single table. Would moving the offending piece of code
>in stored proc #1 out of transaction solve the deadlocking issue?
> Thanks

Deadlock blues

I am trying to rewrite 2 stored procs that have been perpetually having
deadlock issues against each other. The current problem is that the
pieces of SQL that are involved in the deadlock are both in a
transaction. The contention is for a single table. Would moving the
offending piece of code in stored proc #1 out of transaction solve the
deadlocking issue?
ThanksWe would need to see the code. Moving a statement out of a tran may not
give you the desired results when it comes to data integrity. Most deadlocks
are caused by updating the tables in different orders.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:%23HxuQteJGHA.208@.tk2msftngp13.phx.gbl...
>I am trying to rewrite 2 stored procs that have been perpetually having
>deadlock issues against each other. The current problem is that the pieces
>of SQL that are involved in the deadlock are both in a transaction. The
>contention is for a single table. Would moving the offending piece of code
>in stored proc #1 out of transaction solve the deadlocking issue?
> Thanks

DBMail used for job and alert emails

Can you use DBMail for job and alert emails without having to resort to usin
g
the DBMail stored procs? I have DBMail setup and the test email works fine
but when I run a job and expect to see a completion email send to a defined
operator no email occurs but neither does any error stating why the email wa
s
not sent. I have told SQLAgent to use DBMail and a specified account so I a
m
unsure what else I need to do.Any errors on the Database Mail Log? (Right-click Database Mail and select
View Database Mail Log).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Boggess" wrote:

> Can you use DBMail for job and alert emails without having to resort to us
ing
> the DBMail stored procs? I have DBMail setup and the test email works fin
e
> but when I run a job and expect to see a completion email send to a define
d
> operator no email occurs but neither does any error stating why the email
was
> not sent. I have told SQLAgent to use DBMail and a specified account so I
am
> unsure what else I need to do.|||Database Mail alerts did not work for me until after applying SQL 2005 SP1.
"Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
> Can you use DBMail for job and alert emails without having to resort to
> using
> the DBMail stored procs? I have DBMail setup and the test email works
> fine
> but when I run a job and expect to see a completion email send to a
> defined
> operator no email occurs but neither does any error stating why the email
> was
> not sent. I have told SQLAgent to use DBMail and a specified account so I
> am
> unsure what else I need to do.|||I found the issue. When you alter a DBMail configuration you need to restar
t
SQL Server Agent. The SQL Server Agent retains the last DBMail setting that
was available when the Agent started. Since I had no DBMail the last time
SQL Server Agent had been started it did not care what I was doing with
DBMail until I started the Agent again and then it refreshed its information
and worked fine.
This means any changes to an existing configuration of DBMail will also
require a restart of SQL Server Agent otherwise the Agent will be using the
configuration that existed when it started last. I was hoping for more of a
dynamic communication between DBMail and SQL Server Agent but it is working
now for jobs and alerts so hooray.
"Michael D'Angelo" wrote:

> Database Mail alerts did not work for me until after applying SQL 2005 SP1
.
> "Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
> news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
>
>

DBMail used for job and alert emails

Can you use DBMail for job and alert emails without having to resort to using
the DBMail stored procs? I have DBMail setup and the test email works fine
but when I run a job and expect to see a completion email send to a defined
operator no email occurs but neither does any error stating why the email was
not sent. I have told SQLAgent to use DBMail and a specified account so I am
unsure what else I need to do.Any errors on the Database Mail Log? (Right-click Database Mail and select
View Database Mail Log).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Boggess" wrote:
> Can you use DBMail for job and alert emails without having to resort to using
> the DBMail stored procs? I have DBMail setup and the test email works fine
> but when I run a job and expect to see a completion email send to a defined
> operator no email occurs but neither does any error stating why the email was
> not sent. I have told SQLAgent to use DBMail and a specified account so I am
> unsure what else I need to do.|||Database Mail alerts did not work for me until after applying SQL 2005 SP1.
"Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
> Can you use DBMail for job and alert emails without having to resort to
> using
> the DBMail stored procs? I have DBMail setup and the test email works
> fine
> but when I run a job and expect to see a completion email send to a
> defined
> operator no email occurs but neither does any error stating why the email
> was
> not sent. I have told SQLAgent to use DBMail and a specified account so I
> am
> unsure what else I need to do.|||I found the issue. When you alter a DBMail configuration you need to restart
SQL Server Agent. The SQL Server Agent retains the last DBMail setting that
was available when the Agent started. Since I had no DBMail the last time
SQL Server Agent had been started it did not care what I was doing with
DBMail until I started the Agent again and then it refreshed its information
and worked fine.
This means any changes to an existing configuration of DBMail will also
require a restart of SQL Server Agent otherwise the Agent will be using the
configuration that existed when it started last. I was hoping for more of a
dynamic communication between DBMail and SQL Server Agent but it is working
now for jobs and alerts so hooray.
"Michael D'Angelo" wrote:
> Database Mail alerts did not work for me until after applying SQL 2005 SP1.
> "Jerry Boggess" <JerryBoggess@.discussions.microsoft.com> wrote in message
> news:BF1A9E66-BDEB-4E53-B43A-693BE8BC4112@.microsoft.com...
> > Can you use DBMail for job and alert emails without having to resort to
> > using
> > the DBMail stored procs? I have DBMail setup and the test email works
> > fine
> > but when I run a job and expect to see a completion email send to a
> > defined
> > operator no email occurs but neither does any error stating why the email
> > was
> > not sent. I have told SQLAgent to use DBMail and a specified account so I
> > am
> > unsure what else I need to do.
>
>