detect and solve deadlocks

Is there any way by which deadlocks can be detacted and then appropriate
action can be taken (in vb.net code or sql) to avoid it. instead of throwing
error we can revoke the sp call..
thankshttp://www.sql-server-performance.com/deadlocks.asp
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||You can determine what processes are currently blocking other processes by
running sp_who and looking for SPIDs in the [blkby] column.
http://msdn2.microsoft.com/ms174313.aspx
Once you have the SPID of a blocking process, you can use DBCC INPUTBUFFER
to see what command it last executed.
http://msdn.microsoft.com/library/d...
v8y.asp
Here are a couple of articles on tracing deadlocks:
http://support.microsoft.com/defaul...kb;en-us;832524
http://support.microsoft.com/defaul...kb;en-us;271509
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>|||http://support.microsoft.com/defaul...kb;en-us;832524
Regards
Amish
*** Sent via Developersdex http://www.examnotes.net ***|||I think all the answers you are getting explain how to figure out what is
causing deadlocks so you can rewrite your applications to avoid them. While
this is definitely the right approach, you seem to be asking what you can do
to detect deadlocks in your code. SQL Server already detects and resolves
deadlocks for you. It does this by picking one of the transactions involved
in the deadlock and terminating it so the other transactions can continue.
The error you get at the client is saying that your transaction is the one
picked as the victim. The right thing to do when you're the deadlock victim
is to restart the transaction. If you have enough information available to
do the transaction again, you can just do it without telling the user that
something happened but if you need the user to re-enter the data to rerun
the transaction then just tell the user what happened and have them redo it.
Avoiding deadlocks through application design is important but in some cases
they are unavoidable and you should code your application to respond
appropriately.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Vikram" <aa@.aa> wrote in message
news:%23x95g36CGHA.2700@.TK2MSFTNGP14.phx.gbl...
> Is there any way by which deadlocks can be detacted and then appropriate
> action can be taken (in vb.net code or sql) to avoid it. instead of
> throwing
> error we can revoke the sp call..
> thanks
>

Design Question

Dear All,
I have the following design issue which I am unsure how to solve.
Here's the key information:
I am writing a tool which will send emails/SMS to registered users. The
registered user data will be stored in one database (we'll call it
DB1). No changes can be made to DB1. All information related to the
tool will be stored in a different database (we'll call it DB2). This
will include the following tables (among others):
- "Sent Email" table to store which user (from DB1) received what email
- "User" table to store individuals who have permission to login to the
tool
- "Email Template" table to store email templates
- "Email Template Log" table to record which user created/edited the
template
My specific problem is how to model these relationships in DB2. Will
DB2 contain a "User Look Up" table which stores the ID (PK) of the user
from DB1? Therefore my "Sent Email" table will have a FK from this look
up table? In addition if the scenario was that I needed to record an
attribute "Don't send me messages" would this be stored on the look up
table as well?
Thanks for any help in advance.
Jose"Jose" <discussions@.avandis.co.uk> wrote in message
news:1135436233.215881.325210@.g14g2000cwa.googlegroups.com...
> Dear All,
> I have the following design issue which I am unsure how to solve.
> Here's the key information:
> I am writing a tool which will send emails/SMS to registered users. The
> registered user data will be stored in one database (we'll call it
> DB1). No changes can be made to DB1. All information related to the
> tool will be stored in a different database (we'll call it DB2). This
> will include the following tables (among others):
> - "Sent Email" table to store which user (from DB1) received what email
> - "User" table to store individuals who have permission to login to the
> tool
> - "Email Template" table to store email templates
> - "Email Template Log" table to record which user created/edited the
> template
> My specific problem is how to model these relationships in DB2. Will
> DB2 contain a "User Look Up" table which stores the ID (PK) of the user
> from DB1? Therefore my "Sent Email" table will have a FK from this look
> up table? In addition if the scenario was that I needed to record an
> attribute "Don't send me messages" would this be stored on the look up
> table as well?
> Thanks for any help in advance.
> Jose
>
I guess you'll need a Users table in DB2. What you won't be able to do is
create a foreign key on it that references DB1. Cross-database constraints
aren't supported. You could create a view in DB2 that references the Users
table in DB1.
Have you considered using Notification Services? All you've described and
more ...
http://www.microsoft.com/sql/techno...on/default.mspx
Both 2000 and 2005 editions of NS are available.
David Portas
SQL Server MVP
--

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
David
With ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David
|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David