February 11, 2009 at 3:19 pm
Hi All,
I have table 'my_table' with columns 'status' and 'eff_date'. The table is populated by some applications. For ex; the data in a row will be 'I', '02/12/2009 12:00'. Now I want to execute a StoreProcedure at '02/12/2009 12:00' specified time in eff_date column. My stored procedure will do whole bunch of checks on other tables then update the status to 'A'. I can do this by adding scheduled job dynamically in SQL server agent at specified time (02/12/2009 12:00), but our client do not want to give admin privileges and not accepted due to other reasons. I can think of writing 'Windows Service' looking at the table every 30 secs, but again client is not comfortable about windows service either. Any ideas to achieve the same.
thanks
dhanush
February 11, 2009 at 4:37 pm
bad news: I agree with your client. there is no reason to to try and update a column every 30 seconds or one minute or on a specific schedule, just to get a status to a certain value.
my first guess would be that the table that you want to change the status of could be replaced with a view, and the view can get the regular columns, and join the other tables the stored procedure would have checked, and have the view return a status based on a case statement that uses the logic the stored proc would have done.
that way, when ever the table is QUERIED, the most up to date status, based on getdate() and other biz logic, would return the correct status.
just had a similar question a couple of days ago, where somoen wanted to use a trigger to update a status...theirs was more simple, where ti was only based on date, and could use a calculated column:http://www.sqlservercentral.com/Forums/FindPost652103.aspx
yours would have to use a view because of the joins to other tables.
show us the schema and your procedures code, and i'm sure it could be re-written into a view instead.
Golden Arrow (2/11/2009)
Hi All,I have table 'my_table' with columns 'status' and 'eff_date'. The table is populated by some applications. For ex; the data in a row will be 'I', '02/12/2009 12:00'. Now I want to execute a StoreProcedure at '02/12/2009 12:00' specified time in eff_date column. My stored procedure will do whole bunch of checks on other tables then update the status to 'A'. I can do this by adding scheduled job dynamically in SQL server agent at specified time (02/12/2009 12:00), but our client do not want to give admin privileges and not accepted due to other reasons. I can think of writing 'Windows Service' looking at the table every 30 secs, but again client is not comfortable about windows service either. Any ideas to achieve the same.
thanks
dhanush
Lowell
February 11, 2009 at 9:48 pm
[font="Verdana"]This can be done through Job Scheduling. The same discussion can be found @ http://www.sqlservercentral.com/Forums/Topic653497-145-1.aspxHowever I am totally agree with Lowell.
Let us know the complete details.
Mahesh[/font]
MH-09-AM-8694
February 12, 2009 at 6:15 am
Yeah, I'm going to pile on with Lowell here. Running a check every 30 seconds and updating a status does what exactly that simply writing the same check into a stored procedure won't do?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2009 at 9:18 am
Hi,
I over simplified the problem. The main problem is to call a stored procedure/trigger at 'specified time' (at exact datetime from my_table.eff_date ) just like scheduling a job in sql server agent at specified time, not with updating 'status'. I can not use 'WAITFOR DDELAY', because I just can not open hundreds of thread/connections. Moreover, what happens if the server is restarted. It should be native means within SQL server.
unfortunately I can not share the code.
thanks
Dhanush
February 12, 2009 at 9:41 am
again, I call BS.
what possible business process needs to scan the table every 30 seconds? how often is the table queried against per second? you've explained just fine what you want to do, and we are trying to help you understand the logic is incorrect.
You've painted yourself into a corner,where you think you need to run a process to make sure the data is "up to date" based on an expiration date.
by simply using a view, where the view calculates the values you need comparing the tables expiration_date to getdate(), the view will be 100% accurate EVERY time the data is queried.
Lowell
February 13, 2009 at 2:26 pm
My bad; I failed again to explain the problem; Let me try..
my_table (ordername varchar(50) ,
status varchar(1),
end_date datetime )
An inbound feed engine inserts data in 'my_table'. For ex: after three feeds the data in the table looks like
123 A 2/14/2009 0800
234 A 2/15/2009 1200
345 A 2/15/2009 2300
Now a windows based application reads the my_table and displays the order information in one of the form and thus displays three orders. There will be more than one or more windows apps retrieve the information.
Now the problem is..
For the order '123', when end_date is reached exactly at '2/14/2009 0800' , the status need to be changed to 'I'. ( the windows application will show only two orders '234' and '345' on/after ''2/14/2009 0800').
The restrictions are
1. without windows service
2. without a scheduling job
3. without modifying windows based application.
How can I update the status to 'I' when the end date is reached with those restrictions.
Thanks for your help.
Dhanush
February 13, 2009 at 3:09 pm
Here's a complete example for you...
you simply change whatever application is looking at "my_table" to report the current status to point to "ViewOfMyTable"...all done...no process needing to run every thirty seconds....the correct status is looked up at the moment the query is materialized, so no wasted CPU cycles from midnight to 5am when no one is looking at the data.
If you needed to, you could even rename "my_table" to my_table_old, and create a view called "my_table"...see the last snippet below!
I'm updating the VIEW and it works!!! As log as you don't try and insert the calculated field, this works!
[font="Courier New"]CREATE TABLE my_table
(ordername VARCHAR(50) ,
status VARCHAR(1),
end_date DATETIME )
CREATE VIEW StatusOfMyTable
AS
SELECT
ordername ,
CASE
WHEN end_date < GETDATE()
THEN ISNULL(STATUS,'A')
ELSE 'I'
END AS status,
end_date
FROM my_table
INSERT INTO my_table(ordername,status,end_date)
SELECT '123' ,'A','2009-02-13 08:00:00.00'UNION
SELECT '234' ,'A' ,'2009-02-15 12:00:00.00' UNION
SELECT '345' ,'A' ,'2009-02-13 23:00:00.00' UNION
SELECT '456','A',GETDATE() -1 UNION
SELECT '567','A',GETDATE() -.25 UNION --today minus 8 hours
SELECT '678','A',GETDATE() +.025
/*
SELECT * FROM StatusOfMyTable
--Results
ordername status end_date
123 A 2009-02-13 08:00:00.000
234 I 2009-02-15 12:00:00.000
345 I 2009-02-13 23:00:00.000
456 A 2009-02-12 16:56:35.107
567 A 2009-02-13 10:56:35.107
678 I 2009-02-13 17:32:35.107
*/
--i'm updating VIEW! just don't try to update the calculated portion!
INSERT INTO StatusOfMyTable (ordername,end_date)
SELECT '789',GETDATE() -1 UNION
SELECT '890',GETDATE() -1 [/font]
Lowell
February 13, 2009 at 3:29 pm
Thanks for the solution.
But, the restriction is 'without changing' the windows application. (I can imagine client's nightmare of installing new application if I use 'views' as per your solution). Moreover, the status in my_table will always be 'A' and will never change it to 'I' as per your solution.
Thanks
Dhansh
February 13, 2009 at 3:51 pm
the point trying to make, is that if the "status" is based on criteria, which you said was a combination of the end date and other factors in other tables, it should not be a static column. it should be calculated based on your business criteria.
the status would always be correct in the view, and the column in my_table for "Status" probably shouldn't exist at all.
Fixing something or making it better is always the right thing to do. how often is the table queried to check the status? once or twice an hour? more often?
Lowell
February 13, 2009 at 3:57 pm
Couldn't the status column also be changed to be a calculated column using the same logic as that used in the view?
February 13, 2009 at 4:10 pm
Lynn he had stated previously that he actually has to check a couple other tables to get the true status...i think this was just a simplified example....since he's got to gather data from other tables, he'd need to use a view instead ohe simple inline calculated field...
he's locked mentally into updating the table via an external process though....
Lowell
February 13, 2009 at 4:28 pm
Oh well. Not much more we can do here then I guess.
February 13, 2009 at 5:47 pm
dunno why i keep looking at this...
you can add a trigger on the VIEW to handle the status column....
rename the original table.
--add a view that has the old table name that calculates the status.
--add a trigger on the view as INSTEAD OF to handle the insert issue.
--no changes to the original application...everything on SQL
--problem solved.
enhanced example:
[font="Courier New"]
CREATE TABLE my_table
(ordername VARCHAR(50) ,
status VARCHAR(1),
end_date DATETIME )
GO
--now rename it
EXEC sp_rename 'my_table','my_table_Original'
CREATE VIEW my_table
AS
SELECT
ordername ,
CASE
WHEN end_date < GETDATE()
THEN ISNULL(STATUS,'A')
ELSE 'I'
END AS status,
end_date
FROM my_table
GO
CREATE TRIGGER tr_StatusOfMyTable ON my_table --a trigger on a VIEW!
INSTEAD OF INSERT
AS
--carefully avoiding the insert of the status column
INSERT INTO my_table(ordername,end_date)
SELECT ordername,end_date FROM INSERTED
GO
--insert some data...updating a view...the trigger addresses the calculated column update problem
INSERT INTO my_table(ordername,status,end_date)
SELECT '123' ,'A','2009-02-13 08:00:00.00'UNION
SELECT '234' ,'A' ,'2009-02-15 12:00:00.00' UNION
SELECT '345' ,'A' ,'2009-02-13 23:00:00.00' UNION
SELECT '456','A',GETDATE() -1 UNION
SELECT '567','A',GETDATE() -.25 UNION --today minus 8 hours
SELECT '678','A',GETDATE() +.025
/*
SELECT * FROM My_Table
--Results
ordername status end_date
123 A 2009-02-13 08:00:00.000
234 I 2009-02-15 12:00:00.000
345 I 2009-02-13 23:00:00.000
456 A 2009-02-12 16:56:35.107
567 A 2009-02-13 10:56:35.107
678 I 2009-02-13 17:32:35.107
*/[/font]
Lowell
February 13, 2009 at 5:52 pm
Lowell (2/13/2009)
dunno why i keep looking at this...you can add a trigger on the VIEW to handle the status column....
rename the original table.
--add a view that has the old table name that calculates the status.
--add a trigger on the view as INSTEAD OF to handle the insert issue.
--no changes to the original application...everything on SQL
--problem solved.
enhanced example:
[font="Courier New"]
CREATE TABLE my_table
(ordername VARCHAR(50) ,
status VARCHAR(1),
end_date DATETIME )
GO
--now rename it
EXEC sp_rename 'my_table','my_table_Original'
CREATE VIEW my_table
AS
SELECT
ordername ,
CASE
WHEN end_date < GETDATE()
THEN ISNULL(STATUS,'A')
ELSE 'I'
END AS status,
end_date
FROM my_table
GO
CREATE TRIGGER tr_StatusOfMyTable ON my_table --a trigger on a VIEW!
INSTEAD OF INSERT
AS
--carefully avoiding the insert of the status column
INSERT INTO my_table(ordername,end_date)
SELECT ordername,end_date FROM INSERTED
GO
--insert some data...updating a view...the trigger addresses the calculated column update problem
INSERT INTO my_table(ordername,status,end_date)
SELECT '123' ,'A','2009-02-13 08:00:00.00'UNION
SELECT '234' ,'A' ,'2009-02-15 12:00:00.00' UNION
SELECT '345' ,'A' ,'2009-02-13 23:00:00.00' UNION
SELECT '456','A',GETDATE() -1 UNION
SELECT '567','A',GETDATE() -.25 UNION --today minus 8 hours
SELECT '678','A',GETDATE() +.025
/*
SELECT * FROM My_Table
--Results
ordername status end_date
123 A 2009-02-13 08:00:00.000
234 I 2009-02-15 12:00:00.000
345 I 2009-02-13 23:00:00.000
456 A 2009-02-12 16:56:35.107
567 A 2009-02-13 10:56:35.107
678 I 2009-02-13 17:32:35.107
*/[/font]
I had actually started to suggest this as another solution, but changed my mind. This is a possible solution for this problem.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply