I use SQL Server Express 2005 and would like that a trigger is fired automatically at a specific time. I have been developing a game which has a lot of periods with a start date and an end date. Every time a periode is finished the trigger should add new records for the next periode. The dates of the periodes are already setted at the beginning of the game. For example:
periode startdate enddate
1 23.08.2006, 15:00 24.08.2006, 17:00
2 24.08 .2006, 17:00 25.08.2006, 08:00
and so on
I would like to limit the number of pages a user can view on my website each day. The users logs in and I can count the number of pages viewd in a field but i want to know how i can set the page count field to reset to 0 at the end of the day (ie midnight). Is it possible to do this? and if so how? Thanks.
I have a dilema, I need to have a delete trigger enabled to track user deletes to update an external history table. However, when the posting process runs for the table for which the delete trigger runs, all the records from that table are deleted. Th end result is that instead of capturing the specific deletes, it shows all recods being deleted. I know you can disable foreign keys and triggers as a whole, can you do it for specific triggers?
I have a table where I want to prevent user from deleting or setting a flag on a field to "y" with a database trigger (sql 2000). I understand the trigger for just one (stopping the delete, or stopping the field being changed to "y"). Should I have 2 seperate triggers or would there be a way to handle both.
I`m developing a library catalog in SQL server with a Cold Fusion front end, and I`m having some trouble with setting due dates for books when they get checked out. Most items in the library are due in a week, a month, etc., so it is easy to calculate the due date. However, we have a number of items that are due at the end of the working day.
So, I need to find a way to calculate "the next 5 PM", no matter what time of day it is, and store it in a datetime field? Any ideas?
Any help would be appreciated. Thanks, -Elizabeth S. Thomas Technical Librarian MAJIQ, Inc. ELizabeth_Thomas@majiq.com
I need a trigger to know who and when a char(1) column is changed. Â Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test]( [Customer] [varchar](12) NULL, [Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values )
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. Â If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server.
Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails.
It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on.
Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working.
Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost.
I have a program that is automatically ran through a job. The program gets the most recent files that have been uploaded to a server. I would like to be able to query the database to see when the last time this job was ran successfully and set this date as the date to look for files newer than the last successful run date.
Could someone point me in the right direction to what tables this data is stored in on a 2005 SQL Server Database?
I am wondering if it€™s possible to lock a SQL table for specific amount of time, say 5 min.
There is a particular 'Phone' table on the database that should never get locked. Yet, during the development stages we have noticed that the table gets locked at time. The issue since has been resolved to the best of our ability, but, there is still a vague chance that the table can get locked due to the multiple jobs that query the table, when we go live.
If such a situation occurs, we just want to be able flip the switch that will send the server to the mirror mode and the previously mirrored database to become the principal.
So, I just want to recreate a situation by voluntarily locking the table.
INSERT INTO Query_results(login_name, total_elapsed_time, total_elapsed_time) SELECT login_name, total_elapsed_time, total_elapsed_time FROM sys.dm_exec_sessions
I need to then kill all sessions at 11:59pm then log all those that are killed. This is so I can schedule a job at that time, I have sessions that are blocking my job.
hi there i am using .net framework 1.1 with SQL 2000 . i want the data in table to get deleted automatically after 30 days of inserting data. so how do i achieve this?
SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , execution_count FROM sys.dm_exec_query_stats AS a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b where last_execution_time >= '2015-04-07 10:01:01.01' ORDER BY execution_count DESC
But the result of execution count is from the first. I want to know it only one day.
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this: IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL BEGIN
I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1 AS DECLARE cursor_1 CURSOR FOR SELECT 'This is a executable query' FROM table_1 DECLARE @table_2
Hi, we work with the Reporting Services of the Itanium Edition of SQL Server 2005. With some reports (only some) we have the problem that very long time is needed for the processing of the report. I checked the ExecutionLog table in my Reportserver database and detected that values in the column TimeDataRetrieval (TDR) or pretty slow, but them in the column TimeProcessing (TP) are very high. Report1: TDR = 8304ms TP = 34377ms . In other (most) reports the values are completely different Report2: TDR 8336ms TP = 233ms
Now the most interesting thing: When I execute the same report on our test server which is a xeon machine (same data volume, no user workload) I get the following results: Report1: TDR = 5244ms TP = 11731ms Report2: TDR = 4750ms TP = 163 The differences in TimeDataRetrieval (TDR) should be ok, because the machine is used by over 700 people and so the response times of the Analysis Server database could differ. Report1 and 2 do not differ too much in complexity. A few groupings, parameters and so on.
The Itanium machine is a 2 way dual core system with 16 Gigabyte RAM. The Xeon machine is a 2 way xeon system with 8 Gigabyte RAM (32bit processors).
What is going on there? How could I optimize the TimeProcessing of Report1 on the Itanium machine? Which performance counters or tools should I use to go deeper finding out where the problem is?
Is there any equivalent function in T-SQL (SQL Server 2014 / SQL Azure)?
Specifically, I want to be able to convert a time into UTC before inserting it into a table. Right now I am doing:
INSERT INTO Incident (IncidentDescription, dtOccurred) VALUES (@IncidentDescription, DATEADD(hour, 8, @dtOccurred))
I don't want to hardcode "8" and I want it to adjust for daylight savings time (DST).Â
Is there any concise solution that does not require me to define SQL functions or have lookup tables? I also don't want to use the difference between GETDATE() and SYSUTCDATETIME(), because the timezone of my server is not my local timezone.
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1 AS DECLARE cursor_1 CURSOR FOR SELECT 'This is a executable query' FROM table_1 DECLARE @table_2 DECLARE @stoptime DATETIME = NULL;
At the following MDX code , I want to get the aggregate of measure only for  members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS (b)+(C) MEMBER [Measures].[Aggregate] AS Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00], ([Measures].[D])
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
Hi i am trying to make a trigger that calculates a new due time from a start time. I have a hour target like 24 hours, but the dates have an starttime and stoptime or the days could even be closed. I need it to calculate the new due datetime using the 24 hours as effective working time.
Can anyone offer any advice. I was wondering whether MS SQL server offeredany facilities to trigger an event after a period of time had lapsed? e.g.once a week check data and if it is out of date email an related address.many thanks
--main table > create table test (manufacturer varchar(500), score int) > insert into test values('Toyota', 1 ) > insert into test values('Toyota', 2) > > --logging table > > create table log_old_val(manufacturer varchar(500), score int, operation > varchar(10)) > > --trigger to log old value into log_old_val table. > create trigger tr_man on test > for update,delete > as > if @@rowcount = 0 > return > if exists (select * from inserted) > if exists (select * from deleted) > insert into log_old_val > select manufacturer,score, 'update' from deleted > > if exists (select * from deleted) > if not exists (select * from inserted) > insert into log_old_val > select manufacturer,score, 'delete' from deleted > go
