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 Migrating from 2000 to 2005- SSIS package

Hi,

I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,

- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables

Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow

-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )

-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client

pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not

Thanks for ur help
Niru

You should be able to build just one package. You might need as many data-flows as there are destination tables. it depends on the situation.

Use precedence constraints to ensure that the data-flows are executed in the correct order according to RI declared on the destination tables.

If you ask more specific questions then more specific answers can be provided.

-Jamie

|||

Thanks Jamie for the clarifying ..yes i think i should create different dataflows instead of different packages .

Here is the scenario which iam working now

I have a table...i have to map some of those fields to the existing 5 tables.

fileds should map like this

source table Destination fileds with tables
(contract)

contract_code --> contract_code (Incident)

Date --> DateId (Dates)
TimeId (Times)

Duration --> Duration (Session)

Ended --> Completion_state(Session) will have options like 'yes','no','unknown'
Problem(session) -- need to set 'true' if completion_state is 'No'

From --> Number(Audit) -- Adding source if it is not existed
Id (Audit) -- giving id if you added target in the above column

To --> Number(Audit) -- Adding Traget if it is not existed
Id (Audit) -- giving id if you added target in the above column

[I will differentiate the difference betweeen 'from' and 'TO' columns in session tables seperatley by refernce]

I think you got some idea what iam trying to do ..like this i have 9-10 tables .

I apperciate if can help me out in this .

Thanks

Niru

help needed for a query.

Hi

MailMethodId Is an integer, and it has 2 possible values 1 which means 'Mail to Participant' or 2 which means 'Mail To client'

select

cp.PlanId,

cp.ClientPlanId,

psi.MailMethodId,

psi.StatementTypeId,

cp.PlanName,

cp.ClientId ,

c.ClientName

from ClientPlan cp

Join PlanStatementInfo psi on cp.PlanId = psi.PlanId

Join Client c on cp.ClientId = c.ClientId

Where cp.ClientId = @.ClientId

Union

Select

cp.PlanId,

cp.ClientPlanId,

2,

1,

cp.PlanName,

cp.ClientId,

c.ClientName

From

ClientPlan cp

innerjoin Client c on cp.ClientId = c.ClientId

where

cp.ClientId = @.ClientId

and

cp.PlanId NotIN

(Select psi.PlanId from PlanStatementinfo psi)

So how can i get the Mail methodId to display 'Mail to Participant' if the data is 1 and 'Mail to Client' if the Data is 2

i tried doing this

case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,

instead of psi.MailmethodId, but i am getting a error message that says

Syntax error converting the varchar value 'Mail to Participant' to a column of data type int.

Any Help will be appreciated

Regards

KAren

Karen:

My knee-jerk reaction to this is that you probably have an incompatibility between how the "MailMethodID" column is used in each separate SELECT that comprises the UNION. Try it like this:

Code Snippet

select
cp.PlanId,
cp.ClientPlanId,
case When psi.MailMethodId = 1 then 'Mail to Participant' Else 'Mail to Client' End,
psi.StatementTypeId,
cp.PlanName,
cp.ClientId ,
c.ClientName
from ClientPlan cp
Join PlanStatementInfo psi on cp.PlanId = psi.PlanId
Join Client c on cp.ClientId = c.ClientId
Where cp.ClientId = @.ClientId
Union
Select
cp.PlanId,
cp.ClientPlanId,
'Mail to Client',
1,
cp.PlanName,
cp.ClientId,
c.ClientName
From
ClientPlan cp
inner join Client c on cp.ClientId = c.ClientId
where
cp.ClientId = @.ClientId
and
cp.PlanId Not IN
(Select psi.PlanId from PlanStatementinfo psi)

|||

Thanks a lot Ken, that worked.