September 4, 2017 at 3:39 am
Hi,
I am using SQL Server 2012 Express Edition.
I need to automatically change the value of status field to "2" if it reaches the particular time "2016-09-09 18:13:00.000".
How to achieve this?
I am not having SQL agent in my system in order to use the "SQL Job Scheduler" as I am having SQL SERVER 2012 EXPRESS EDITION
Here is the table,
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#JobHistory','U') IS NOT NULL
DROP TABLE #JobHistory
--===== Create the test table with
CREATE TABLE #JobHistory
(
ID BIGINT IDENTITY(1,1) Not null PRIMARY KEY,
EnqId INT,
ModifiedDate DATETIME,
UserId int,
[Status] int,
OldValue nvarchar(max),
NewValue nvarchar(max)
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #JobHistory ON
--===== Insert the test data into the test table
INSERT INTO #JobHistory
(ID,EnqId,ModifiedDate,UserId,[Status],OldValue,NewValue)
SELECT '1','2','Sep 9 2017 6:11PM','3','1','4','5' UNION ALL
SELECT '2','2','Sep 9 2017 6:11PM','3','1','7','8' UNION ALL
SELECT '3','2','Sep 9 2017 6:13PM','58','1','8','16' UNION ALL
SELECT '4','2','Sep 9 2017 6:14PM','58','1','16','18' UNION ALL
SELECT '5','2','Sep 9 2017 6:14PM','58','1','18','22' UNION ALL
SELECT '6','2','Sep 9 2017 6:14PM','58','1','22','23' UNION ALL
SELECT '7','2','Sep 9 2017 6:15PM','58','1','23','24'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #JobHistory OFF
select * from #JobHistory
Thanks in Advance,
Regards,
Poornima
September 4, 2017 at 4:10 am
You want the status set to 2 when what exactly happens?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2017 at 4:10 am
If you need to automate anything with SQL Express that you would normally do with the SQL Agent, you need to use the Windows Task Scheduler.
Is this a job though and not the need for a trigger perhaps, a bit of background on the task at hand may be beneficial. Eg on insert of a row that is 6:13pm set status = 2 can be done without the need for a job/task schedule.
September 4, 2017 at 7:05 am
poornima.s_pdi - Monday, September 4, 2017 3:39 AMHi,
I am using SQL Server 2012 Express Edition.
I need to automatically change the value of status field to "2" if it reaches the particular time "2016-09-09 18:13:00.000".
How to achieve this?
I am not having SQL agent in my system in order to use the "SQL Job Scheduler" as I am having SQL SERVER 2012 EXPRESS EDITION
Here is the table,
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#JobHistory','U') IS NOT NULL
DROP TABLE #JobHistory--===== Create the test table with
CREATE TABLE #JobHistory
(
ID BIGINT IDENTITY(1,1) Not null PRIMARY KEY,
EnqId INT,
ModifiedDate DATETIME,
UserId int,
[Status] int,
OldValue nvarchar(max),
NewValue nvarchar(max)
)--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #JobHistory ON--===== Insert the test data into the test table
INSERT INTO #JobHistory
(ID,EnqId,ModifiedDate,UserId,[Status],OldValue,NewValue)
SELECT '1','2','Sep 9 2016 6:11PM','3','1','4','5' UNION ALL
SELECT '2','2','Sep 9 2016 6:11PM','3','1','7','8' UNION ALL
SELECT '3','2','Sep 9 2016 6:13PM','58','1','8','16' UNION ALL
SELECT '4','2','Sep 9 2016 6:14PM','58','1','16','18' UNION ALL
SELECT '5','2','Sep 9 2016 6:14PM','58','1','18','22' UNION ALL
SELECT '6','2','Sep 9 2016 6:14PM','58','1','22','23' UNION ALL
SELECT '7','2','Sep 9 2016 6:15PM','58','1','23','24'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #JobHistory OFFselect * from #JobHistory
Thanks in Advance,
Regards,
Poornima
You don't need SQL Agent for any of this. You don't need a manual update either. I do, however, need some more information to help you.
You said the following...
I need to automatically change the value of status field to "2" if it reaches the particular time "2016-09-09 18:13:00.000".
What does "it" mean in this case? Are you saying that if the ModifiedDate becomes greater than 2016-09-09 18:13:00.000" (no matter the reason), that you want the Status to change to "2"? And are you saying that will ALWAYS be true for all rows from now until the end of time or will there be some other criteria in the future as to when a given row should automatically change to a Status of "2"?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2017 at 7:36 pm
Hi All,
Thanks for your reply.
I need to automatically change the value of status field to "2" if it reaches the particular time "2017-09-09 18:13:00.000".
Here "it" refers to current date time.
If current date time reached the modified date then I want status to change automatically to 2.
how to achieve this?
Regards,
Poornima
September 4, 2017 at 10:04 pm
Do you mean like this? (Note: I had to change your comparison date.)DECLARE @CurrTime DATETIME = '2016-09-09 18:13:00.000';
UPDATE #JobHistory
SET [Status]=2
WHERE ModifiedDate<=@CurrTime;
September 4, 2017 at 11:54 pm
Hi pietlinden,
Thanks for your reply.
I am doing a bidding system,
I need to change the status of the table ,
i.e., The bidding is scheduled at the "Modified Date" column.
So if the current time reaches the ModifiedTime then I Want the system to automatically change the status to "2".
Status=2 means Bid is in "Open".
I hope "this information is clear which helps to achieve my tasks".
If not let me know.
Regards,
Poornima
September 5, 2017 at 1:11 am
poornima.s_pdi - Monday, September 4, 2017 11:54 PMHi pietlinden,Thanks for your reply.
I am doing a bidding system,
I need to change the status of the table ,
i.e., The bidding is scheduled at the "Modified Date" column.
So if the current time reaches the ModifiedTime then I Want the system to automatically change the status to "2".
Status=2 means Bid is in "Open".
I hope "this information is clear which helps to achieve my tasks".
If not let me know.Regards,
Poornima
Write an update loop with a 1 minute wait delay and have it running all the time. Either that or create a stored procedure that people would access instead of the table. The first thing it would do is change any statuses that needed to be updated.
Either way, performance will get quite bad as the table grows. Can't you calculate the status by date when someone goes to look at it through a view or proc instead of trying to keep a temporally based status up to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2017 at 1:37 am
In that case, you need a scheduled job running on a regular interval. Use Windows Scheduler since you don't have SQL Agent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2017 at 2:19 am
Hi pietlinden,
Thanks for your reply.
I am doing a bidding system,
I need to change the status of the table ,
i.e., The bidding is scheduled at the "Modified Date" column.
So if the current time reaches the ModifiedTime then I Want the system to automatically change the status to "2".
Status=2 means Bid is in "Open".
I hope "this information is clear which helps to achieve my tasks".
If not let me know.
Regards,
Poornima
Jeff Moden - Tuesday, September 5, 2017 1:11 AMpoornima.s_pdi - Monday, September 4, 2017 11:54 PMHi pietlinden,Thanks for your reply.
I am doing a bidding system,
I need to change the status of the table ,
i.e., The bidding is scheduled at the "Modified Date" column.
So if the current time reaches the ModifiedTime then I Want the system to automatically change the status to "2".
Status=2 means Bid is in "Open".
I hope "this information is clear which helps to achieve my tasks".
If not let me know.Regards,
PoornimaWrite an update loop with a 1 minute wait delay and have it running all the time. Either that or create a stored procedure that people would access instead of the table. The first thing it would do is change any statuses that needed to be updated.
Either way, performance will get quite bad as the table grows. Can't you calculate the status by date when someone goes to look at it through a view or proc instead of trying to keep a temporally based status up to do.
Hi Jeff,
Thanks for your reply.
I am not clear with the following.
Either way, performance will get quite bad as the table grows. Can't you calculate the status by date when someone goes to look at it through a view or proc instead of trying to keep a temporally based status up to do.
Are you asking me to use view or storedprocedure? If it is a view, the actual data of the table is not affected.
I am also trying to run a storedprocedure continuously but don't know how to do?
I never used Windows scheduler before.
How to write the update loop with 1 minute delay and have it running all the time using stored procedure?
Regards,
Poornima
September 5, 2017 at 5:47 am
Tables don't "automatically update" by themselves. (Egad, would that be a mess if they did!) You need something to make the table update.
Usually that would be a stored procedure that runs a query. A relatively simple query, like:UPDATE table
SET Status = '2'
WHERE ModifiedDate >= GETDATE()
AND Status <> '2' -- why update it again, if it was already set?
Then you have to have a mechanism for this to run. That usually means a SQL agent job. Since your sample has datetime values that vary minute by minute, you'd probably need this job to run every minute. But without having access to SQL Agent, you'd have to rely on Windows Scheduler instead - sorry I can't help you with that.
Performance may benefit by having an index on Status.
But again the question becomes, Does this data need to be updated real time every minute of every day of every year? Or can you wait until someone accesses the data to determine the status? If you can wait, it can be done in a view, and Status does not need to be part of the table. Simply define a column in the view with a Case:CASE
WHEN ModifiedDate >= GETDATE() THEN '2'
ELSE '1'
END as Status
If the users all access the data via the view, they will always have the up-to-date status.
September 5, 2017 at 11:32 am
You really need to read a book on basic SQL and RDBMS. What you posted is fundamentally wrong. There is no such thing as a generic universal “statusâ€; it has to be the status of something in particular.
A job_status is a state of being. That means it has a duration with a start and end timestamp. But you’ve designed a table modeled after paper forms instead of a relational model!
The proprietary Sybase IDENTITY table property (it is not a column by definition) can never be a key. It’s how we located the physical records (not rows!) in a UNIX system when Sybase first created SQL Server.
You also don’t seem to know that identifiers can never be numerics; you don’t do math with them. At least, I hope you don’t do math with them. Let’s try redoing your disaster.
This is a basic history table skeleton for property rentals and sales.
CREATE TABLE Occupancy_History
(property_id CHAR(13) NOT NULL
REFERENCES Inventory(property_id),
prev_date DATE NOT NULL,
start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATE, -- NULL means property_ids current status
property_status VARCHAR(10) NOT NULL
CHECK (property_statusIN ('vacant', 'rented', 'condemned', .. ),
etc.
-- now we add constraints
--uniqueness
PRIMARY KEY (property_id, start_date),
UNIQUE (property_id, end_date),
UNIQUE (property_id, prev_end_date),
--ordering of events
CHECK (start_date <= end_date),
-- Optionally, CHECK (start_date < end_date
CHECK (prev_end_date <= start_date),
-- Optionally, CHECK (start_date = prev_date + INTERVAL ‘1’ DAY), -- prevents gaps
-- self-referencing constraint
CONSTRAINT property_id_prev_end_date
FOREIGN KEY (property_id, prev_end_date)
REFERENCES statusHistory (property_id, end_date)
);
The first three uniqueness constraints are fairly obvious. The property_id and the start of a status have to be unique and not NULL-able so we have a natural primary key. The ending dates, current and previous, might have NULLs, so we need to use a UNIQUE constraint.
The next constraints give an ordering to each event, namely, prev_date is on or before start_date, which is on or before end_date. The reason for not putting this into a single BETWEEN predicate is that each constraint will have a name in production code that will show up in error messages so we want to be exact.
The self-referencing constraint is a trick from Alex Kuznetsov. It says that all the previous status ending dates were really ending dates for some time period. You will want to play with options to get them to fit your own business rules.
It is also a good idea to have a VIEW with the current data:
CREATE VIEW Current_Status (..)
AS
SELECT ..
FROM Occupancy_History
WHERE end_date IS NULL
OR end_date >= CURRENT_TIMESTAMP;
You use a BETWEEN predicate to get the appropriate status for particular date.
SELECT ..
FROM Occupancy_History AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP); -- or other known data as needed
Not only is your design fundamentally wrong, but the details are also wrong. If you really want to learn, we can dissect the code you posted line by line for the errors.
Your particular problem probably be resolved with a CHECK () Constraint, something like
CHECK (CASE WHEN start_timestamp < ‘2016-09-09 18:13:00’
OR generic_status <> ‘2’
THEN ‘F’ ELSE ‘T’ END = ‘T’)
The basic idea is to forbid they bad data with constraints. This is the fundamental concept in SQL. But I think you really want to do something else.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 5, 2017 at 9:10 pm
Hi Joe Celko,
Thanks for your reply.
You said the following,
Not only is your design fundamentally wrong, but the details are also wrong.
This is just a sample table.
My need is to have some scheduler or timer kind of things to run the stored procedure for every min.
The "Modified Date" is the bidding Start date.
If the current time reaches the "Bidding Start date and time" ,I want the "Status" field to get automatically change to "2".
Status 1->Scheduled Jobs
Status 2->Open Job
Status -> Closed Job.
Here the table design is making others to understand what my need is?
If not let me know I will try to explain in someother way..
Regards,
Poornima
September 5, 2017 at 10:34 pm
poornima.s_pdi - Tuesday, September 5, 2017 9:10 PMHi Joe Celko,
Thanks for your reply.You said the following,
Not only is your design fundamentally wrong, but the details are also wrong.
This is just a sample table.
My need is to have some scheduler or timer kind of things to run the stored procedure for every min.
The "Modified Date" is the bidding Start date.
If the current time reaches the "Bidding Start date and time" ,I want the "Status" field to get automatically change to "2".
Status 1->Scheduled Jobs
Status 2->Open Job
Status -> Closed Job.
Here the table design is making others to understand what my need is?
If not let me know I will try to explain in someother way..Regards,
Poornima
You should ignore him. He rants about many things and then does the very same things he rants about. He goes on about not using proprietary stuff and always following the rules of ISO and then uses proprietary stuff that isn't ISO compliant and has no real clue that portability is a myth.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2017 at 8:28 pm
Hi Jeff,
Thanks for your reply.
Your answer suits for me.
Thanks a lot for your consoling reply too..
Regards,
Poornima
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply