Help on scheduling a data change ??

Dear all,
I have a table with a certain amount of data. One of the data column is
named STATUS. by default all entry have a status of 5 (which means for the
client application a waiting states)
I need to schedule the change of this status from 5 to 1 automatically.
For example I would like that status value for all records with 5 is chaged
to 1 on a particular date and time.
How can I do that ?
regards
sergeHi
UPDATE Table SET status=1 WHERE status =1
AND GETDATE()>='20050101' AND GETDATE() <'20050201'
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Correction
Should be
UPDATE Table SET status=5 WHERE status =1
......
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> Hi
> UPDATE Table SET status=1 WHERE status =1
> AND GETDATE()>='20050101' AND GETDATE() <'20050201'
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
the
> chaged
>|||May be you should check out "Scheduling Jobs" in BOL.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Thnaks for the tip.
Where should I store that SQL querry ?
Does it have to be in a store procedure and then run that store procedure
from a schedule task ?
"Uri Dimant" wrote:

> Correction
> Should be
> UPDATE Table SET status=5 WHERE status =1
> .......
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> the
>
>|||>Does it have to be in a store >procedure and then run that store >procedure
>from a schedule task ?
Yes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> Thnaks for the tip.
> Where should I store that SQL querry ?
> Does it have to be in a store procedure and then run that store procedure
> from a schedule task ?
>
> "Uri Dimant" wrote:
>
is
for
automatically.|||Hi,
If I use the task scheduler to run the store procedure, I do not need the
GETDATE function, the UPDATE is enough right ?
"Uri Dimant" wrote:

> Yes.
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> is
> for
> automatically.
>
>|||You could just store the creation date in the table and then derive the
Status from the date when you query the data.
David Portas
SQL Server MVP
--|||That was the idea I get at first.
Something like storing the desire date/Time when the status needs to be
changed and then update teh status when the date/time field is corresponding
to current date.
But then if I do that I can use different methode to check this date.
Either I can do it in my client VB application or on the server itself.
But if I do it on the server side what shoudl I use to pol the current date
and check if it correspond to the one store in the table and then if it is
update the field.
Can we make some loop in server procedure ?
Sorry I am new n that and could not get the proper way
thnaks for your help
serge
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>|||What do you mean by derive the status '
by the way I have try to run a script from a new job that I schedule at a
certain time. If I run the script from the querry analyser it works fine, bu
t
when I put it as a new job and load the script file to execute, the set the
time to schedule it, it is not updating or may be not runing at all.
Script is :
EXEC dbo.UpdateStatus
GO
and UpdateStatus is a store procedure
Any idea?
thaks
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>

Help on #911 Cant locate entry in sysdatabases...

Could not locate entry in sysdatabases for database 'TEST'. No entry found with that name. Make sure that the name is entered correctly.

I got the above error from time to time and like to do some troubleshooting and in vain...hope I can get some help or hints here...
Thanks a lot.

WDid you rename the database ? Which version of sql server are you using ? Is it your login that receives this error or other logins ?|||I have a new database and use it for a library server and through the installation program, it will create admin account on sql server for that database...at this point, I got the above error. I didn't do anything to the new database...

Can't see anything strange in Profiler...anyone out there can help?|||What are the details of the installation program/process ?

Help Me with This Query!

Hi All,

I have this query and I want to show single entry on the every records, I already use Group By, but it doesn't solve the problem.

Please help to find the problem.

=============================================================================================

SELECT EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal,SUM(TotalOrder)as TotalOrderFROM

(SELECT

e.OfficeCode,

(SELECTDISTINCT OfficeFROM OfficeCode ocWHERE oc.Code= e.OfficeCode)as Office,

e.OfficeCode+'-'+ e.EmployeeCodeas EmployeeCode,

(SELECT FirstName+' '+ LastNameFROM Person pWHERE p.PersonID= e.PersonID)as EmployeeName,

InvoiceTotal=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENSUM(o.OrderFee)ELSE 0END,

TotalOrder=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENCount(o.OrderID)ELSE 0END

FROM Employee e

LEFTJOIN oms.[OrderAssignment] oaON e.EmployeeID= oa.EmployeeID

LEFTJOIN oms.[Order] oON o.OrderID= oa.OrderID

LEFTJOIN OfficeCode ocON oc.Code= e.OfficeCode

WHERE e.OfficeCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.OfficeCode,','))AND

e.IsManager='True'AND

e.DivisionCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.DivisionCode,','))

GROUPBY e.OfficeCode, e.EmployeeCode, e.PersonID, o.InvoiceDT, o.OrderFee, o.OrderID)as tmp

GROUPBY EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal, TotalOrder

=================================================================================================

Thanks in advance.

Anyone please...|||I got it. The problem is I put Group By TotalOrder. If I take out the group by totalOrder, it will works just fine. Thanks.