|
You replied to my reply - you may want to move this to the OP so as to get/give some feedback
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ello everyone.
I have an application that communicate with a database in sql server 2005, sometimes I have sql query that fails knowing that they are simple requests for updates. I give an example
I have a person table with a column name
I happen to change the names properly, may at one time given a specific name, a clear line in my table is blocked.
when looking around the net I have found that piece is a lock problem.
because once I restart my application everything works well with this line, but I still fall on another line that is blocked.
the query is:
[Code] SELECT
*
FROM
master.dbo.sysprocesses processlist
INNER JOIN master.dbo.sysdatabases DATABASELIST
ON PROCESSLIST.dbid = DATABASELIST.dbid
WHERE
(DATABASELIST.name = N'VISION_DB ')
[/ CODE]
lastwaitintype=LCK_M_X or LCK_M_X
I started with sql server and I do not know how to not have to lock every time
|
|
|
|
|
First of all I am not a database developer and therefore I may well have misunderstood your question.
However:
Morad SAJID wrote: I have a person table with a column name
what is the name of the database containing the person table?
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.”
|
|
|
|
|
Maybe try defining an explicit transaction before you do your updates and use a Try ... Catch block for error handling. If the update fails then you can execute a rollback, if the update succees, then execute a commit. By using explicit transactions, you should be more confident that locks are not staying around unwanted.
Database updates require locks to be performed, this is unavoidable. Give it a try. 
|
|
|
|
|
hello
i am using SQL server 2005 as my back end
now i want report of like from this date to this date...for that i wrote query like
format of my date is dd/MMM/yyyy
If optn1.Checked = True Or optn3.Checked = True Then
sqlstr="select * from des_mast"
sqlstr = sqlstr & "where pickup_date between '" & Me.dtp1.Value & "' and '" & Me.dtp2.Value & "'"
End If
but while executing this query shows an error message like
"incorrect syntax near the keyword 'between'"
please suggest i cant get where the problem is
thank you
|
|
|
|
|
Try to debug and follow debugger line and then you will find the problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
nazimghori wrote: format of my date is dd/MMM/yyyy
The format of your sql or the format of your datetimepicker?
nazimghori wrote: If optn1.Checked = True Or optn3.Checked = True Then
You dont need to compare a booleanvalue with true or false
Just do it like this:
If optn1.Checked Or optn3.Checked Then
Or even better:
If optn1.Checked OrElse optn3.Checked Then
nazimghori wrote: sqlstr="select * from des_mast"
sqlstr = sqlstr & "where pickup_date between '" & Me.dtp1.Value & "' and '" & Me.dtp2.Value & "'"
After concatenating this, i dont think youll get a space between des_mast and where, youll get des_mastwhere
Also, have you tryed using # for the date values: between #12/12/2009# and #12/12/2009#
If you still cant get it to work, you can test manually the query in sql server managment studio
Alexei Rodriguez
|
|
|
|
|
Hi to all,
Here is a problem I am facing in a table design in ms access.I am accessing the database through vb.net application.
All I need to get an efficient way to enter the marks for each students for each subject.Now, my number of subjects may increase or decrease.
Can I do this by adding or deleting the columns in the table(student which will contais all the details of the students with subjects as its column). If so please provide me the syntax for altering a table(adding /deleting columns)
Please advice me, if any other efficient method exist for the same.
Thanks in advance.
HimangshuS
-----------------------------
I am a beginner
|
|
|
|
|
Instead of altering the table that contains the student and his scores per class, create 2 tables. Students and StudentScores
The Student table could look something like this:
StudentID (primary key)<br />
Name<br />
GraduatingYear<br />
etc
The Course table would be:
CourseID (primary key)<br />
CourseName
The StudentScores table would look something like this:
StudentID (combination of StudentID,CourseID and CourseDate would be the primary key)<br />
CourseID<br />
CourseDate <br />
Score
By introducing a CourseDate, the student could take the course multiple times and have different grade for each time it was taken.
Also, this design allows for an unlimited number of Courses and each Student could take any number of courses offered.
This design is by no means complete, but it should give you something to think about.
Good luck with your project.
David
|
|
|
|
|
thanks a lot David,
I think thats the solution...Thanks a lot.
But what do you mean by
"This design is by no means complete"
Scared of that..
Please do reply for that. Since I need to create those table..
regards,
HimangshuS
-----------------------------
I am a beginner
|
|
|
|
|
You may want to inlcude more information in the tables than I have given, for example in the Student Table you could have the parent's name and contact numbers. In the StudentScores table you may want to store multiple scores, quizes, tests, mid-terms, final, etc. I don't know exactly what you need, but I'm sure you can think of ways to expand this basic design.
|
|
|
|
|
Hi,
I just wanted to know that is it possible to retrieve data without rollback or declaring any transaction point in sql 2005.
Rock Star
|
|
|
|
|
Yes.
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.”
|
|
|
|
|
So how can I solve this issue.
It will be great if you can guide me for it.
Thank you!
Rock Star
|
|
|
|
|
The answer is to not declare any transaction, just do the select.
For example: select ID,FullName from Customer
This will retrieve the data and not create any implicit or explicit transactions. No need to commit or rollback anything.
|
|
|
|
|
Thanx for reply
My question here is that consider I excuted an update query on a table without defining any transaction point, but now I want to roll back this update query so I can recover my data. How can I do this?
Thank u!
Rock Star
|
|
|
|
|
You need to do some work on your question construction b/c thats not what anyone would have though the OP was about.
If you are talking about recovering from an error in the statement then a transaction is required, if there was not transaction you are gone.
If you want to recover from an incorrect update you may be able to restore the database from a backup or possibly by working the transaction logs. Restore the DB to another SQL instance and compare the data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have 3 tables.
1. A contact table
2. A company table
3. A company to contact association table.
If a company to contact association is created, a field, 'CompanyName', in the contact table needs to contain the name of that company (in order for our mailing lists to print correctly).
I've been trying to write a query which will fill in all of the contact table 'CompanyName' values, with all of the corresponding values in the company table.
The company to contact association table is the link between the two so:
1. I need to determine which contact's have an association with a company
2. I need to get the companyID from the association table, and get the company name
3. I need to set the contact company name to the retrieved value.
Only problem is... IM STUCK!!!
This is what i've done so far.
<br />
<br />
SELECT Contacts.CompanyName<br />
FROM MarkSandpit.dbo.Contacts<br />
INNER JOIN<br />
MarkSandpit.dbo.Associations_Companies_to_Contacts<br />
ON Contacts.mxContactID = Associations_Companies_to_Contacts.mxContactID<br />
INNER JOIN mxContact.dbo.Companies<br />
ON Associations_Companies_to_Contacts.mxCompanyID = Companies.mxCompanyID<br />
<br />
This just gives me a list of all the company names in the contact table, which have associations with a company.
Could some please help me out?
Cheers,
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
HI GUYS
I have installed sqlserver 2005 exp with advanced services and toolkit , but i cant see
report server ,
can any body tell me how to install a report server ?
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
|
Hi, thanks for looking
Im trying to make a query that will show me something like a report
I have a Homes table and a History table
Table Homes: Info about homes
Col: HomeID
Col: AddressID
Col: ...
Table History: A record for every status in wich a home has been
Col: HistoryID
Col: HomeID
Col: StatusID
Col: DateStarted
Col: DateFinished
Col: ...
A home can have up to 4 rows in the History (Status 1 - Status 4), what i need to do is a query that return something like this:
HomeID - Status1 - Status2 - Status3 - Status4
1 - 10/10/09 - 10/12/09 - 10/14/09 - 10/18/09
2 - ................
Here, im showing a home and the DateFinished column from the History table
In case that a home doesnt have statusX, null is ok
I was trying to create this view with the view designer, first i draged the Homes and History and made the query by filtering history with status1, so i got homeid and status1, then i had to redrag history with an alias and filter status2 to get status2 and so on
It seems really unefficient this way
Please let me know if you know another way to do it
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
Hmmm I presume you are using SQL Server 2005/8. Try this article [^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can try something similar to this.
select
homeid,
max(case when statusid=1 then datefinished else null end) status1,
max(case when statusid=2 then datefinished else null end) status2,
max(case when statusid=3 then datefinished else null end) status3,
max(case when statusid=4 then datefinished else null end) status4,
from history
group by homeid;
<edit>fixed typo</edit>
modified on Friday, May 15, 2009 4:36 AM
|
|
|
|
|
Thanks lot!!!
This reduced my query a lot
I just have a question
What is the max for?? 
I removed the max as well as the group by and still works
Thanks again
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: Thanks lot!!!
You're welcome
AlexeiXX3 wrote: What is the max for??
I removed the max as well as the group by and still works
In case you have duplicated values in the table, the query will then select the last date (use Min() for the first date).
This is of course not necessary if you have HomeID and StatusID as a composite key in the table.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
