I just inhertied a database. In it are multiple tables that keep track of a
users profile. In each table is a modified_date field. That date only
relates to that table and not the entire profile. There was never a central
modified_date for the user.
I need to be able to check the users profile to see when the last time they
made an update. I am looking for a way to query all the tables
modified_date and get the most recent one date.While you're busy preparing the DDL and some sample data, here's a wild gues
s:
select max(combined_set.modified_date) as last_modified_date
from (
select modified_date
,user_id
from table_1
union
select modified_date
,user_id
from table_1
union
select modified_date
,user_id
from table_1
-- ...add more tables here...
) combined_set
where (user_id = @.user_id)
ML|||If you don't like the way the question is presented then feel free not to
answer it. Adding wise ass comments doesn't help anyone.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:B395E91D-77AB-4739-8712-E7A8AAF15508@.microsoft.com...
> While you're busy preparing the DDL and some sample data, here's a wild
> guess:
> select max(combined_set.modified_date) as last_modified_date
> from (
> select modified_date
> ,user_id
> from table_1
> union
> select modified_date
> ,user_id
> from table_1
> union
> select modified_date
> ,user_id
> from table_1
> -- ...add more tables here...
> ) combined_set
> where (user_id = @.user_id)
>
> ML|||On Wed, 7 Sep 2005 15:07:23 -0400, Brian wrote:
>If you don't like the way the question is presented then feel free not to
>answer it. Adding wise ass comments doesn't help anyone.
Hi Brian,
If you don't like the fact that the professionals in this group prefer
to help as good as possible, and that this can only be done if the
specifications are very clear, feel free to take your questions
elsewhere.
I've seen lots of threads that start with a vague question, then the
first answer isn't correct because the question wasn't exact enough, etc
etc. That can go on for days. What a waste of time for everyone
involved!
ML really deserves better than your brush-off. Instead of just asking
for better specifications, he ALSO posted a query that (to me, at least)
looks like it might do the job for you. Did you already try it?
If ML's suggestions is not working for you, then please post better
specs: table structure (as CREATE TABLE statements), sample data (as
INSERT statements) and required results. See www.aspfaq.com/5006 for
more details, and some useful hints and links.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo -
Believe me I appreciate every answer I have ever gotten from this newsgroup.
But I see little snide comments like that all the time (in a variety of
newsgroups) and it isn't really necessary. People can simple say, as I have
seen, please provide 'this' information so we can get a better idea of what
you are really looking for.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:86iuh11vpom3uilukkv3kpuncjkdbue989@.
4ax.com...
> On Wed, 7 Sep 2005 15:07:23 -0400, Brian wrote:
>
> Hi Brian,
> If you don't like the fact that the professionals in this group prefer
> to help as good as possible, and that this can only be done if the
> specifications are very clear, feel free to take your questions
> elsewhere.
> I've seen lots of threads that start with a vague question, then the
> first answer isn't correct because the question wasn't exact enough, etc
> etc. That can go on for days. What a waste of time for everyone
> involved!
> ML really deserves better than your brush-off. Instead of just asking
> for better specifications, he ALSO posted a query that (to me, at least)
> looks like it might do the job for you. Did you already try it?
> If ML's suggestions is not working for you, then please post better
> specs: table structure (as CREATE TABLE statements), sample data (as
> INSERT statements) and required results. See www.aspfaq.com/5006 for
> more details, and some useful hints and links.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label track. Show all posts
Showing posts with label track. Show all posts
Friday, March 9, 2012
multiple stored procedures executed simultaneously
hi,
i have an application that uses a table to track the last invoice number used. there are around 50 users simultaneously trying to retrieve the invoice number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from the workstation, then send it back to the server via an UPDATE, there is the remote possibility of some users retrieving the same number before it being updated by the first user. (the UPDATE issued by the first user can sometimes be delayed due to slow network)
can i use stored procedures to solve this problem? i figured that if i make the SELECT and UPDATE into a single stored procedure, even if i have 50 users calling it all at the same time, each call (SELECT and UPDATE) must execute completely before the next user's call can be executed. there by making sure that the invoice number is incremented before the next user can retrieve it from the table. is this a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
> hi,
> i have an application that uses a table to track the last invoice number
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
> the problem is when i issue a SELECT to get the number, increment it from
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
> can i use stored procedures to solve this problem? i figured that if i
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
> thanks in advance.
> bob
>
i have an application that uses a table to track the last invoice number used. there are around 50 users simultaneously trying to retrieve the invoice number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from the workstation, then send it back to the server via an UPDATE, there is the remote possibility of some users retrieving the same number before it being updated by the first user. (the UPDATE issued by the first user can sometimes be delayed due to slow network)
can i use stored procedures to solve this problem? i figured that if i make the SELECT and UPDATE into a single stored procedure, even if i have 50 users calling it all at the same time, each call (SELECT and UPDATE) must execute completely before the next user's call can be executed. there by making sure that the invoice number is incremented before the next user can retrieve it from the table. is this a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
> hi,
> i have an application that uses a table to track the last invoice number
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
> the problem is when i issue a SELECT to get the number, increment it from
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
> can i use stored procedures to solve this problem? i figured that if i
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
> thanks in advance.
> bob
>
multiple stored procedures executed simultaneously
hi,
i have an application that uses a table to track the last invoice number use
d. there are around 50 users simultaneously trying to retrieve the invoice
number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from th
e workstation, then send it back to the server via an UPDATE, there is the r
emote possibility of some users retrieving the same number before it being u
pdated by the first user. (
the UPDATE issued by the first user can sometimes be delayed due to slow net
work)
can i use stored procedures to solve this problem? i figured that if i make
the SELECT and UPDATE into a single stored procedure, even if i have 50 use
rs calling it all at the same time, each call (SELECT and UPDATE) must execu
te completely before the ne
xt user's call can be executed. there by making sure that the invoice numbe
r is incremented before the next user can retrieve it from the table. is th
is a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
i have an application that uses a table to track the last invoice number use
d. there are around 50 users simultaneously trying to retrieve the invoice
number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from th
e workstation, then send it back to the server via an UPDATE, there is the r
emote possibility of some users retrieving the same number before it being u
pdated by the first user. (
the UPDATE issued by the first user can sometimes be delayed due to slow net
work)
can i use stored procedures to solve this problem? i figured that if i make
the SELECT and UPDATE into a single stored procedure, even if i have 50 use
rs calling it all at the same time, each call (SELECT and UPDATE) must execu
te completely before the ne
xt user's call can be executed. there by making sure that the invoice numbe
r is incremented before the next user can retrieve it from the table. is th
is a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
quote:
> hi,
> i have an application that uses a table to track the last invoice number
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
quote:
> the problem is when i issue a SELECT to get the number, increment it from
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
quote:
> can i use stored procedures to solve this problem? i figured that if i
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
quote:
> thanks in advance.
> bob
>
Subscribe to:
Posts (Atom)