|
Please help me out, what are the types of deadlocks in SQL Server; and how are the ways to solve it?
Regards,
John.L.Ponratnam
|
|
|
|
|
I can only think of one type of deadlock, meaning that 2 or more processes are trying to get to the same set of resources. There are however many type of "Locks".
1) Shared locks
2) Exclusive locks
3) Update locks
4) Intent locks
Here is how a deadlock can occur:
Process 1
Locks resource A
Looking to lock resource B
Process 2
Locks resource B
Looking to lock resource A
Classic deadlock situation. The way to avoid this is to lock the resources in the same order, this way process 1 & 2 would never be able to lock each other out. Either process 1 or proccess 2 would be granted resource A, then lock resource B and continue on.
Most database engines solve the deadlock issue by choosing a "victim" and forcing a rollback of the transaction, thus releasing the locks owned by it. Typically once the victim has been choosen, the remaining processes can continue on with their work.
|
|
|
|
|
So doesn't google work for you? there are thousands of hits.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
these might be helpful
exec sp_who
exec sp_who2
exec sp_lock
exec sp_monitor
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Hi..
this is my table info
ATT_DATE NOT NULL DATE
SHIFT NOT NULL VARCHAR2(8)
EMP_ID NOT NULL NUMBER
DEP_ID VARCHAR2(20)
TIMEIN DATE
TIMEOUT DATE
EMP_NAME VARCHAR2(30)
TOTALDAYS NUMBER
i want to insert in my TOTALDAY feild the total record of an employe of one month and + total sunday in month
if this possible so please please help me.... 
|
|
|
|
|
With the table designed as it is, you are going to have terrible problems in getting the thing to work the way you want.
At the very least the TotalDays column should be in a separate table, but this is not the only problem.
There is not enough space in a forum like this to properly advise you, so I can only suggest thet you do some studying on DataBase Design, before proceeding further.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi,
I am using SQl 2005 for setting up a job which has 2 steps:-
Step 1: Executes a SP which sends out result to a file
Type : T-SQL
Command :
DECLARE @cmd varchar(2000),
SET @cmd = 'osql -E -S CONDOR -s";" -w2000 -d Arion -Q"EXEC dbo.ENRSP_Applicants" -o D:\Applicants.txt'
EXEC master..xp_cmdshell @cmd
Step 2: Mailing the txt file to the user
Type : Operating System(CmdExec)
Command :
"D:\MSSQL\JOBS\EmailFile.cmd" D:\Applicants.txt aa@yahoo.com ss@yahoo.com "Applicants Report"
This works fine but the user want some modification to the current Job
* the email subject to include the date also
* Wants the result set to be dumped into an Excel file and to attach the file to the mail, if there is no
results returned no file should be atached
* If the Sp doesn't return a result set she wants a message and if it does she wants the message to be
another which includes date also.
Can all these be done , if so can you plz help me with it?
Thanks in advance.
Warm regards,
Payal
|
|
|
|
|
Firstly: is a CSV file acceptable? This opens just in Excel but is easier to create from SQL than an Excel file.
If an Excel file is required then you should probably use SSIS (which allows all of this winderfule logic to be implemented)
|
|
|
|
|
Can you tell me how to proceed(Using SQL job) with a CSV file please?
Thanks & regards
Payal
|
|
|
|
|
Hi,
I am trying to add a trigger on insert event on a table in SQL_Express.
ALTER trigger [dbo].[TR_Insert_New_Pair] on [dbo].[Vendors]
FOR INSERT AS
DECLARE
@ACCPACID varchar(12),
@VNAME varchar(60)
SELECT
@ACCPACID = (SELECT ACCPACVENDORID from inserted),
@VNAME = (
SELECT VENDNAME
FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server
WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID))
)
UPDATE dbo.Vendors SET
VENDNAME = @VNAME
WHERE
LTRIM(RTRIM(ACCPACVENDORID)) = ltrim(rtrim(@ACCPACID))
--This part works great (I've tested)
SELECT VENDNAME
FROM [ONT-ACCOUNTING].EXTDAT.DBO.APVEN --[ont-dell] is a linked server
WHERE LTRIM(RTRIM(VENDORID)) = LTRIM(RTRIM(@ACCPACID))
Is there something I am not seeing ????
Thanks
Kirill
|
|
|
|
|
zeusToronto wrote: Is there something I am not seeing ????
I'm not seeing your question. What the problem and error message?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I am sorry,
when Insert happens trigger does not seem to update the line. This trigger has other parts to it which seem to work just fine.
|
|
|
|
|
Have you checked that the value from the linked server is being populated? Have you tried the simple test of hard coding a value into @Vname and seeing if that works? I have a sneaky feeling that linked servers don't work from triggers, but I'm not certain.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield this is exactly how I've tested everything. Values do come from linked server, and when I hard code the value trigger seem to update it just fine.
I even went as far as changing the trigger to update from the local table (LOCAL SQL Table) and it works. The only issue I am having is when update happens from a linked server.
|
|
|
|
|
zeusToronto wrote: Values do come from linked server
Is that tested from within the trgger or just as a piece of sql? Based on your reply, if you hard code it or get the values from a local table it works, the only time it doesn't is when you get the values from a linked server in the trigger. The only cnclusion you can draw from this is that the linked server fetch is not working in the trigger.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi everybody,
I'm getting this error.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Contract_ContractType". The conflict occurred in database "ContractDB", table "dbo.ContractType", column 'ContractTypeId'. The statement has been terminated.
I understand that this is because I'm not sending the exact ContracatTypeId to table. I'm doing this deliberately because I want to store Null as well for some reason.
Please could any body tell me how can I remove/drop this constraint from database (please bare in mind I have no table diagram).
any help will be much appreciated.
|
|
|
|
|
just goto your detail table where your Foreign Key exists , open the table and click on your field which is Foreign Key and right click and click on Relationships , select the specific relationship and on the right side Grid in table designer Set the value for 'Enforce Foregin Key Constraint' to 'No'
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
|
Hello Friends,
I've two columns i.e Name and Address
Now i want to swap the column names i mean Name to be Address and Address to be Name
|
|
|
|
|
And the question is...?
My advice is free, and you may get what you paid for.
|
|
|
|
|
Just go to your table and Rename the table fields in Design View , if you want to do it script then you can click on Generate Change Script and it will Create Script for your
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
As the others said, plus you may want to use a third and temporary name to avoid a "Field name already used" error (like when swapping two variables).
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
Yes
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
how about
select Name as Address, Address as Name from table
Just create a view on it and start working ..
|
|
|
|
|
Hello every body!
I am writing a C# application using SQL Server as a database. I run transactions on some tables of my database. I want to write something like a trigger for example, that if a transaction rollbacks, for whatever reason, the database itself tries it one more time and then send the response to the application. Is it possible or not? If yes, how can I do this?
Thanks in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
