|
You can use WITH ( ROWLOCK ) after the table name in the FROM clause. However, you should consider whether you should be holding your transactions open for user interaction. It's usually better to select the results and release the locks, then perform comparisons to determine if the row has changed when the user wants to update. It's up to you what you do if another user has changed a row since this user started editing.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I would recommend not using locks. What I do is use a time stamp column. During the update stored procedure, I do a check to make sure the date time stamp is equal to the actual value in the table for that record. If the time is the same, I update the column to the current date time prior to the actual update. If not, I raise an error, then requery the table information for the last change user and date time.
Again, I would not recommend do any type of locks.
Just my 2¢,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
This might help someone else out; in any event, I wanted to get this off my chest.
Some s/w I've worked many, many hours on uses the ATL OLEDB classes to communicate with a database, which is specified by a connection string. When we use the SQLOLEDB provider against MSSQLServer, we have no problems (well, not with the database!). However, using OraOLEDB.ORACLE against Oracle 9.2 gives some really bizarre behaviour.
Normally, if you want an updateable rowset, you just specify (DBPROP_IRowsetChange, true) and (DBPROP_UPDATEABILITY, flags).
Open your rowset, call SetData() to update, and all goes well.
However, with Oracle, if your rowset contains blobs (long data), then you also need to specify (DBPROP_IRowsetUpdate,true), which *should* give you a deferred update rowset, meaning that you need to call SetData() and Update() to update the row. Now (a) you don't need to call Update(), just SetData works! and (b) this doesn't work for SQL Server 
All in all, a sorry little mess.
Thankfully, it's possible to determine from a CSession object whether we're connecting to an ORACLE database or not, and thanks to the ability produce a template, the rowset property setup and updating of the data can also be hidden from the 'casual developer'. But  isn't OLE DB intended to take care of this crud for us? Come back ODBC, all is forgiven....
Steve S
My, that feels better. What will feel even better, of course, is leaving this job on 30th April - now if only I had somewhere to go....
|
|
|
|
|
Ahhhhhh....Many Oracle problems you still have grasshopper....
Ah well - 30th not long to go....still nothing in pipeline?
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I am using ADO recordsets in my C++ program. I am adding a new record by calling AddNew method of recordset object. Now, I dont' want to override any default values set for a particular column while adding the records. How can find whether default values are set for a column, and if set, how can I get the default values. what are the methods available for that?
Thanks in advance
Shenthil
|
|
|
|
|
Hi,
As part of a Project I have to create a Database Software, with MS Access as the Back end and use VB for the front end. I have to go right from the basics. Is there any good tutorials or documents available on the net that I could use. I would really appreciate if someone would pass it on.
Regards,
Ajit
"Insanity is my Birth Right!!!!"
|
|
|
|
|
|
How can i open an access-file (database.mdb) with DAO using a special systemdb/workgroupfile (database_sys.mdw) ???
if i use ODBC i can defien a systemdb in de ODBC-connect-string ... but it doesn't work with DAO !!!
thx a lot
cr97
|
|
|
|
|
|
thanx ...
But how can i do that in C++ ???
i found this ...
void CDaoWorkspace::Create(LPCTSTR lpszName, LPCTSTR lpszUserName, LPCTSTR lpszPassword)
{
...
_AFX_DAO_STATE* pDaoState = AfxGetDaoState();
if (pDaoState->m_pDAODBEngine == NULL)
InitializeEngine();
...
}
and i know that DAODBEngine has the method ...
pDaoState->m_pDAODBEngine->put_SystemDB(THIS_BSTR SystemDBPath);
Can i call this like it is to change the SystemDB ???
And i can't use this AfxGetDaoState() ... it's unknown ...
mhg cr97
|
|
|
|
|
|
Yes i think it would help !!!
I found this article yesterday ...
thank you !!!
cr97
|
|
|
|
|
Could someone help me construct an SQL query which will conditionally select records from multiple tables which share the same schema, without joining every compatible record onto the end of each (creating a total mess) like "select * from <table1>, <table2> where 1" does. Placing the name of the table as a field for each would be a bonus (Im using mySQL)
The database I have is quite unusual as it contains a lot of binary-serialised data which has been categorised into a lot of different tables (for instance one table for records beginning with A, B, C etc), for (I expect) high-performance and easier management. If there is any other tweak you can think of that'll allow better querying on a collection of similar tables, your input would be welcome.
|
|
|
|
|
Hello,
I've got a table called samples. This table has three fields called PicID1, PicID2 and PicID3 for simplicity. My second table is called Pics. Pics has an ID field (key) and a PicName field. The select query I'd like to write selects all records from the Samples table and the PicName value for each of the PicIDs (1,2 and 3):
[Samples]
ID|PicID1|PicID2|PicID3
1|345|664|733
[Pics]
ID|PicName
..
345|Himalaya
664|Sahara
733|New York
The result should return a recordset containing all records from the samples table and "instead of the numeric PicID*" the Pics.PicName.
Can this be done in one query? If so, how?
Thanks in advance,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
SELECT Samples.ID, P1.PicName AS PicName1, P2.PicName AS PicName2, P3.PicName AS PicName3
FROM
Samples
INNER JOIN
Pics P1 ON Samples.PicID1 = P1.ID
INNER JOIN
Pics P2 ON Samples.PicID2 = P2.ID
INNER JOIN
Pics P3 ON Samples.PicID3 = P3.ID
-- insert WHERE clause here Basically, all you have to do is to alias the repeated table name.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, that's exactly what I was looking for!
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
hi
use subqueries.
Select Samples.ID
,(Select Pics.PicName where ID=Samples.PicID1) as FirstPic
,(Select Pics.PicName where ID=Samples.PicID2) as SecondPic
,(Select Pics.PicName where ID=Samples.PicID3) as ThirdPic
but i prefere that you re-design the database and create a link table that holds the SampleId and the PicID
Table:Sample_Pics
SampleID | PicID
1 |1
1 |2
1 |3
2 |4
------------------
etc.
this will allow you to have a variable number of pics per sample not just 3.
|
|
|
|
|
I've been thinking about this, but if fact, there will never ever be a situation when there is more then three 
Thanks anyway,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Hello everybody ...
Well, I have a problem in something with SQL Server 2000 ...
I want to write a procedure in stored procedure in SQL Server 2000, this stored procedure should output a variable holding the number of records in the table.
ex
select count(*) from tbl
where tblAccount = 2828;
I want this procedure to output the number of records retrieved, and i want to get it from ASP page. Actually i used a command object in ASP to execute my procedure, and with a recordset.
rs.open cmd
but how can i get the output from this procedure???? and how can i write like this procedure in SQL Server 2000 ????
Best Regards
Sam
Sam2004
|
|
|
|
|
If you've used SELECT and opened a Recordset, the results will be in the Recordset object. The recordset has the concept of the current row - the Value property of the Field objects in the Recordset's Fields collection will contain the retrieved value of that field in the current row.
Straight after opening, if any rows were retrieved, the Recordset object's current row will be the first row returned. If no rows were retrieved, the EOF property will be False.
Since you haven't given the field a name in the result set, you will only be able to access it by position, i.e. as rs.Fields(0). To give it a name, use
SELECT COUNT(*) AS RowCount
FROM tbl
WHERE tblAccount = 2828; As for the procedure, the easiest way is to send the SQL explicitly to the server, by setting the command object's CommandText property. This isn't the best performing method, though; look up the documentation on stored procedures and parameters.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi
how to know the sapce used in the data file or log files ?
the sp_spaceused stored proc gets the space used by a database or object.But what i need is the space used by in each data or log file.
how to get this info ?
|
|
|
|
|
You can use your programming language to get size of your database and its log file.If its C# you can use FileInfo.Length and if it is C++ you can use GetFileSize().
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
thank you .. but seems that my question wasn't clear enough.
I can get the file size (OS file size) using sp_helpFile. But this is the total space allocated for the file..
What i need is to know how much actual space consumed by data or log within the file..
for example you can allocate a 50MB file while the data actually use 20MB. In this case i want to get 30MB.
|
|
|
|
|
USE database
EXEC sp_spaceused I can't find a way of specifying which database you want to look at, other than just selecting the database with USE.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hello,
DB Admin is not one of my stong points so I'm a bit stuck. I have a development database that has almost filled the disk. I want to reduce the file sizes so I can continue development (a process I left running over the weekend has increased the log file by 10Gb - It didn't work so I have to fix and rerun it and I only have 5Gb left)
Can anyone let me know how to reduce the file sizes back to something sensible (the operation I was running modified about 17% of the records and deleted 83% of the records it was working on). I tried Shrink Database but it reports that it has shrunk the database by a few hundred Mb but the files are physically the same size. 
Any help would be much appreciated.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
