|
Mika Wendelius wrote: what's the biggest pain in this?
(patient=OP, symptom=Headache) 
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
It took me awhile to get this since I had completely forgotten the context... After reading the original post I got it. Think I'm 
|
|
|
|
|
I would like to rebuild the indexes of my database by using the query 'ALTER INDEX ALL'. With this I would like to set the option SORT_IN_TEMPDB to ON. But before that I want to know the value set for this particular flag in the table indexes. How can I get the current value of this flag?
I tried using the tables like sys.indexes, sys.stats, sys.schemas etc but miserably failed.
Please advice
With Regards,
Abraham Chethuvelil
|
|
|
|
|
AFAIK this information isn't stored when index is created or recreated.
|
|
|
|
|
Hi,
Then how do we know what is the value for this flag?
I want to set it on in our database now but I am not sure whether the people who created or maintained this database before were already set it or not?
Another doubt regarding setting this flag is that, will it cause any issues in the future w.r.t. Size or performance and w.r.t. Tempdb as well?
With Regards,
Abraham Chethuvelil
|
|
|
|
|
Arun Abraham Jose wrote: Then how do we know what is the value for this flag
I don't think it's possible to say afterwards if an index is created with SORT_IN_TEMPDB On or Off. Since this option affects only when the index is created, it isn't necessary information for SQL Server when index is used.
Arun Abraham Jose wrote: I want to set it on in our database now but I am not sure whether the people who created or maintained this database before were already set it or not
Since this isn't found in the system tables the only thing that comes in mind is that you create a standard that you add an extended property to your indexes. In this property you can document what option was used when the index was created.
Property can be set for example like this:
EXEC sys.sp_addextendedproperty
@name=N'SORT_IN_TEMPDB',
@value=N'True' ,
@level0type=N'SCHEMA',
@level0name=N'DatabaseName',
@level1type=N'TABLE',
@level1name=N'TableName',
@level2type=N'INDEX',
@level2name='IndexName'
After adding this property you can see it's value in system tables and in Management Studio.
Arun Abraham Jose wrote: Another doubt regarding setting this flag is that, will it cause any issues in the future w.r.t. Size or performance and w.r.t. Tempdb as well?
SORT_IN_TEMPDB affects only index creation. It does not affect how the index behaves after creation so it's creation time option only.
|
|
|
|
|
OK, I'm not a DB expert but I wanted to do the following and I'm not sure how to go about it. I googled but I think I'm using the wrong name for it.
I have a table that has a foreign key (PatientID) and there is an ID that is Patient relative called AdmittanceID. Now for the sake of clarity I'll give an example of what I want done.
Imagine two patiens, 10 & 20
{Table}
PatientID AdmittanceID
10 01
20 01
10 02
now, the AdmittanceID would autoincrement for each distinct PatientID. I'm stumped as to how to do this in SPs.
Suggestions?
|
|
|
|
|
You could do this with triggers. Depending on the business rules you would have a bit different implementations. For example if new record gets the maximum value for patient + 1, you would have something like:
CREATE TRIGGER SomeTriggerName
ON YourTableName
AFTER INSERT
BEGIN
DECLARE @keyValue int;
DECLARE @patientID int;
DECLARE newRows CURSOR
FOR SELECT PrimaryKeyColumnName, PatientId
FROM inserted;
--
OPEN newRows;
FETCH NEXT FROM newRows INTO @keyValue, @patientId;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE YourTableName
SET AdmittanceID = (SELECT MAX(AdmittanceID)
FROM YourTableName
WHERE PatientID = @patientId)
WHERE YourTableName.PrimaryKeyColumn = @keyValue;
--
FETCH NEXT FROM newRows INTO @keyValue, @patientId;
END
CLOSE newRows;
DEALLOCATE newRows;
END
And if you need that the patientid does not have gaps, you would create a delete trigger where you reorder numbering for records having the same patientid as deleted row(s).
|
|
|
|
|
Brilliant!
Thanks Mika, that is exactly what I needed!
|
|
|
|
|
You're welcome 
|
|
|
|
|
Quick question.
PatientID is a key, but the primary key would be the combined PatientID and the AdmittanceID, this shouldn't affect the implementation correct? Also, why do I need the cursor? Shouldn't the quick select statement in addition to incrementing the value by 1 be sufficient?
There shouldn't be gaps because the values increment for each patient, and each patient's visit is mutually exclusive of the other unless my implementation is way wrong.
[edit]
The way I see it, it can't be done as such, it needs the SP that's doing the insert to find the last admittanceID, increment that and insert it with the rest of the data.
Last modified: 17mins after originally posted --
|
|
|
|
|
Mustafa Ismail Mustafa wrote: the primary key would be the combined PatientID and the AdmittanceID, this shouldn't affect the implementation
Actually it does. If it's defined as primary key, you cannot leave it null in the insert statement. Also you cannot insert duplicates. One way could be that you use negative patientid in the insert statement and this trigger modifies the values to correct ones. In that case the where clause would be something like:
...
WHERE (AdmittanceId, PatientId) = (@OldAdmittanceId, @PatientId);
Also it would be benefitial to have a surrogate key if none is present at the moment. That would make this operations easier. You could change the current primary key to unique key and define the surrogate key as primary key.
Mustafa Ismail Mustafa wrote: Also, why do I need the cursor
I didn't quickly have any other idea. The problem is that the trigger fires only once when rows are added regardless how many rows are added by the same statement. In other words the trigger isn't executed separately for each inserted row. Now if you add simultaneously two rows for the same patientid and you simply execute an update which sets the patientid to current max in db + 1 those two rows would have the same value for the patientid.
Mustafa Ismail Mustafa wrote: There shouldn't be gaps because the values increment for each patient
Ok, but what I meant is that is it ok to delete a record later? If it is, I think you would have a gap in patientid numbering. Don't know if it's any problem though.
|
|
|
|
|
I ended up doing this:
<br />
@PatientID int, <br />
@AdmittanceID int, <br />
@DateAdmitted datetime = GETDATE,<br />
@NurseNotes int,<br />
@DoctorNotes int,<br />
@MiscNotes int<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
<br />
SET @AdmittanceID = (SELECT MAX(EMR.PatientHistory.AdmittanceID) <br />
FROM EMR.PatientHistory <br />
WHERE EMR.PatientHistory.PatientID = @PatientID);<br />
<br />
SET @AdmittanceID = @AdmittanceID + 1;<br />
<br />
INSERT INTO EMR.PatientHistory<br />
(PatientID, AdmittanceID, DateAdmitted, NurseNotes, DoctorNotes, MiscNotes)<br />
VALUES<br />
(@PatientID, @AdmittanceID, @DateAdmitted, @NurseNotes, @DoctorNotes, @MiscNotes)<br />
END<br />
|
|
|
|
|
This one is also fine.
If you want, you can combine those statements to a single statement like:
INSERT INTO EMR.PatientHistory
(PatientID, AdmittanceID, DateAdmitted, NurseNotes, DoctorNotes, MiscNotes)
SELECT @PatientID,
MAX(EMR.PatientHistory.AdmittanceID) + 1
@DateAdmitted,
@NurseNotes,
@DoctorNotes,
@MiscNotes
FROM EMR.PatientHistory
WHERE EMR.PatientHistory.PatientID = @PatientID
|
|
|
|
|
wicked cool, thanks for everything Mika
|
|
|
|
|
No problem
|
|
|
|
|
i have some packages code in oracle now i want to write the alternate of these packages in the sqlserver but how this can be implemented in the sqlserver since sqlserver does not support packages
|
|
|
|
|
There's no similar mechanism in SQL Server so you would have to create separate procedures and functions in SQL Server. Also when calling them, the notation is different since you don't have the package to reference.
|
|
|
|
|
tq mr Mika wendelius will u give one sample example on this query or a site to view the examples on this one
|
|
|
|
|
|
I've been asked to write a program for a small company. They will have just a few users (1 to 4) accessing a database. The database will probably grow to just 20,000 records at the most. If multiple users are accessing simultaneously and one of them adds or updates a record, they want the screens of the other users to be refreshed and reflect the newly added data.
My thought is to write the application in MSVC++ and connect to an MS Access DB using ODBC. My dilemma is in deciding the best way to handle the updating of all the users screens when one user has added a record.
1) I could have the application spawn a thread, which would kickoff a timer. Say every 20 seconds, the timer would run a query against the DB and see if any records have been added. If so, it's time to refresh the data here on this machine.
2) I could also maybe write a service that would run on the server with the database. The application on each users machine would interact with this service, not the database. The service would accept all the requests from the users (clients), interact with the DB and respond to the clients. If a record is added or updated, the service would send a msg or invoke a callback on the client and tell it to update.
3) I'm not sure about this, but I think there also might be some broadcast function/message that I can use.
Does anyone have any suggestions or warnings or previous experience with this?
Thanks very much!
Brian
|
|
|
|
|
Few thoughts:
Member 3822922 wrote: I could have the application spawn a thread, which would kickoff a timer
This should work fine. I've used similar technique earlier with no problems.
Member 3822922 wrote: I could also maybe write a service that would run on the server with the database
This is a slightly modifed version from the previous and in my opinion it would add components and logic to your application. Unless the refreshing is time critical, I doubt if this version is worth the effort.
Member 3822922 wrote: I think there also might be some broadcast function/message that I can use
This is beyond my knowledge, but I have a feeling that this also could cause (much) work, so is it worth it.
Don't know if you've made the final decision for the products to use, but have you considered using SQL Server. I admit that it would cause a little more work when building the environment, but also it has some benefits compared to Access, like:
- true database engine so you would have much more options in implementation
- better backup mechanisms (online backups, log backups if needed etc)
- much better performance
- far better concurrency etc.
What comes to the options, for example one thing is that .Net Framework has SqlDependency[^] class which can be used to receive notification when the data changes. This would eliminate the need for timer based polling.
Since I don't know anymore about the program I'm not able to give more examples but I'm quite confident that productivity would be greater when using SQL Server since .Net Framework has large amount of specialized classes for use with SQL Server. As far as I can see there's nothing similar against Access so you would have to use basic ODBC, as you mentioned.
|
|
|
|
|
I just wanted to thank you for responding (back on 1/27) to my question at Code Project. The company decided not to proceed.
Anyway, thank you -
Brian
|
|
|
|
|
Member 3822922 wrote: connect to an MS Access DB using ODBC
Not OleDb?
Member 3822922 wrote: they want the screens of the other users to be refreshed and reflect the newly added data
Sounds awful. Why would this be required? I prefer not to keep a bunch of data on the clients.
I would likely go with 1. You could also have the insert/update/delete methods update a table of timestamps, so when a client looks for changes it can query the timestamps to know which (if any) data has changed.
|
|
|
|
|
I just wanted to thank you for responding (back on 1/27) to my question at Code Project. The company decided not to proceed.
Thanks again,
Brian
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
