Query For Returning The Last Time A Specific Job Ran Successfully
Feb 18, 2008
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?
View 3 Replies
ADVERTISEMENT
Mar 23, 2007
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.
Thanks in advanced!
View 3 Replies
View Related
Oct 29, 2015
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
[code]....
View 1 Replies
View Related
Oct 29, 2015
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;
[Code] ....
View 8 Replies
View Related
Jul 31, 2001
I select a couple of rows from a table and sort them by the Date column. Now I would want to return only the 3rd row...
Thanks for any help!
View 1 Replies
View Related
Apr 24, 2008
I have a field that contains text strings that always follow this syntax "ABC:DE:comment follow". I want to return a new field with only whatever is between the two : (in this case DE but it could be more). It would be perfect if all the field follow that syntax, however, I have variations where users put spaces either before or after the :. Can the spaces be ignored?
Thank you for any help.
View 4 Replies
View Related
Jul 6, 2015
I have a column that has data in column user4 such as :
CALBRIGÂ Â ALBIONÂ COMPANY LTDÂ Â
1900 No. 8 Road, RIchmond, BC, V6V 1W3
I can do a LEFT(user4, 7) That returns me the first 7 charcters but I need to retrieve the company information but the number of characters may vary with each field. Is there anyway I can do this without returning the entire line?
View 3 Replies
View Related
Jul 23, 2005
I am running a DTS Package.I have a temp table with 1494 records. I am inserting a 'Y' or'N'into a temp table #HasClaims.The TempTable name with the Provider Id's(PRPR_ID) is#TempFACETSNODupesThe @identityID is an identity field counting back from 1494 to 1I count back from the Max value of the identityid (1494) in the Whileloop until I get through all the records. The idea is to check for theexistance of a claims and authorization record and put a 'Y' or 'N'record in the temptable #HasClaims.This is running in the Execute SQL Task object of the DTS Package.The Package runs successfully but only inserts 200 rows into the newtemp table. There should be a row for each provider. Each time itruns, the number of rows it returns is different. Sometimes it is 205,then 185, then 210, before it completes the DTS package.Has anyone run into While looping problems within an Execute SQL taskin a DTS package(SQL 2000)like this--------------------------------------------------------------SELECT @identityID = MAX(IDENTITYID) FROM #TempFACETSNODupesWhile @identityID >= 1BEGIN@PRPRID is the placeholder for the PRPR_ID (Provider)SELECT @PRPRID = PRPR_ID FROM #TempFACETSNODupes WHERE IDENTITYID =@identityIDIF exists( SELECT CLCL_ID FROM dbo.CMC_CLCL_CLAIM CLCL WHERECLCL.PRPR_ID = @PRPRID)BEGININSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'Y', @identityID)ENDELSE INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'N', @identityID)------------------------------------------------------------SELECT @identityID = @identityID - 1END
View 7 Replies
View Related
Sep 26, 2005
Hi There,
I want to able to run a batch of sql statments (execute procedures) in SQl Server at a specific time. Just wondering if there is a method for that.
View 5 Replies
View Related
Aug 13, 1998
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
View 1 Replies
View Related
Oct 25, 2005
I have an sql statement that I can run by hand. How do I get a job to run this automatically.
I have tried to create a job and it just failes out every time...
View 3 Replies
View Related
Aug 25, 2006
Hi!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 enddate1 23.08.2006, 15:00 24.08.2006, 17:002 24.08 .2006, 17:00 25.08.2006, 08:00and so onCan anyone help me how to do this? Thanks!
View 5 Replies
View Related
Oct 19, 2005
Is there a server variable which will tell me how long the server took in returning a particular recordset. I've looked through MSDN SQL 2000 articles and could'nt / did'nt see anything. Or perhaps there is another way? Thanks!
View 1 Replies
View Related
Feb 25, 2004
Hi,
We want trace ALL activity on prod
server between 3:00 AM and 3:15 AM
Profiler has stop time option ,but
no start option.
I am looking for example how it could be done
Thank you
Alex
View 5 Replies
View Related
Feb 28, 2008
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.
Any help in this regard will be appreciated.
View 6 Replies
View Related
Mar 11, 2008
I have a table (tbl_entries) in my db that has a timestamp field (startDate). What I need to do is run a query that counts the number of records on a 15 min interval.
something like
start_date
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:14:00.000
EVERY THING ABOVE HERE IS IN GROUP 1
2008-01-01 00:35:00.000
EVERY THING ABOVE HERE IS IN GROUP 2
2008-01-01 01:01:00.000
2008-01-01 01:03:00.000
2008-01-01 01:03:00.000
2008-01-01 01:04:00.000
EVERY THING ABOVE HERE IS IN GROUP 3
2008-01-01 01:29:00.000
EVERY THING ABOVE HERE IS IN GROUP 4
2008-01-01 01:41:00.000
EVERY THING ABOVE HERE IS IN GROUP 5
2008-01-01 02:25:00.000
2008-01-01 02:28:00.000
2008-01-01 02:31:00.000
2008-01-01 02:33:00.000
EVERY THING ABOVE HERE IS IN GROUP 6
Hope this is enough Info
View 3 Replies
View Related
Apr 10, 2008
Hi All,
I am having an issue with a JDBC based reporting tool when connected to my SQL2005 db. Basically I am getting erradic results. Let me explain the two cases.
1) The full result set gets returned. This is good. In my trace file, I can see the cursor getting created and chunks of 50 records returning to the program. The last chunk has 10 records in it (a total of 960 records).
2) Partial results returned. 10 records, which is strangely enough the last chunk size of the full result set. No errors are flagged.
The vendor of the product ensures me that they cannot replicate the problem (even given my db), and I am having trouble identifying places/settings in SQL2005 that might help.
If it makes a difference, the dataset is a query with many LEFT OUTER JOINS, but the problem seems to stem from the inclusion of a subquery which uses a function in its from clause. EG.
Code Snippet
SELECT blah1, blah2
FROM table1 LEFT OUTER JOIN table2 ON ....
...
WHERE EXISTS (SELECT id FROM func1(2,2) WHERE id = blah1)
If I remove the EXISTS clause, things seem to work well.
My @@version = Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86) Feb 13 2007 23:02:48 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Any help with this would be GREATLY appreciated.
Thanks,
Steele.
View 4 Replies
View Related
Apr 3, 2015
So I have a job that shows active processes
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.
View 2 Replies
View Related
May 28, 2015
In sql 2012 i want to track the blocking details at a specific time is any query to get the data ?
View 6 Replies
View Related
Sep 16, 2005
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END
IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END
IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END
IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END
RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'
END
View 1 Replies
View Related
May 17, 2008
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?
View 4 Replies
View Related
Apr 7, 2015
I have this query
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.
View 9 Replies
View Related
May 11, 2015
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.
View 7 Replies
View Related
Aug 8, 2007
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?
View 2 Replies
View Related
Nov 17, 2015
In C#, I can convert UTC time to a specific timezone using:
TimeZoneInfo localTimeZone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
DateTime UTCTime = TimeZoneInfo.ConvertTimeToUtc(localTime, localTimeZone);
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.
View 5 Replies
View Related
Jun 1, 2015
Need to display current time in below format:
06/01/2015 at 7:00 a PST
(Date at time TimeZone)
View 2 Replies
View Related
Oct 31, 2015
If we want to compare datetime vs a specific time what should we do?
i.e.  startdatetime                     specific time
     2015-10-12 00:03:19:020               16:23:00
I want to compare just time in startdatetime coloum vs a specific time i.e. 16:23 .
I convert startdatetime to (DT_DBTIME) after that i want to calculate just time difference. how can i do it?
i.e i want to calculate this two (DT_DBTIME) columns without any date ==> (16:23:00)-(00:03:19)
View 6 Replies
View Related
Aug 6, 2015
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])
[Code] ....
View 13 Replies
View Related
Nov 19, 2015
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
[Code] ....
View 20 Replies
View Related
Aug 2, 2012
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
View 1 Replies
View Related
Apr 21, 2015
SELECTÂ
  CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
  CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate   04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
May 26, 2005
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
View 2 Replies
View Related
Apr 12, 2008
Hello Valueable Friends , I have an issue and i am going to need a help..
I have a rents table with fields that matter now
Stuffid , rentdate , rentCustomer ........
On my stuff table i Added a new field called LastCustRent
So I want to update this field With the Customerid that rented each
stuff last time
I tried this
Select Stuffid ,max(rentdate) as maxrentdate , rentcust
from rents
Group by rentcust , stuffid
But I do not take unique results
Can someone help me on how will i get only the customer of my last rent time for each stuffid on my rents table?
I sell my mother in law.Is anybody interested?
View 4 Replies
View Related
