Database help

hii have a DB scenario, it may be basic to u, but i am new to this so plz explain in details or if you can provided links to related articles that will be great.

i have sql server 2000 and i am using dataset on my asp.net application. now as Dataset is connenctionless, what will happen if i have filled my dataset with some records from database and updated those records in dataset. in that time some other user deleted some of those records from database. now when i will try to batch update database using my dataset (with records that has been deleted from DB) what will happen? will i get an exception or what. plz explain in detail . any links to articles will be great.

my second question is same, what are the methods to lock database if one user is accessing (updating) one record, so that other user dont get to change it at the same time? do i have to manage it in Database or what.

thank you in advance

regards

Hi,

I think this article will explain a lot

http://www.asp.net/learn/data-access/tutorial-21-vb.aspx

|||

Hi zeeshannasir,

now as Dataset is connenctionless, what will happen if i have filled my dataset with some records from database and updated those records in dataset. in that time some other user deleted some of those records from database.

My question is: what do you mean by "dataset is connectionless"? Do you mean that your database has been detached in your database engine? Well,in that case your database won't be accessible at all so you(and all the other users) cannot modify your database.

As to your second quesion, actually you don't have to worrry about it.All the transactions in SQL has been designed to comply with theACID (Atomicity ,Consistency, Isolation Durability) compliance.For example, if user A is currently opening up and reading the info. of a particular database, and meanwhile, user B is trying to open up the same database. Since there is no modification being made to the database, user B is allowed to open up. However, if user A is writting new data to the database, all the operations user B makes will be denied.

Hope my suggestion helps

|||

hi Bo Chen

what i meant by "dataset is connectionless" is that, i filled my dataset with required data using a stored procedure. now i am not connected to database, plz correct me if i am wrong. any changes i made to dataset is local until i dont batch update manually, is it correct. now what will be the scenario if this is the case, i have filled my dataset and made some changes and while i was doing that someone else also access the same database and changed some of the records.

plz correct me if i am wrong

thanks

Database engine tunning advisor and triggers

Hi,

I captured a trace using the profiler and provided the trace to the tunning advisor for analysis of the indexes.

I am not convinced that it processes the triggers properly. I used the tunning template on the profiler and confirmed that the code from the triggers is captured.

However, in the tunning advisor I am getting an error message like this:

E000

INSERT INTO SP ( FkSID, FkRPID, FkSPStID, NPR )
SELECT SID, RPID, 0, NPR
FROM INSERTED INNER JOIN RP ON
INSERTED.FkCID = RP.FkCID
WHERE 0 = INSERTED.IA

2 [Microsoft][SQL Native Client][SQL Server]Invalid object name 'INSERTED'.

From the above it appears that the advisor does not recognise the "inserted" temp table used in an insert trigger. Is there anyway to have the advisor consider this code as well or am I doing something wrong?

Thanks

Jose Fortuna

DTA should suppose to identify them as a trigger based tables, anyway as a test have you tried another trace capture and try the DTA again. If this persists again then use Connect page at microsoft to report the bug.|||

Hi Satya,

Are you saying that DTA should handle such cases?

Jose

|||

Yes, it should.

Edit:

After I replied here I have checked the same again and it seems this is 'not' working me either and I think due to the fact INSERTED or DELETED tables are temp. tables when a trigger is initiated. When DTA is checking for such values using the above reference it is unable to find the referred tables. So I would expect the behaviour is by default, to get a resolution I believe you need to assign to a permanent tables on trigger.

HTH

|||

Hi,

Further to the above, if you include in the code the creation of the trigger than all is correct, that is, the inserted temp table is recognised.

Since the triggers are an important aspect of database performance, it is a real shame that these cannot be analised through a profiler capture.

Thanks for the help provided here.

Jose

Database design

I am creating database tables for company testimonials. Database columns: name, position, companyname, comment, service we provided.

My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments.

What is best practice for putting this db structure together?

Thanks

Andrew

You have 4 principal objects: Company, Person, Service and Testimonial. That leads to 4 tables:

Company
CompanyIDPK
Address1
Address2
etc...

Person
PersonIDPK
Title
Initials
FirstName
Surname
Position
CompanyIDFK
etc...

Services
ServiceID
Service

Testimonials
TestimonialIDPK
Testimonial
PersonIDFK
ServiceIDFK

So now each testimonial is linked to a person and a service, and each person is linked to a company, so a testimonial is linked to a company through the person. You can add another table:

CompanyServices
CompanyIDFK
ServiceIDFK

to manage the relationship between companies and all the services they avail themselves of, regardless of whether they comment on it.