Re: Types of Deadlocks in SQL Server - Database Discussion Boards

Re: Multiple queries being requested on for same table

Vishnu Nath18-Sep-09 21:05

18-Sep-09 21:05

Thanks for giving out your suggestion.
Well databases must be having robust engine to handle the queries.
But its fuctioning and understanding is important while building a database driven web application.

Also was thinking how facebook, orkut would be using their databases to manage their user queries around the world.

Aspiring Techie,
Vishnu Nath

Re: Multiple queries being requested on for same table

Ashfield18-Sep-09 21:30

Ashfield

18-Sep-09 21:30

Vishnu Nath wrote:
But its fuctioning and understanding is important while building a database driven web application.

So you need to do more research than you will get from asking in a forum. Buy a few books on your chosen database (SQL Server, Oracle etc) and investigate further.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Database Deployment

mobius11100117-Sep-09 4:30

mobius111001

17-Sep-09 4:30

I wanted to get some opinions on this. This is my first time I’ve been tasked with doing this. I need to deploy a SQL Server database. My question is this…is it better to script the creation of the database, or is it better to ship an already structured database?

Thank you in advance.

Re: Database Deployment

J4amieC17-Sep-09 4:51

J4amieC

17-Sep-09 4:51

Restoring a database is certainly easier - but in terms of best practice, i'd script it!

Script it

David Mujica17-Sep-09 5:07

David Mujica

17-Sep-09 5:07

I used to work for a software company and we would always ship a series of scripts along with our application to create the basic database structure and seed the lookup tables with basic information.

Advantage: Store your database scripts in a version control repository along with your applicaiton source code so that any given build of your source has a corresponding set of database scripts.

Also, when you have to upgrade the system from version x to y, you should be able to deliver scripts that can update the database schema accordingly.

Re: Database Deployment

mobius11100117-Sep-09 5:46

mobius111001

17-Sep-09 5:46

Thank you very much for your input. I was thinking scripting, but I wanted to get some real-world information. I really appreciate it.

Types of Deadlocks in SQL Server

John.L.Ponratnam17-Sep-09 4:21

John.L.Ponratnam

17-Sep-09 4:21

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

Re: Types of Deadlocks in SQL Server

David Mujica17-Sep-09 5:02

David Mujica

17-Sep-09 5:02

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.

Re: Types of Deadlocks in SQL Server

Ashfield17-Sep-09 9:17

Ashfield

17-Sep-09 9:17

So doesn't google work for you? there are thousands of hits.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Types of Deadlocks in SQL Server

εїзεїзεїз17-Sep-09 13:28

εїзεїзεїз

17-Sep-09 13:28

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

insert Record

mjawadkhatri16-Sep-09 23:46

mjawadkhatri

16-Sep-09 23:46

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.... Sigh | :sigh:

Re: insert Record

Henry Minute17-Sep-09 3:22

Henry Minute

17-Sep-09 3:22

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.”

Sending Email using SQL Job

Payal_e_m16-Sep-09 14:22

Payal_e_m

16-Sep-09 14:22

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

Re: Sending Email using SQL Job

ElectricLlama17-Sep-09 23:41

ElectricLlama

17-Sep-09 23:41

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)

Re: Sending Email using SQL Job

Payal_e_m20-Sep-09 17:27

Payal_e_m

20-Sep-09 17:27

Can you tell me how to proceed(Using SQL job) with a CSV file please?

Thanks & regards
Payal

dbo Trigger

zeusToronto16-Sep-09 12:30

zeusToronto

16-Sep-09 12:30

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

Re: dbo Trigger

Ashfield16-Sep-09 20:58

Ashfield

16-Sep-09 20:58

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

Re: dbo Trigger

zeusToronto17-Sep-09 2:43

zeusToronto

17-Sep-09 2:43

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.

Re: dbo Trigger

Ashfield17-Sep-09 9:10

Ashfield

17-Sep-09 9:10

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

Re: dbo Trigger

zeusToronto18-Sep-09 2:43

zeusToronto

18-Sep-09 2:43

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.

Re: dbo Trigger

Ashfield18-Sep-09 21:27

Ashfield

18-Sep-09 21:27

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

Foriegn_key constraint (help needed urgently)

Learner52016-Sep-09 0:50

Learner520

16-Sep-09 0:50

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.

Re: Foriegn_key constraint (help needed urgently)

εїзεїзεїз16-Sep-09 5:12

εїзεїзεїз

16-Sep-09 5:12

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

Re: Foriegn_key constraint (help needed urgently)

Learner52016-Sep-09 5:53

Learner520

16-Sep-09 5:53

thanks a lot

Swap column Names

.NET- India 15-Sep-09 23:55

.NET- India

15-Sep-09 23:55

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.